Microsoft KB Archive/261297

= PRB: Error When You Use adLockBatchOptimistic with OLE DB Provider =

Article ID: 261297

Article Last Modified on 4/1/2005

-

APPLIES TO


 * Microsoft Active Server Pages 4.0
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q261297



SYMPTOMS
When you add records to an ADO recordset object and set the LockType value to adLockBatchOptimistic, you may receive the following error message:

Microsoft JET Database Engine error '80040e21'

Errors occurred

/a.asp, line xx

-or-

Provider (0x80040E54)

Number of rows with pending changes exceeded the limit.

/a.asp, line xx

-or-

Microsoft OLE DB Provider for SQL Server (0x80040E25)

Row handles must all be released before new ones can be obtained.

/a.asp, line xx



CAUSE
When you use server-side cursors for a recordset (CursorLocation = adUseServer), which is the default cursor, some OLE DB providers only support one pending row update.

SQL Server 6.5 and 7.0 only support adLockBatchOptimistic when the cursor type is keyset-driven or static with server-side cursor. In addition, keyset-driven cursors can only be supported with tables that have unique indexes.



RESOLUTION
To resolve this issue, use a client-side cursor. To do this, set the value of the recordset's CursorLocation property to adUseClient as follows: oRs.CursorLocation = adUseClient



Steps to Reproduce Behavior
  Create a new Active Server Pages (ASP) page, and paste the following code: <% Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot;) Set oRS = Server.CreateObject(&quot;ADODB.Recordset&quot;) oConn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Test.mdb&quot;

Set oRs.ActiveConnection = oConn 'oRs.CursorLocation = adUseClient 'To resolve this issue, uncomment this line. oRS.CursorType = adOpenKeySet

oRS.LockType = adLockBatchOptimistic oRS.Open &quot;TestTableName&quot;

oRS.AddNew oRS(&quot;ID&quot;) = &quot;10&quot; oRS(&quot;Description&quot;) = &quot;Testing adLockBatchOptimistic&quot; oRS.Update oRS.AddNew oRS(&quot;ID&quot;) = &quot;11&quot; oRS(&quot;Description&quot;) = &quot;Testing adLockBatchOptimistic&quot; oRS.Update oRs.UpdateBatch

oRs.Close oConn.Close

Set oRs = Nothing Set oConn = Nothing

%>                    Run the ASP page.

Additional query words: 80040e21 0x80040E54 0x80040E25

Keywords: kberrmsg kbprb KB261297

-

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

© Microsoft Corporation. All rights reserved.