PRB: Update of Same Record 2x w/Disconnect ADO Recordset Fails

ID: Q193515


The information in this article applies to:


SYMPTOMS

This article assumes that the reader is familiar with disconnected ActiveX Data Objects (ADO) recordsets, and how to pass these from an out-of-process server to a client application. For more information on disconnected recordsets please refer to the article listed in the REFERENCES section.

This article uses an out of process server to pass an ADO recordset to a client. The client then makes modifications to the recordset, and passes the recordset object back to the out-of-process server so that the server can reconnect to the database and update the data.

If the disconnected recordset on the client is updated twice, the changes made on the second update may not be committed on the server. This is because the client0side recordset either did not retrieve the updated copy of the recordset from the server after the first update, or did not call UpdateBatch on the client recordset to update the OriginalValue properties of the client-side recordset.

ADO recordsets maintain a copy of the original values that were returned from the database in the OriginalValue property. When the client updates a disconnected recordset locally, the Value property is modified and the OriginalValue property is left to reflect the value that was originally in the database. When the recordset is passed back to the out-of-process server and an UpdateBatch is performed, a temporary stored procedure is created and used to update the database values. The OriginalValue property is used in the WHERE clause when this update is performed. The recordset on the server side is updated, but because the client still has the original recordset object the client recordset original values are now out of date. If the client does not obtain an updated recordset object or perform an UpdateBatch on the local recordset, the second time the recordset is passed back to the out-of-process server the OriginalValue property is out of date and prevents the update from actually taking place.


RESOLUTION

Use one of the following two examples:


STATUS

This behavior is by design.


MORE INFORMATION

Steps to Reproduce Behavior

There are two main steps to reproduce this behavior. First create an out-of-process server to access the database. Second, create a client to use this out of process server. The following sample uses the Pubs database that is provided with SQL Server. You need to alter the connection information in the code so that it corresponds to your SQL Server.

Create the ActiveX EXE Server

  1. Create a new Visual Basic ActiveX .exe project.


  2. Create a reference to either the Microsoft ActiveX Data Objects 1.5 or 2.0 Library and also to the Microsoft ActiveX Data Objects Recordset 1.5 or 2.0 Library.


  3. From the Project menu, choose Project Properties and rename the project to ADOTest.


  4. Paste the following code into the default class module:
    Const szConnect = "Driver={SQL Server};Server=yourserver;" & _
           "Uid=sa;Pwd=;Database=pubs"
    
          Public Function FLoad() As ADOR.Recordset
    
           Dim rs As ADOR.Recordset
           Dim cn As ADODB.Connection
    
           Set cn = New ADODB.Connection
           cn.CursorLocation = adUseClient
           cn.Open szConnect
    
           Set rs = New ADOR.Recordset
           Set rs.ActiveConnection = cn
    
           rs.CursorLocation = adUseClient
           rs.Open "Select au_lname from authors", cn, _
            adOpenKeyset, adLockBatchOptimistic
    
           Set rs.ActiveConnection = Nothing
           Set FLoad = rs
    
          End Function
    
          Public Sub Update(ByVal rsClient As ADOR.Recordset)
    
           Dim rsConnection As New ADODB.Connection
           Dim rsServer As New ADODB.Recordset
    
           rsConnection.Open szConnect
    
           rsServer.Open rsClient
           Set rsServer.ActiveConnection = rsConnection
    
           rsServer.UpdateBatch
    
          End Sub
    
      


  5. From the File menu, choose Make ADOTest.exe.


Create the Client

  1. Create a new Visual Basic Standard .exe project.


  2. Create a reference to either the Microsoft ActiveX Data Objects Recordset 1.5 or 2.0 Library. Then, create a reference to the ADOTest component that you just created.


  3. Add a command button to the default form.


  4. Add the following code to the default form:
    Dim rs As ADOR.Recordset
          Set rs = New ADOR.Recordset
          Dim obj As ADOTest.Class1
          Set obj = New ADOTest.Class1
    
          Set rs = obj.FLoad
    
          'First update.
          '---------------------------
          rs.MoveLast
          rs.MoveFirst
          rs(0).Value = "FirstTime"
          rs.Update
    
          rs.MarshalOptions = adMarshalModifiedOnly
          Debug.Print "OriginalValue after Update (Before method): " _
              & rs(0).OriginalValue
          obj.Update rs
          Debug.Print "OriginalValue after Update:" & rs(0).OriginalValue
    
          'If you comment out the following line the second update
          'fails, although it does NOT return an error message.  You have
          'to look at the data in the table to see that it failed.
          '-------------------------------------------------------------
          rs.UpdateBatch
          Debug.Print "OriginalValue after UpdateBatch:" & rs(0).OriginalValue
    
          'Second update.
          '----------------------------
          rs.MoveLast
          rs.MoveFirst
          rs(0).Value = "SecondTime"
          rs.Update
    
          rs.MarshalOptions = adMarshalModifiedOnly
          obj.Update rs
          rs.UpdateBatch
     



REFERENCES

For additional information about disconnected recordsets, please see the following article(s) in the Microsoft Knowledge Base:

Q184397 Getting ADO Disconnected Recordsets in VBA/C++/Java

Additional query words: kbADO150 kbADO200 kbDatabase kbDCOM


Keywords          : kbADO150 kbADO200 kbDatabase 
Version           : WINDOWS:1.5,2.0
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 24, 1999