PRB: Update of Same Record Twice with Disconnect ADO Recordset Fails (193515)

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.

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.

If the disconnected recordset on the client is updated twice without either returning the new updated recordset to the client or without calling UpdateBatch on the client recordset between updates, the second update will not be successful on the server because the client and server data will be out of synch.

STATUS

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

Create a new Visual Basic ActiveX .exe project.
Create a reference to the Microsoft ActiveX Data Objects Library and also to the Microsoft ActiveX Data Objects Recordset Library.
From the Project menu, choose Project Properties and rename the project to ADOTest.

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

From the File menu, choose Make ADOTest.exe.

Create the Client

Create a new Visual Basic Standard .exe project.
Create a reference to either the Microsoft ActiveX Data Objects Library. Then, create a reference to the ADOTest component that you just created.
Add a command button to the default form.

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

MORE INFORMATION

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 unchanged to reflect the value that was originally in the database. When the recordset is reconnected and an UpdateBatch is performed, the row to be updated is located on the server and the new value is changed and saved. The method by which this occurs may differ slightly depending on the version of the server database engine being used, and whether ODBC or OLE-DB is being used. In all cases though, the row is ultimately located and changed by using the value of the OriginalValue property in the WHERE clause of an UPDATE statement.

In the scenario described in this article, the first time the recordset is passed back to the out-of-process server, the data on the server is updated. However if a new updated recordset isn't returned to the client, or if an UpdateBatch is not performed on the client recordset, the OriginalValue property will still contain the values from the original recordset object. In this case, the client recordset will now be out of synch with the actual data on the server. With the recordsets out of synch, the second update to the out-of-process server will fail when UpdateBatch is called. The update fails because the row cannot be located by the value passed in the WHERE clause, since it no longer exists in the server table. With ADO version 1.5, it is possible that no error will occur if SQL 6.5 is being used, the ADO connection uses the OLE-DB Provider for ODBC, and the ODBC Driver in use has the "Create Temporary Stored Procedures for Prepared SQL Statements" option enabled. With ADO version 2.0 and later, the following error message will occur when the second update is executed:

2147217864 Row cannot be located for updating. Some values may have been changed since it was last read.