none
Data type mismatch in criteria expression. Please help

    Question

  •  

    <% @codepage=950%>
    <!-- #include virtual="common/adovbs.inc" -->


    <%
    sid = "1"
    Dim Connect, RS, Query
    Set Connect = Server.CreateObject("ADODB.Connection")
    Connect.Open "81231888-katiga"
    Set RS = Server.CreateObject("ADODB.Recordset")

    Query = "SELECT * FROM lunch_other where LOid = '"& sid &"'"

    response.write Query

    RS.Open Query, Connect, adOpenDynamic, adLockOptimistic

    'if rs.eof then

         response.write "Testing OK!!" 
        
    'Else
     response.write trim(rs("LOid"))
      response.write trim(rs("LMenu"))

    connect.close
    end if
    %>

    Error Result

     

    SELECT * FROM lunch_other where LOid = '1'

    Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

    [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

    /lunchset/test3.asp, line 16

    Friday, March 14, 2008 12:13 PM

All replies

  • William,

    The problem appears to be passing the sid:

    LOid = '"& sid &"'"

    The query is passing the value as a character, which should implicitly convert to a numeric if that is the true data type:

    SELECT * FROM lunch_other where LOid = '1'

    What is the datatype of LOid?

     

    Friday, March 14, 2008 12:19 PM
  •  WILLIAMCYTSE wrote:

    Query = "SELECT * FROM lunch_other where LOid = '"& sid &"'"

     

    In addition to David's (derekman) response, when using 'wildcards' the equals does not work.

    You need to use the LIKE keyword.

     

    Assuming that LOID is a character datatype, then something more in this fashion:

     

    Query = "SELECT * FROM lunch_other where LOid LIKE '"& sid &"'"

    Friday, March 14, 2008 10:41 PM
  • I see that the query that is dynamically created has a correct syntax. Response.Write writes the following to the output:

     

    SELECT * FROM lunch_other where LOid = '1'

     

    So the syntax looks correct. I guess this query is executed on an ACCESS database. Since the error is 'data type mismatch', i suspect the column LOID is a number and the value passed in is text. SQL Server does an automatic conversion. But does access do that ?

     

    Try generating another version of the query without the quotes around the value.

    Tuesday, March 18, 2008 10:37 AM
  •  

    Looks to me like it's the presence of the single quote marks around "1" that is causing the problem. You might try this instead:

     

    Query = "SELECT * FROM lunch_other where LOid = "& sid

     

    Ron

     

    Tuesday, March 18, 2008 4:11 PM