Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 261297 - Last Review: April 1, 2005 - Revision: 4.1
PRB: Error When You Use adLockBatchOptimistic with OLE DB Provider
This article was previously published under Q261297
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
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
%>
- Run the ASP page.
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
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate