Visual Basic Q&A

Symptoms
If you use ADO to insert a new record through a client-side recordset into a SQL Server table that has a non-nullable datetime field with a default value, you receive the following error message if you do not supply a value for the datetime field:

Run-time error ‘-2147217887 (80040e21)’: Multiple-step operation generated errors. Check each status value. This error occurs whether you use the OLE DB Provider for SQL Server or the OLE DB Provider for ODBC Drivers. The error message may differ when you use Microsoft Data Access Components (MDAC) version 2.5 Service Pack 1 (SP1) or earlier. This error does not occur with a server-side cursor.
Resolution
This error occurs in the Client Cursor Engine when it attempts to convert the value of type DBTYPE_DBTIMESTAMP to DBTYPE_VARIANT.

Symptoms
When you update a bound text box by setting its text property in code, and then you call a CancelUpdate method, the value in the field does not revert to its original value.
Resolution
Instead of setting the text property of the bound text box, set the value of the underlying recordset field directly, as follows:

Adodc1.Recordset!LastName = “Smith”
The bound text box should update itself after you run the code. Subsequent calls to the CancelUpdate method then work as expected, and change the text box contents back to its original value.

Symptoms
When you attempt to add a new record to a recordset, the Data Access Objects (DAO) Recordset Update method may fail silently when the following conditions are met: The Recordset is based on a query that joins two tables.The table being updated has a Required field enforced by a Primary Key or Unique Index.No values are supplied for any of the fields in the Recordset.
Resolution
To work around this problem, use either of the following methods: If the joined table is unnecessary, remove the joined table from the query. Doing this causes the 3314 error to fire as expected. To remove the joined table:
Open the database in Microsoft Access.Open the query in Design View.Delete the unnecessary table from the query.Save and close the query.Prior to calling the Update method, write code that checks to ensure that a value is supplied for all fields in the table where the Required Value property is set to Yes. For example, if you are using text boxes on a form for data entry and you have a text box named txtSSN and a required field in the table named SSN, check the Text property of the txtSSN text box prior to calling the Update method to ensure it contains a value as follows:

If Trim(txtSSN.Text) = “” ThenMsgBox “The SSN field is Required. You must supply a valid SSN.”txtSSN.SetFocusExit Sub End If Prior to calling the Update method, write code that checks to ensure that a value is supplied for all fields in the table where the Required Value property is set to Yes. For example, if you are using text boxes on a form for data entry and you have a text box named txtSSN and a required field in the table named SSN, check the Text property of the txtSSN text box prior to calling the Update method to ensure it contains a value as follows:

If Trim(txtSSN.Text) = “” ThenMsgBox “The SSN field is Required. You must supply a valid SSN.”txtSSN.SetFocusExit Sub End If