PRB: Error When You Use adLockBatchOptimistic with OLE DB Provider

Article translations Article translations
Article ID: 261297 - View products that this article applies to.
This article was previously published under Q261297
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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
				

MORE INFORMATION

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.

Properties

Article ID: 261297 - Last Review: February 28, 2014 - Revision: 4.1
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
Keywords: 
kbnosurvey kbarchive kberrmsg kbprb KB261297

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com