This error is thrown up by the underlying OLEDB Provider. The actual error message is:
The command contained one or more errors
The error message that you see on the screen will vary depending on the MDAC that
you have installed and the actual cause of the error.
There are a number of possible causes:
Scenario 1 - Syntax error in FROM clause
This commonly occurs when you have a table name that is a reserved word or your table name
contains a space (eg "table 1").
Certain words (like table, field, date, select, password, level
etc) are reserved by either ADO, OLEDB or by Access for use as commands or system objects.
You can get a list of words reserved by the Jet database engine in Microsoft Knowledge article:
Q248738. The Microsoft
MDAC Platform SDK
outlines ADO/OLEDB reserved words.
You should never use these words as names for tables or fields nor should you have spaces in
your tablenames. It is recommended that you rename your offending tables/fields and adjust
your SQL statement accordingly.
If this is not possible you should enclose your offending table names with [ ] marks, eg
Scenario 2a - Syntax error in INSERT INTO statement.
This commonly occurs when your field name is a reserved word (see scenario 1 above).
Adjust your field names and SQL statement accordingly and you should avoid the problem.
If you can't adjust your fieldnames you can use [ ] marks to delimit the field names, eg
INSERT INTO table1
VALUES ('value1', 'value2')
Scenario 2b - Syntax error in UPDATE statement.
This has the same cause as Scenario 2a immediately above.
Scenario 3a - Syntax Error (Missing Operator)
This is commonly caused when some value that you are trying to SELECT/UPDATE etc contains a
single quote mark. The error that you receive looks like:
Microsoft JET Database Engine (0x80040e14)
Syntax error (missing operator) in query expression 'Name = 'O'Malleys''.
Because of the presence of the ' in the name O'Malleys the database engine thinks that
you are constructing a WHERE clause like:
WHERE name = 'O'
and doesn't know what to do with the rest of the name (Malleys). To solve this problem you
need to use the Replace() function and replace all single quotes with two single quotes. It
is recommended that you put the Replace() function above into a user-defined function and
call as necessary within your page.
Click here for an example of such a function.
Scenario 3b - Syntax Error (Missing Operator) - incorrect delimiters
This error can also be caused when attempting an INSERT or UPDATE SQL statement and you have used
Field Type (Access)
Field Type (SQL Server)
Any numeric type
# (Access), ' (SQL Server)
A sample query in Access that uses all these field types would look like:
SELECT field1, field2, field3
WHERE someTextField = 'somevalue'
AND someNumericField = 5
AND someDateField = #2001/12/31#
Scenario 4 - Division by Zero
Dates need to be delimited with # marks when passed to the Jet Database Engine. This
marks them as a date literal. Access then takes the literal date and converts it into a
number. Alternatively, you can pass an integer to Access, and Access will construct a date
based on that number.
However if you do this:
WHERE field1 = 01/01/00
Access will treat this as an integer - namely 1 divided by 1 divided by 0, resulting in a "division by zero"
error. Instead you need to write your SELECT query as:
WHERE field1 = #01/01/00#
Scenario 5 - Mismatched CommandTypeEnum value
When opening a recordset you can pass an optional CommandTypeEnum value (eg adCmdText, adCmdTable or
adCmdStoredProc) to ADO to help it identify the source of your data. If you choose to specify a database
table as your data source, but pass the CommandTypeEnum value for adCmdText, then you will receive the
following error message. Either change the CommandTypeEnum to adCmdTable or adCmdTableDirect -or- use
an SQL query as the source of your recordset.
Microsoft JET Database Engine (0x80040E14)
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
Back to FAQ Listing |