Microsoft KB Archive/193515

= PRB: Update of Same Record Twice with Disconnect ADO Recordset Fails =

Article ID: 193515

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q193515



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.



RESOLUTION
Use one of the following two examples:
 * Return a new updated recordset object to the client after each UpdateBatch on the server. -or-


 * Perform an UpdateBatch on the client to update the OriginalValue properties of the recordset to be updated to the values contained in the Value properties.



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.</li> Create a reference to either the Microsoft ActiveX Data Objects Library. Then, create a reference to the ADOTest component that you just created.</li> Add a command button to the default form.</li>  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

</li></ol>

<div class="moreinformation_section">

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.

<div class="references_section">