PRB: IRowsetNotify Error with ADO Data Control and ADO Recordset

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.

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:

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