PRB: IRowsetNotify Error with ADO Data Control and ADO Recordset

ID: Q195638


The information in this article applies to:
  • 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.


  3. -or-

  4. 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:


  5. 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:


  3. 
          Microsoft ADO Data Control 6.0 (OLEDB)
          Microsoft DataList Controls 6.0 (OLEDB)
     
  4. On the default form (Form1), add the following controls:


  5. 
          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
     
  6. 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.


  7. Add the following code to the form:


  8. 
          Private Sub cmdAddNew_Click()
            ADODC1.Recordset.AddNew
          End Sub
    
          Private Sub cmdUpdate_Click()
            ' ADODC1.Recordset.Move 0
            ADODC1.Recordset.Update
          End Sub
     
  9. Run the project, click cmdAddNew, and select a customer from the Data Combo.


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


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


Notes:
  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:


  3. 
          Microsoft ADO Data Control 6.0 (OLEDB)
     
  4. Add an MDI form to the project (MDIForm1).


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


  6. Add the following code to the MDI form:


  7. 
             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
     
  8. On the default form (Form1), set the MDIChild property to True.


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


  10. 
          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
     
  11. 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.


  12. Add the following code to Form1:


  13. 
          Private Sub cmdUpdate_Click()
            ADODC1.Recordset.Update
          End Sub
     
  14. Run the project and verify that you see an order.


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


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


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


  18. 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 does not have a consistently reproducible sample for this error. However, it can occur when you have a Data List Combo box bound to two ADO Data controls. The error occurs when you have code that deletes a record from the Data control that is bound to the DataList Combo box's DataSource property. The error only happens if the table has one record and the Data control is using server-side cursors.

The workarounds are the same as the other two cases. Also, unlike the other two cases, using an ADO Recordset does not cause the error to occur.

© 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


Last Reviewed: November 16, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.