Visual Basic Q&A

Symptoms
With SQL Server’s quoted_identifier option set to Off, you may receive the following error:

Run-time error ‘-2147217900 (80040e14)’:
Line 1: Syntax error near ‘tablename’ This error occurs when you are using client-side cursors with the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). The error occurs on an ActiveX Data Objects (ADO) recordset’s Update method and may occur on an AddNew method.
Resolution
With ADO client-side cursors, when you invoke an ADO recordset’s AddNew or Update method, the OLE DB Provider prepares a SQL statement to send to SQL Server.
The Microsoft OLE DB Provider for SQL Server automatically quotes identifiers on an ADO recordset’s Update method and may quote identifiers on an AddNew method. Identifiers include table names and field names.
For example, updating the Titles table in the Pubs database with the following code:

MyADORecordet.Update The preceding would be prepared similar to the following:

UPDATE “titles” SET “title”=’Hello World’ WHERE “title_id”=’3′ Note that the table name is in quotes, “titles”, and that each field name is in quotes, “title”, “title_id”, and so on.
If SQL Server’s Quoted_Identifier option is set to Off, SQL Server will not recognize table names and field names enclosed in quotes.
The error “Syntax error near ‘tablename’” occurs.

Symptoms
When using the ODBC, Microsoft Access, or SQL Server OLE DB Providers thefollowing may appear:
A “0″ displays in the AutoNumber (or Identity) field after addingrecords through the DataGrid control bound to an ActiveX Data Objects(ADO) Data ControlA “0″ is stored in the AutoNumber (or Identity) field after addingrecords to a recordset, using the AddNew method of the recordset. This only occurs when the CursorLocation is set to “3″ – adUseClient.
Resolution
By using the client-side cursors, the OLE DB provider is unable to requerythe server for the updated record, and a “0″ appears in place of thecorrect value. When you requery the recordset, the correct value appears.

Symptoms
Identity columns are normally read-only because the server generates the values. However, when you migrate data from one table to another you often want to keep the existing Identity column values rather than use server-generated values. Microsoft SQL Server 7.0 allows you to do this with the SET IDENTITY_INSERT ON statement.
This operates correctly when you use INSERT INTO SQL statements. However, when you insert records through the AddNew method of an ActiveX Data Objects (ADO) Recordset, you receive the following error:

Run-time error ‘-2147217887 (80040e21)’
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.
Resolution
The Microsoft SQL Server 7.0 OLE DB provider incorrectly reports to ADO that the Identity column is read-only.