PRB: Migrating SQL Statements with Quotes From DAO to ADO

Last reviewed: February 27, 1998
Article ID: Q181832

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.0, 1.5

SYMPTOMS

ActiveX Data Objects (ADO) imposes no restrictions on the syntax used to generate a recordset or execute a statement. This comes from the underlying native provider, such as the OLE DB provider for Index Server, or the OLE DB provider for ODBC drivers. Valid syntax required for using ADO successfully is defined by the underlying OLE DB provider. When migrating SQL syntax to ADO, you will need to consider this for SQL statements such as the following, which works when using DAO to go to the Microsoft Access Jet engine:

   INSERT INTO Authors ( Au_ID, Author ) VALUES (  54, "Record # 54" )

However, with ADO (to the OLE DB provider for ODBC drivers via the Microsoft Access ODBC driver) this syntax generates an error and fails to execute.

With ADO, this syntax generates an error, 0x80040E10 or -2147217904, or the following error message:

   Too few parameters.  Expected 1.

Instead, you would have to use the following syntax:

   INSERT INTO Authors ( Au_ID, Author ) VALUES (  54, 'Record # 54' )

This is only one example and specific to DAO/Jet.

CAUSE

You should examine the Online help for the underlying provider and for the datastore it exposes in order to determine the correct syntax to provide your ADO code. As in the case above, even though DAO and the Microsoft Access ODBC Driver both use Jet, the syntax differed slightly in order for the Microsoft Access ODBC Driver to meet the ODBC specification.

STATUS

This behavior is by design.

MORE INFORMATION

Errors raised by the provider (or the datastore it exposes) are stored in the ADO Errors collection. Whereas errors raised by ADO are exposed by the native error handling of the language you are using. For Visual Basic for Applications (VBA) and/or VBScript this is the Err object. For C++ or Java this is typically some kind of exception, although with C++ you may just have to check for a failed HRESULT if manipulating ADO directly via COM.

REFERENCES

For additional information, please see the following Visual Basic article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q178070
   TITLE     : HOWTO: Handle Quotes and Pipes in Concatenated SQL Literals

This article, while written for DAO, has handy routines for processing a string to be used in a SQL statement that contains special characters. It can easily be adapted for non-DAO syntax requirements.

For additional information on error handling, please see the following Visual Basic article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q167957
   TITLE     :INFO: Extracting Error Information from ADO in VB

Keywords          : adoall adoengdb
Component         : dao jet
Technology        : odbc ole
Version           : WINDOWS:1.0,1.5
Platform          : WINDOWS
Issue type        : kbprb

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 27, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.