Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Mar 2003
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data type mismatch in criteria expression error

    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 ?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •