ADO recordset problem 
Author Message
 ADO recordset problem

Hi,

I am having a problem with a Visual Basic script. The script copies
data from an odbc destination to SQL server via ADO. The error message
that I am getting is: [Microsoft][ODBC SQL Server Driver][SQL
Server]Incorrect syntax near the keyword 'values'. The error occurs on
the UpdateBatch call on a recordset that is trying to update the
destination database. The status of the recordset is 4097, which I
think is a combination of the ADO constants adRecNew and
adRecIntegrityViolation. Does anyone know why the status of the
recordset gets set to this? The code is pasted below. The copyfield
function just copies the specified field from the source recordset to
the destination recordset.

Code:

'-------------------------------------------------------------------------------
' Populate the rsSource recordset
Set rsSource= SourceDB.Execute("SELECT * FROM
pros_charge_proformas_particulars  " _
                              & "WHERE id = '" & UVUpdate.Key & "' ")

' Select an empty recordset from pros_charge_proformas_charge so it
can be populated
Set rsTarget = TargetDB.Execute("SELECT * FROM
pros_charge_proformas_particulars  " _
                              & "WHERE id = '" & UVUpdate.Key & "' ",
False)
'-------------------------------------------------------------------------------

If Not (rsSource.BOF And rsSource.EOF) Then
    While Not rsSource.EOF
        rsTarget.AddNew
        For I = 0 To (rsSource.Fields.Count - 1)
            CopyField UVUpdate, rsSource.Fields(I).Name, rsSource,
rsTarget
        Next
        rsSource.MoveNext
    Wend
End If

' Save & Close the recordset
If Not (rsTarget.BOF Or rsTarget.EOF) Then
    If rsTarget.status = adRecModified Or rsTarget.status = adRecNew
Then
        rsTarget.UpdateBatch
    End If
End If
rsTarget.Close



Sun, 29 May 2005 07:57:15 GMT  
 ADO recordset problem
Hi Adrian,

You problem can be something weird in VB 6 that I noticed. It concerns the
command after the remark  ' Save & Close the recordset :

If Not (rsTarget.BOF Or rsTarget.EOF) Then

Though this should be correct, I noticed after some tests, it should look as
follows:

If Not rsTarget.BOF or  Not rsTarget.EOF Then

I know that both commands sound the same, but my experience that vb treats
them different.

Regards,

Martin



Quote:
> Hi,

> I am having a problem with a visual basic script. The script copies
> data from an odbc destination to SQL server via ADO. The error message
> that I am getting is: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Incorrect syntax near the keyword 'values'. The error occurs on
> the UpdateBatch call on a recordset that is trying to update the
> destination database. The status of the recordset is 4097, which I
> think is a combination of the ADO constants adRecNew and
> adRecIntegrityViolation. Does anyone know why the status of the
> recordset gets set to this? The code is pasted below. The copyfield
> function just copies the specified field from the source recordset to
> the destination recordset.

> Code:

'---------------------------------------------------------------------------
----
Quote:
> ' Populate the rsSource recordset
> Set rsSource= SourceDB.Execute("SELECT * FROM
> pros_charge_proformas_particulars  " _
>                               & "WHERE id = '" & UVUpdate.Key & "' ")

> ' Select an empty recordset from pros_charge_proformas_charge so it
> can be populated
> Set rsTarget = TargetDB.Execute("SELECT * FROM
> pros_charge_proformas_particulars  " _
>                               & "WHERE id = '" & UVUpdate.Key & "' ",
> False)

'---------------------------------------------------------------------------
----

- Show quoted text -

Quote:

> If Not (rsSource.BOF And rsSource.EOF) Then
>     While Not rsSource.EOF
>         rsTarget.AddNew
>         For I = 0 To (rsSource.Fields.Count - 1)
>             CopyField UVUpdate, rsSource.Fields(I).Name, rsSource,
> rsTarget
>         Next
>         rsSource.MoveNext
>     Wend
> End If

> ' Save & Close the recordset
> If Not (rsTarget.BOF Or rsTarget.EOF) Then
>     If rsTarget.status = adRecModified Or rsTarget.status = adRecNew
> Then
>         rsTarget.UpdateBatch
>     End If
> End If
> rsTarget.Close



Wed, 15 Jun 2005 05:06:05 GMT  
 ADO recordset problem
Hi Martin,

This problem turned out to be a problem with the source database that
I was connecting to. It connects to a UniVerse database and there was
a problem with one of the table definitions which the DBA has now
fixed. I think that Not(rsTarget.BOF Or rsTarget.EOF) is equivalent to
: not rsTarget.BOF and not rsTarget.EOF. Not sure about that, it has
been a while! Thanks for your help,

Adrian

Quote:

> Hi Adrian,

> You problem can be something weird in VB 6 that I noticed. It concerns the
> command after the remark  ' Save & Close the recordset :

> If Not (rsTarget.BOF Or rsTarget.EOF) Then

> Though this should be correct, I noticed after some tests, it should look as
> follows:

> If Not rsTarget.BOF or  Not rsTarget.EOF Then

> I know that both commands sound the same, but my experience that vb treats
> them different.

> Regards,

> Martin



> > Hi,

> > I am having a problem with a visual basic script. The script copies
> > data from an odbc destination to SQL server via ADO. The error message
> > that I am getting is: [Microsoft][ODBC SQL Server Driver][SQL
> > Server]Incorrect syntax near the keyword 'values'. The error occurs on
> > the UpdateBatch call on a recordset that is trying to update the
> > destination database. The status of the recordset is 4097, which I
> > think is a combination of the ADO constants adRecNew and
> > adRecIntegrityViolation. Does anyone know why the status of the
> > recordset gets set to this? The code is pasted below. The copyfield
> > function just copies the specified field from the source recordset to
> > the destination recordset.

> > Code:

> '---------------------------------------------------------------------------
> ----
> > ' Populate the rsSource recordset
> > Set rsSource= SourceDB.Execute("SELECT * FROM
> > pros_charge_proformas_particulars  " _
> >                               & "WHERE id = '" & UVUpdate.Key & "' ")

> > ' Select an empty recordset from pros_charge_proformas_charge so it
> > can be populated
> > Set rsTarget = TargetDB.Execute("SELECT * FROM
> > pros_charge_proformas_particulars  " _
> >                               & "WHERE id = '" & UVUpdate.Key & "' ",
> > False)

> '---------------------------------------------------------------------------
> ----

> > If Not (rsSource.BOF And rsSource.EOF) Then
> >     While Not rsSource.EOF
> >         rsTarget.AddNew
> >         For I = 0 To (rsSource.Fields.Count - 1)
> >             CopyField UVUpdate, rsSource.Fields(I).Name, rsSource,
> > rsTarget
> >         Next
> >         rsSource.MoveNext
> >     Wend
> > End If

> > ' Save & Close the recordset
> > If Not (rsTarget.BOF Or rsTarget.EOF) Then
> >     If rsTarget.status = adRecModified Or rsTarget.status = adRecNew
> > Then
> >         rsTarget.UpdateBatch
> >     End If
> > End If
> > rsTarget.Close



Sun, 26 Jun 2005 08:39:23 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. ADO recordset problem...

2. ADO recordset problem

3. ADO Recordset problem

4. VB ado recordset problem...

5. Updatebatch on a Disconnected ADO Recordset Problem

6. ADO recordset problems when querrying Active Directory

7. ADO recordset problem (SQL error)

8. ADO recordset problem - Please Help

9. ADO Recordset problem. 2x same RS records reading.

10. Disconnected ADO recordset problem with calculated fields: OK in SQL 6.5 but error in SQL 2000 ?!?

11. Open ADO recordset on another ADO recordset - possible?

12. Problem Converting ADO Recordset to XML

 

 
Powered by phpBB® Forum Software