BUG: Error Message "Rows Must Be Released" with SQLOLEDB and ADO Recordset Events

If a user opens an ActiveX Data Object (ADO) recordset by using the OLE DB Provider for SQL Server, sets the CursorLocation property to adUseServer, uses the WithEvents keyword, updates the same record more than once, and then executes a Move method such as MoveNext, the following error message appears

in MDAC versions 2.5 and later: in prior MDAC versions:

This error message can be avoided in one of the following ways:

• Using a CursorLocation property of adUseClient.
  
  -or-
  
  
• Implementing code that moves off the changed record after each Update (for example, a MoveNext and MovePrevious method pair).
  
  -or-
  
  
• Executing the Requery method of the Recordset after each Update.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce Behavior

1. Create a new Visual Basic Standard EXE project in Visual Basic. Form1 is created by default.
2. Set a reference to Microsoft ActiveX Data Objects (ADO).
3. Enter the following code into Form1's Code Window. Modify the ConnectionString to connect to your SQL Server:
   
   Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

   Option Explicit
   Dim WithEvents rs As ADODB.Recordset
   
   Private Sub Form_Load()
     Dim cn As ADODB.Connection
     Set cn = New ADODB.Connection
     With cn
       .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=servername;"
       .CursorLocation = adUseServer
       .Open
     End With
     Set rs = New ADODB.Recordset
     With rs
     Set rs.ActiveConnection = cn
       .CursorLocation = adUseServer
       .CursorType = adOpenDynamic
       .Open "select * from Authors", cn, adOpenDynamic, adLockOptimistic
       .Fields("au_lname").Value = "footfoot1"
       .Update
       .Fields("au_lname").Value = "footfoot2"
       .Update
       .MoveNext
     End With
   End Sub

4. Run the code. Note the error raised on the .MoveNext method.