PRB: IRowsetNotify Error with ADO Data Control and ADO Recordset

• Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0
• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

SYMPTOMS

When manipulating methods of the ADO Data Control's Recordset property through code, or when manipulating an ADO Recordset object that has controls bound to it, you receive the following error message:

Run-time error '-2147217888 (80040e20)':
Provider called a method from IRowsetNotify in the consumer and the
method has not yet returned.

CAUSE

Microsoft is currently investigating the cause of this bug.

RESOLUTION

1. Use a client-side cursor instead of a server-side cursor.
   
   -or-



2. Add a call to the Recordset's Move method immediately prior to the offending line. For example:

   
         ADODC1.Recordset.Move 0
    

   Resolution number 2 is the workaround discussed in the "More Information" section below.


-or-


3. Service Pack 3 for Microsoft Visual Basic has been found to resolve this error in some scenarios. For information on installing Service Pack 3 of Microsoft Visual Basic, please see the following article in the Microsoft Knowledge Base:




Q194022 INFO:Visual Studio 6.0 Service Packs, What, Where, Why

MORE INFORMATION

The ADO data control and the ADO Recordset object sometimes do not respond correctly to IRowsetNotify events raised by the data provider. There are currently three known methods to generate this error. The workaround described above works in each of these cases. It should also be tried in any unconfirmed cases.

Notes:

1. The error does not appear when using client-side cursors, only when using server-side cursors (ServerLocation = adUseServer). However, it may not always be feasible or desirable to use client-side cursors.



2. Though the article demonstrates the error using the Microsoft OLE DB Provider for Jet, the error is not limited to this provider.



3. Though the article demonstrates the error using the ADO Data Control, the first two methods also can cause the error when using an ADO Recordset object bound as the DataSource of the controls.

Method A - Adding a Record

1. Create a Standard EXE project in Visual Basic. Form1 is created by default.



2. Use the Projects | Components dialog to register the following controls with the project:




      Microsoft ADO Data Control 6.0 (OLEDB)
      Microsoft DataList Controls 6.0 (OLEDB)
 

3. On the default form (Form1), add the following controls:




      ADO Data Control
         Name = ADODC1
         CursorLocation = adUseServer
         CursorType = adOpenKeyset
         LockType = adLockOptimistic
         ConnectString = Provider=Microsoft.Jet.OLEDB.3.51;
                                  Data Source=nwind.mdb
         RecordSource = Orders

      ADO Data Control
         Name = ADODC2
         CursorLocation = adUseServer
         CursorType = adOpenKeyset
         LockType = adLockOptimistic
         ConnectString = Provider=Microsoft.Jet.OLEDB.3.51;
                                  Data Source=nwind.mdb
         RecordSource = Customers

      ADO Data Combo
         Name = DataCombo1
         DataSource = ADODC1
         DataField = CustomerID
         RowSource = ADODC2
         ListField = CompanyName
         BoundColumn = CustomerID

      Command Button
         Name = cmdAddNew
         Caption = Add New

      Command Button
         Name = cmdUpdate
         Caption = Update
 

4. Fix the connect string property on the ADO Data Controls to point either to NWIND or to the database of your choice on your system.



5. Add the following code to the form:




      Private Sub cmdAddNew_Click()
        ADODC1.Recordset.AddNew
      End Sub

      Private Sub cmdUpdate_Click()
        ' ADODC1.Recordset.Move 0
        ADODC1.Recordset.Update
      End Sub
 

6. Run the project, click cmdAddNew, and select a customer from the Data Combo.



7. Click cmdUpdate and note that you receive the error.



8. Uncomment the first line in cmdUpdate_Click and redo steps 6-9. The error does not occur.

1. The error also does not appear if you use a text box or the standard Combo box to select customer codes from, indicating the problem may be with the Data Combo.



2. The error can also be reproduced using a standard ADO Recordset as the DataSource of the DataCombo. However, the other two methods described below do not use the Data Combo at all.

Method B - Updating a Record

1. Create a Standard EXE project in Visual Basic. Form1 is created by default.



2. Use the Projects | Components dialog to register the following control with the project:




      Microsoft ADO Data Control 6.0 (OLEDB)
 

3. Add an MDI form to the project (MDIForm1).



4. On the MDI form, add a Picture Box control, and, in the Picture Box control, add a CommandButton (cmdBadUpdate).



5. Add the following code to the MDI form:




         Private Sub MDIForm_Load()
           Form1.Show
         End Sub

         Private Sub cmdBadUpdate_Click()
           ' MDIForm1.ActiveForm.ADODC1.Recordset.Move 0
           MDIForm1.ActiveForm.ADODC1.Recordset.Update
         End Sub
 

6. On the default form (Form1), set the MDIChild property to True.



7. On the default form (Form1), add the following controls:




      ADO Data Control
         Name = ADODC1
         CursorLocation = adUseServer
         CursorType = adOpenKeyset
         LockType = adLockOptimistic
         ConnectString = Provider=Microsoft.Jet.OLEDB.3.51;
                                  Data Source=nwind.mdb
         RecordSource = Orders

      Text Box
         Name = Text1
         DataSource = ADODC1
         DataField = CustomerID

      Text Box
         Name = Text2
         DataSource = ADODC1
         DataField = OrderDate

      Text Box
         Name = Text3
         DataSource = ADODC1
         DataField = ShippedDate

      Command Button
         Name = cmdUpdate
         Caption = Update
 

8. Fix the connect string property on the ADO Data Control to point either to NWIND or to the database of your choice on your system.



9. Add the following code to Form1:




      Private Sub cmdUpdate_Click()
        ADODC1.Recordset.Update
      End Sub
 

10. Run the project and verify that you see an order.



11. Edit both date fields (just one won't do it).



12. Click the CommandButton in the MDI form. You will get the error.



13. Repeat steps 10-12, but click on the "Update" button on Form1. The error does not occur.



14. Uncomment the first line in cmdBadUpdate_Click on the MDI form and repeat steps 10-12. The error does not occur.
    
    NOTE: You can also reproduce the error if you replace the DataSource of the three text boxes with a reference to an ADO Recordset object.

Method C - Deleting a Record

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Malcolm Stewart, Microsoft Corporation

Additional query words:

Keywords : kbADO200 kbDatabase kbDataBinding kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbprb