|
FAQ #5: Syntax Error in Database
Statement
90% of the problems submitted to the listserves could be
solved by systematically checking your code against these guidelines. If you complete all
of these steps, then submit it to the list, but most people will solve their problem
without the list!
Step 1: did you write the SQL statement to the browser?
....code...
conn.execute(SQLstuff)
....code...
needs to be converted to:
....code...
response.write SQLstuff
conn.execute(SQLstuff)
....code...
The SQLstatement written to the browser will allow Step 2 and
Step 3 to be checked. The code tells you little about the syntax error until you write
what the code assembles.
Step 2: Are the field names a problem?
a. Forbidden/cantankerous field names
Do not name a field date. It will create
problems.
A query like
select * from employees where date=#1/17/98#
will fail even though it is syntactically correct because date is a forbidden name.
Rename the field, so the query could look like
"select * from employees where hiredate=#1/17/98#
and you are "out of the woods"
b. Field names with spaces
A field name that has embedded spaces will create problems if
you don't surround it with square brackets when referencing it in the query.
A query like
select * from employees where state of residence='MD'
will fail even though it is syntactically correct because state of
residence is a field with spaces in the name.
A query like
select * from employees where [state of residence]='MD'
will succeed because the fieldname is delimited properly.
Step 3: Check for these common Syntax Errors?
a. Text Fields get surrounded by ' ' and
numeric fields do not!
A query like:
select * from employees where city=Rockville and State=MD and
yearsresiding>9
will fail because the text fields were not delimited properly.
A query like:
select * from employees where city='Rockville' and State='MD' and
yearsresiding>9
succeeds.
b. Date fields get surrounded by # #
A query like:
select * from employees where birthdate>1/1/1966
will fail because the date fields were not delimited properly.
A query like:
select * from employees where birthdate>#1/1/1966#
succeeds.
|