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