| Answer: |
All these errors can occur in a SELECT, UPDATE, INSERT or other SQL query. Usually, each simply means that you are trying to query, change, or add to a given field but the data you are using does not match the type of that field or that the field name you are using doesn't exist.
For example, if you do
<%
SQL = "SELECT * FROM table WHERE userName = " & Request.Form("UserName")
%>
|
and the system gives you one of these errors, it's a pretty safe bet that the reason is because the field userName either doesn't exist in the table (the field name is misspelled?) or it is a text field, but you did not enclose the text value you are trying to compare it to in apostrophes. The correct form is
<%
SQL = "SELECT * FROM table WHERE userName = '" & Request.Form("UserName") & "'"
%>
|
The errors can occur in the other direction, as well:
<%
SQL = "SELECT * FROM table WHERE accountNumber = '" & Request.Form("acctNo") & "'"
%>
|
Here, a type mismatch (or, more likely with Access, a missing operator message) almost surely indicates that the field accountNumber in the table is a numeric field, and so you should not use the apostrophes around the test value.
If you are using Access, then you have the further possibility that you may be using the wrong format for a Date/Time field:
<%
SQL = "SELECT * FROM table WHERE startDate > '" & Request.Form("when") & "'"
%>
|
One of these errors from that line, assuming you are using an Access Database, presumably means that you should have surrounded the date with #...#, thus:
<%
SQL = "SELECT * FROM table WHERE startDate > #" & Request.Form("when") & "#"
%>
|
Note that Access is the only database that uses #...# for dates instead of simply '...'.
Summary: Go over your query carefully. Check for field names and types in the original database. Make sure all your usages are correct. Then try again.
|
