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
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%>
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