Row cannot be located for updating

From: Veerle (veerleverbr_at_hotmail.com)
Date: 04/23/04 

Date: 23 Apr 2004 06:48:31 -0700

Hi,

I'm using ADO disconnected recordsets in my VB6 program. I use OLEDb
to connect to a SQL Server 2000 DB.

There I have a table Test with a primary key ID and no other indexes.
I connect to de database, do "select * from Test where ID = 1" with as
result a recordset containing one row, and disconnect the recordset.
Then I change some field (other than ID), reconnect to the DB and
execute an update. This gives me the error: "Row cannot be located for
updating. Some values may have been changed since it was last read".
Strangly enough, the value that I changed, is changed in the database
as well...

Any ideas what I'm doing wrong? Here's my code for the select:

    'Open connection to the database
    Dim Con As New ADODB.Connection
    Con.Open ConnectionString
    Con.CursorLocation = adUseClient
    
    'Execute the query and remove the active connection
    Dim Rs As New ADODB.Recordset
    Rs.CursorLocation = adUseClient
    Rs.Open Query, Con, adOpenForwardOnly, adLockBatchOptimistic
    Set Rs.ActiveConnection = Nothing
    
    'Close connection to the database
    Con.Close
    Set Con = Nothing

And the code for the update:

    'Open connection to the database
    Dim Con As New ADODB.Connection
    Con.Open ConnectionString
    Con.CursorLocation = adUseClient
    
    'Reconnect to the database and perform the update
    Rs.ActiveConnection = Con
    Rs.UpdateBatch
    
    Rs.Close
    Con.Close
    Set Rs = Nothing
    Set Con = Nothing

Relevant Pages