01-20-2003, 03:14 PM
Can anyone tell me why I am getting the following error:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
/bl/iteminquiry.asp, line 18
Here is the code for this:
ID = request("ID")
strSQL = "Select * From Products where ProductID = '" & ID & "' " ' This is line 18
Set objRS = objConn.Execute(strSQL)
I have a Products Table and I am trying to pull a record based on the Primary Key(AutoNumber) and keep getting this error. Is there anything wrong with my syntax?
01-20-2003, 03:17 PM
strSQL = "Select * From Products where ProductID = " & ID
Since the primary key ID is an autonumber you shouldn't give it withing quotes.
01-20-2003, 04:13 PM
The line of code works if I hard code the value of my variable.
ID = "15"
But if i do
ID = Request("ID")
strSQL = "Select * From Products where ProductID =" & ID
Then I get this error:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'ProductID ='.
/bl/iteminquiry.asp, line 17
So I am unclear why this is not working!
01-20-2003, 04:19 PM
The problem is that your ID value is '' - use either Request.QueryString("ID") or Request.QueryString("ID") to write the value into ID.
01-20-2003, 04:26 PM
I don't understand what you are saying. Can you explain it a little more?
01-20-2003, 05:02 PM
If you use Response.Write to print SQL statement to the screen, you'll see, that ID variable's value is empty:
Select * From Products where ProductID =
The reason for this is that you don't set any value to variable named ID. To set value to this variable try using Request.Form or Request.QueryString (depending on the type of request).
Also check if the field's name or querystring name is correct.
01-20-2003, 05:28 PM
Finally, I got it all figured out. Thanks Bostjan for telling to print my sql statement. In doing this I realized that in my <form Action=> I was submitting the form to itself but I wasn't including the ProductID and therefore when I clicked submit it was wiping out the ProductID and therefore getting an error!
01-20-2003, 11:31 PM
Actually, that's the first step I take in debugging any SQL statement:
SQL = "whatever"
'Response.Write(SQL) : Response.END 'Uncomment this line to test!