PRB: ADODC Error on Reposition When EOFAction Is adDoAddNew

When you move past EOF, a new blank record is displayed and the ADO Data Control's EOFAction property is set to 2-adDoAddNew. If you do not enter data for the new record and move off the record, you get the error message:

ADO does an implicit update on a Move in a recordset. When the ADO Data Control's EOFAction property is set to 2-adDoAddNew, ADO adds a new record and allows the user to edit the record in bound controls. When the user moves off of the newly-created record, ADO does a recordset Update. If the user has decided not to add a record, leaves the bound controls blank, and moves off the newly-created record, the attempted update causes ADO to display the message, "Cannot insert an empty row - must have at least one column value set".

The ADO Data Control's EOFAction - adDoAddNew behavior is different than the DAO Data Control's EOFAction - Add New behavior and the Remote Data Control's EOFAction - rdAddNew behavior. You will not get a message if you move off of a newly-added record when using the Data Control or the Remote Data Control.

You can work around this behavior by adding error handling code in the ADO Data Control's Error event procedure. Here is an example of code to handle this specific error, which is error number 16389:

This behavior is by design.

Steps to Reproduce Behavior

This example uses the Biblio.MDB database that is included with Visual Basic.

1. In Visual Basic, create a new Standard EXE project. Form1 is created by default.
2. From the Project menu, choose Components, and then select the Microsoft ADO Data Control.
3. Draw a Text box and an ADO Data Control on Form1.
4. Set the ADO Data Control's properties:
   
   
   1. Set the ConnectionString property (change the path in the DataSource string below to reflect the location of Biblio.MDB on your system):

               Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Program
                    Files\Microsoft Visual Studio\VB98\Biblio.mdb
         
      								

   2. Set the EOFAction property to 2-adDoAddNew.
   3. Set the RecordSource property to Authors.
5. Set the Text box's properties.
   
   
   1. Set the DataSource property to ADODC1.
   2. Set the DataField property to Author.
6. Test the application.
   
   
   1. Click run. The text box should display the first author.
   2. Click the ADODC's MoveLast button. The text box will display the last author.
   3. Click the ADODC's MoveNext button. Because you set the ADODC's EOFAction to 2-adDoAddNew, you will see a blank text box, ready for your data entry.
   4. Click the ADODC's MovePrevious button, to cancel your AddNew. You will see the message:
      Cannot insert an empty row - must have at least one column value set.
      Click the Message box's OK button.
   5. To continue, you must either type a new author's name in the Text box, then move off the record, or click Form1's Close button.
7. Add the work around code:
   
   
   1. In Design mode, double-click on the ADO Data Control to open its Event Procedure window.
   2. Copy the following code into the ADO Data Control's Error event procedure:

               If ErrorNumber = 16389 Then
                  If Adodc1.Recordset.State = ADODB.adStateOpen Then
                      Adodc1.Recordset.CancelUpdate
                      Adodc1.Recordset.MoveLast
                      fCancelDisplay = True
                  End If
               End If
         
      								

8. Retest the application. You will no longer get the error message.

In Visual Basic, select Help, Search... and enter "ADO Data Control".

On the form containing your ADO Data Control, select the control, then press the F1 key.