Data Type Mismatch error

Forum Moderators: open

Message Too Old, No Replies

Data Type Mismatch error

b_marks

8:56 pm on Feb 27, 2008 (gmt 0)

Having a ton of trouble with this error:

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

I'm trying to update a record's information and eve though the query works fine on other pages, I'm having a ton of issues out of nowhere. Here's the query I'm using that gets this error:

if Request("task")="update" then

SQLStmt = "UPDATE employee "
SQLStmt = SQLstmt & "SET one_1 = '" & Request("one_1") & "', one_2 = '" & Request("one_2") & "', one_3 = '" & Request("one_3") & "', one_4 = '" & Request("one_4") & "', one_5 = '" & Request("one_5") & "' "
SQLStmt = SQLStmt & "WHERE ID = " & Request("ID") & " ; "

'Response.write sqlstmt
Set RS = Connection.Execute(SQLStmt)
end if

The ID field is the primary key here and is autonumber. I have read these errors are the result of the database field not being able to accept the data, but this works on other pages, so I don't know what the heck is going on. Any help would be great. Thanks!

coopster

12:08 am on Mar 1, 2008 (gmt 0)

I would assume the "criteria expression" to be the WHERE clause and it is complaining about a data type mismatch. This likely means you are comparing a string value when the database is expecting an integer or something along those lines. What data type is the ID column? And if you were to dump the Request("ID") variable in your code, of what data type is it cast?

ZydoSEO

5:45 am on Mar 2, 2008 (gmt 0)

You have a 'Response.Write sqlstmt' statement in your code just before you execute the command object. Uncomment it (remove the apostrophe in front of it) and see what statement you are actually rendering. Post what you find here so we can see the actual statement that is triggering that error. Or better yet, only log the sqlstmt if the 'Set RS = Connection.Execute(SQLStmt)' throws an error.

Just looking at your code (I'm guessing this is ASP/VBScript (so all variables are variants), it appears you will be generating a statment similar to the following:

UPDATE employee
SET one_1 = 'AAA', one_2 = 'BBB', one_3 = 'CCC', one_4 = 'DDD', one_5 = 'EEE'
WHERE ID = someinteger;

Where AAA, BBB, CCC, DDD, and EEE are strings and ID is some integer number.

The fields one_1, one_2, one_3, one_4, and one_5 must all be defined in your DB as some type of string field - CHAR(64) or VARCHAR(64) or TEXT. ID you said was an auto number.

If for some reason Request("ID") returns nothing (as in it has no value, was never set) you would end up with something like:

UPDATE employee
SET one_1 = 'AAA', one_2 = 'BBB', one_3 = 'CCC', one_4 = 'DDD', one_5 = 'EEE'
WHERE ID = ;

which would be invalid since no value was supplied for ID. Not sure if this would throw that particular error though.