...

View Full Version : Data type mismatch in criteria expression error



Nige_S4
10-16-2003, 08:22 PM
Hello, I am getting the above error and can't figure out what I am doing wrong. The page reads field names from an access Db and allows you to update the field values & pressing submit writes them back to the Db.

Here is the related part of the code;

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
If Request("Submit") <> "" Then
varProdIDs = Replace(Request("hidProdIDs"), "*", "") ' remove all the asterisks, to create a list like this: 2, 5, 8, 9 etc.
varRecArray = Split(varProdIDs, ", ") ' Create an array, wich will contain just the IDs of the records we need to update
For i = 0 to Ubound(varRecArray) ' Loop through the array
%>
<%

if(Request("Delivery" & varRecArray(i)) <> "") then commUpdate__varNum1 = Request("Delivery" & varRecArray(i))

if(Request("Price" & varRecArray(i)) <> "") then commUpdate__varNum = Request("Price" & varRecArray(i))

if(Request("Stock" & varRecArray(i)) <> "") then commUpdate__varNum2 = Request("Stock" & varRecArray(i))

if(Request("hidProdID" & varRecArray(i)) <> "") then commUpdate__varProdID = Request("hidProdID" & varRecArray(i))
%>
<%
set commUpdate = Server.CreateObject("ADODB.Command")
commUpdate.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("MyData.mdb") & ";Persist Security Info=False"
commUpdate.CommandText = "UPDATE TestTable SET Price = '" + Replace(commUpdate__varNum, "'", "''") + "', Stock = '" + Replace(commUpdate__varNum2, "'", "''") + "', Delivery = " + Replace(commUpdate__varNum1, "'", "''") + " WHERE ProdID = " + Replace(commUpdate__varProdID, "'", "''") + ""
commUpdate.CommandType = 1
commUpdate.CommandTimeout = 0
commUpdate.Prepared = true
commUpdate.Execute()
%>

Here is the error code ;

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/adm1n/Update.asp, line 25


Interestingly, if I delete the 'Stock' entry, the script works OK ?

Any ideas ?

raf
10-16-2003, 09:08 PM
Heuh ..

I see a few thing i would do differently.
- specify your request collection --> request.form or request.querystring instead of request
- put the connectionstring in a server side include (SSI)
- int the sql statement, use & instead of + . Like
"UPDATE TestTable SET Price = '" & Replace(commUpdate__varNum, "'", "''")
- Delivery = " + Replace(commUpdate__varNum1, "'", "''")
--> replacing the single quote by two single quotes, is only necessary for stringvalues, not numerical values. So if 'delivery' is a numerical value, then drop the replace. If it is a stringvariable, then you need to put quotes around the value. Like
Delivery = '" & Replace(commUpdate__varNum1, "'", "''") & "'"
Same for the ProdID
So check all variabels and use
"var='" & replace(var2,"'","''") & "'" for db-variables of stringtype, and
"var=" & var2 for numerical variables
- drop the last +"" of the sql-statement


What exactlu is the line where the error points to ?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum