Data type mismatch in criteria expression. [Archive]

aburn03

11-16-2003, 12:29 PM

I am working on an update for my product table and I am getting the following error:

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/info3240/Diesel/Angie/ASP4/recUpdate.asp, line 15

I have checked and double checked the names in my database and I know they are correct. The Product_ID is what is giving me the problem, I am assuming, but I can not figure out where I've gone wrong.

here is the recUpdate.asp file that is giving me the error

<html>
<body><h2>Update Record</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open server.mappath ("Products.mdb")

id=Request.Form("Product_ID")

if Request.form("Category_ID")="" then
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM Products WHERE Product_ID='" & id &"'" ,conn
%>

<form method="post" action="recUpdate.asp">
<table>
<%for each x in rs.Fields%>
<tr>
<td><%=x.name%></td>
<td><input name="<%=x.name%>" value="<%=x.value%>"></td>
<%next%>
</tr>
</table>
<br /><br />
<input type="submit" value="Update record">
</form>
<%
else
sql="UPDATE Products SET "
sql=sql & "Category_ID='" & Request.Form("Category_ID") & "',"
sql=sql & "Product_Name='" & Request.Form("Product_Name") & "',"
sql=sql & "Price='" & Request.Form("Price") & "',"
sql=sql & "Quantity='" & Request.Form("Quantity") & "'"
sql=sql & " WHERE Product_ID='" & id & "'"
on error resume next
conn.Execute sql
if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & id & " was updated!")
end if
end if
conn.close
%></body>
</html>

--------------Here is the update.asp file that directs the user to the recupdate.asp---------------------------------------------

<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open server.mappath("Products.mdb")

set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM Products",conn
%>

<h2>Product List</h2>
<font style = "color:red">Click on a Product ID in the table below
to change the entry </font>

<table border="1" width="100%">
<tr>
<%
for each x in rs.Fields
response.write("<th>" & ucase(x.name) & "</th>")
next
%>
</tr>

<% do until rs.EOF %>

<tr>
<form method="post" action="recUpdate.asp">
<%
for each x in rs.Fields
if x.name="Product_ID" then%>
<td>
<input type="submit" name="Product_ID" value="<%=x.value%>">
</td>

<%else%>
<td><%Response.Write(x.value)%></td>

<%end if
next
%>
</form>

<%rs.MoveNext%>

</tr>
<%
loop
conn.close
%>
</table></body>
</html>
Any ideas? :confused:

Thanks so much

yurib

11-16-2003, 12:49 PM

This is probubly problem with data types
Let say if you have in database field "Product_ID" setup as integer datatype the line in your code:
rs.open "SELECT * FROM Products WHERE Product_ID='" & id &"'" ,conn my cause the problem

To fix it use:
SELECT * FROM Products WHERE Product_ID=" & id ,conn

hope it will work for you

aburn03

11-16-2003, 01:14 PM

Worked like a charm. I feel like an idiot now though! :rolleyes:

Thank you!