Microsoft KB Archive/270636

= PRB: Passing a Recordset to a DLL to Do an UpdateBatch More Than Once Causes Error =

Article ID: 270636

Article Last Modified on 9/12/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft OLE DB Provider for Oracle Server 1.0

-



This article was previously published under Q270636



SYMPTOMS
When you call the UpdateBatch method of an ActiveX Data Objects (ADO) Recordset object using the Microsoft OLE DB Provider for Oracle, an error may occur.

With Microsoft Data Access Components (MDAC) version 2.5, the following error message may be returned:

Run-time error '-2147217864 (80040e38)':

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

With MDAC version 2.1, the following error message may be returned:

Run-time error '-2147217864 (80040e38)':

The specified row could not be located for updating: Some values may have been changed since it was last read.



CAUSE
When you have an out-of-process DLL in a 3-tier application, and the DLL is responsible for handling updates to the Oracle server, the following sequence of events may cause this error to occur:


 * 1) The DLL returns an ADO Recordset to the client.
 * 2) The client appends a new record to the Recordset and sends the Recordset back to the DLL.
 * 3) The DLL calls UpdateBatch to commit the changes to the Recordset.
 * 4) The client modifies the new record and sends the Recordset to the DLL again.
 * 5) When the DLL calls UpdateBatch again, the error occurs.

The root problem is that a hidden ROWID column in the Recordset is still set to NULL after the first update on the client-side Recordset. This is because ROWID columns are automatically incremented by the server, and in this case, the client-side Recordset is disconnected; in other words, it has no server connection and therefore cannot be updated automatically. Because this column value is never updated on the client-side, all subsequent updates using this invalid ROWID fail.



RESOLUTION
To prevent this error, set the &quot;Determine Key Columns For Rowset&quot; dynamic property on the Recordset object. The property must be set before the CursorLocation is set to adUseClient. This property is provider-specific, and although it cannot be set once you've asked for a client-side cursor, the property value is retained and used even after the cursor location changes.

Also, in order to ensure that this property is used by the provider, set the ActiveConnection property of your Recordset before setting the &quot;Determine Keys For Rowset&quot; property, and do not pass a connection string into the Open method call for your Recordset.

In the code example in the &quot;More Information&quot; section, the error occurs even though this property is set, because a connection string is passed into the Open call. This forces ADO to create a new Connection object to execute the query, and the property setting is not saved. See the comments within the code for more information on how to prevent this from happening.



Steps to Reproduce Behavior
  Create a table on an Oracle server by using the following SQL statement: create table mhtest (test_id number primary key, txt_fld varchar2(10));  In Microsoft Visual Basic 6.0, create a new ActiveX DLL project named &quot;testmdac&quot;. Set a Project Reference to the ActiveX Data Objects 2.5 Library.  Paste the following code in the default class module of the project. You will need to change the connection strings below to work with your server: Option Explicit

Public Function Load1 As ADODB.Recordset Dim strconnect As String Dim rs As ADODB.Recordset Dim cn As ADODB.Connection Dim strSQL As String Set rs = New ADODB.Recordset Set cn = New ADODB.Connection 'Fails only with OLE DB Provider for Oracle. strconnect = &quot;provider=msdaora;password=password;user id=user;data source=oraclesrv;&quot; 'Works with the following: 'strConnect = &quot;provider=msdasql;DRIVER={Microsoft ODBC for Oracle};pwd=password;uid=user;server=oraclesrv;&quot;

With cn      .ConnectionString = strconnect .Open End With

'Get the SQL statement. strSQL = &quot;SELECT * FROM mhtest&quot;

With rs       .ActiveConnection = cn        'Note: CursorLocation should be set to adUseServer by default. .CursorLocation = adUseServer .Properties(&quot;Determine Key Columns For Rowset&quot;) = True .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic 'The following causes the above dynamic property not to be used. .Open strSQL, strconnect, adOpenStatic, adLockBatchOptimistic

'Instead, use the following: '.Open strSQL,, adOpenStatic, adLockBatchOptimistic 'Disconnect the recordset. Set .ActiveConnection = Nothing End With

Set Load1 = rs   Set rs = Nothing End Function

Public Sub Save(ByVal rs As ADODB.Recordset) Dim strconnect As String Dim cn As ADODB.Connection Set cn = New ADODB.Connection 'Fails only with OLE DB Provider for Oracle. strconnect = &quot;provider=msdaora;password=password;user id=user;data source=oraclesrv;&quot; 'Works with the following: 'strConnect = &quot;provider=msdasql;DRIVER={Microsoft ODBC for Oracle};pwd=password;uid=user;server=oraclesrv;&quot;

With cn      .ConnectionString = strconnect .Open End With With rs      .ActiveConnection = cn       .UpdateBatch '<-- Error will occur here second time around Set .ActiveConnection = Nothing End With End Sub  From the Run menu, click Start with full compile so that you can test the DLL out of process. If prompted, choose Wait for Components to be created and then click OK. Start another instance of Visual Basic 6.0.</li> Create a new Standard EXE project.</li> Set a Project Reference to the ActiveX DLL project that you just created, which is named &quot;testmdac.vbp&quot;.</li> Set a Project Reference to the ActiveX Data Objects 2.5 Library.</li>  Paste the following code in the Load method of the default form: Option Explicit

Private Sub Form_Load Dim rs As ADODB.Recordset Dim DS As testmdac.Class1 Set DS = CreateObject(&quot;TESTMDAC.Class1&quot;) Set rs = DS.Load1 rs.AddNew rs(0) = 42 rs(1) = &quot;TEST1&quot; DS.Save rs   rs.UpdateBatch rs.MoveLast rs(1) = &quot;TEST1&quot;

DS.Save rs   rs.UpdateBatch Set rs = Nothing End Sub </li> Step into the code by pressing F8.

NOTE: Before you run the code, be sure to delete any existing rows in the table; otherwise, you may get an error due to a duplicate primary key.</li></ol>

You will see the error on the second call to the DLL's Save method, specifically on the UpdateBatch call.

Keywords: kboracle kbprovider kbexcel123quattro kbprb KB270636

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.