Microsoft KB Archive/190642

= PRB: ADODC Error on Reposition When EOFAction Is adDoAddNew =

Article ID: 190642

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q190642



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

Cannot insert an empty row - must have at least one column value set.



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



RESOLUTION
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: Private Sub Adodc1_Error(ByVal ErrorNumber As Long, _       Description As String,  ByVal Scode As Long, _        ByVal Source As String, ByVal HelpFile As String, _        ByVal HelpContext As Long, fCancelDisplay As Boolean)

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



STATUS
This behavior is by design.



Steps to Reproduce Behavior
This example uses the Biblio.MDB database that is included with Visual Basic.  In Visual Basic, create a new Standard EXE project. Form1 is created by default. From the Project menu, choose Components, and then select the Microsoft ADO Data Control. Draw a Text box and an ADO Data Control on Form1. Set the ADO Data Control's properties:

  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 </li> Set the EOFAction property to 2-adDoAddNew.</li> Set the RecordSource property to Authors.</li></ol> </li> Set the Text box's properties.

<ol style="list-style-type: lower-alpha;"> Set the DataSource property to ADODC1.</li> Set the DataField property to Author.</li></ol> </li> Test the application.

<ol style="list-style-type: lower-alpha;"> Click run. The text box should display the first author.</li> Click the ADODC's MoveLast button. The text box will display the last author.</li> 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.</li> 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.</li> 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.</li></ol> </li> Add the work around code:

<ol style="list-style-type: lower-alpha;"> In Design mode, double-click on the ADO Data Control to open its Event Procedure window.</li>  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                                </li></ol> </li> Retest the application. You will no longer get the error message.</li></ol>

<div class="references_section">