PRB: Error When You Use adLockBatchOptimistic with OLE DB Provider (261297)

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

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.