...

View Full Version : Error: DataType Mismatch...



tsbarnes
01-20-2003, 03:14 PM
Ok,

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?

Thanks,

tsbarnes

codefox
01-20-2003, 03:17 PM
Try

strSQL = "Select * From Products where ProductID = " & ID

Since the primary key ID is an autonumber you shouldn't give it withing quotes.

tsbarnes
01-20-2003, 04:13 PM
codefox,

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!

Thanks,

tsbarnes

bostjank
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.
Bostjan

tsbarnes
01-20-2003, 04:26 PM
bostjank,

I don't understand what you are saying. Can you explain it a little more?


Thanks,

tsbarnes

bostjank
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.

Bostjan

tsbarnes
01-20-2003, 05:28 PM
Ok,

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!

Thanks,

tsbarnes

whammy
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!
Conn.Execute(SQL)

:)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum