PRB: Error When You Use adLockBatchOptimistic with OLE DB Provider

This article was previously published under Q261297
This article has been archived. It is offered "as is" and will no longer be updated.
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
Provider (0x80040E54)
Number of rows with pending changes exceeded the limit.
/a.asp, line xx
Microsoft OLE DB Provider for SQL Server (0x80040E25)
Row handles must all be released before new ones can be obtained.
/a.asp, line xx
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.
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

  1. Create a new Active Server Pages (ASP) page, and paste the following code:
    <%  Set oConn = Server.CreateObject("ADODB.Connection")  Set oRS = Server.CreateObject("ADODB.Recordset")      oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Test.mdb"  Set oRs.ActiveConnection = oConn  'oRs.CursorLocation = adUseClient 'To resolve this issue, uncomment this line.  oRS.CursorType = adOpenKeySet  oRS.LockType = adLockBatchOptimistic      oRS.Open "TestTableName"  oRS.AddNew  oRS("ID") = "10"  oRS("Description") = "Testing adLockBatchOptimistic"  oRS.Update      oRS.AddNew  oRS("ID") = "11"  oRS("Description") = "Testing adLockBatchOptimistic"  oRS.Update  oRs.UpdateBatch  oRs.Close  oConn.Close  Set oRs = Nothing  Set oConn = Nothing%>					
  2. Run the ASP page.
80040e21 0x80040E54 0x80040E25

Article ID: 261297 - Last Review: 02/28/2014 00:32:33 - Revision: 4.1

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

  • kbnosurvey kbarchive kberrmsg kbprb KB261297