select query data type mismatch

Discussion in 'ASP General' started by eyoung1@uiuc.edu, Jul 1, 2008.

  1. Guest

    this works

    sSQL = "SELECT *" & _
    " FROM Expenses2008" & _
    " WHERE Amount Like '%" & Request.Form("searchItem") & "%'"
    set rs = Connect.Execute(sSQL)

    however if I enter an amount of 99 it not only gives me all entries
    with 99.00 in the Amount collum but 199.00, 1991.72...anything with
    two 9s together.

    So I tried

    sSQL = "SELECT *" & _
    " FROM Expenses2008" & _
    " WHERE Amount = '" & Request.Form("searchItem") & "'"
    set rs = Connect.Execute(sSQL)

    But I get an error message

    Microsoft JET Database Engine error '80040e07'
    Data type mismatch in criteria expression.
    /eforms/shiprec/search.asp, line 201


    Can someone help me with this?
    , Jul 1, 2008
    #1
    1. Advertising

  2. wrote:
    > this works
    >
    > sSQL = "SELECT *" & _
    > " FROM Expenses2008" & _
    > " WHERE Amount Like '%" & Request.Form("searchItem") & "%'"
    > set rs = Connect.Execute(sSQL)
    >
    > however if I enter an amount of 99 it not only gives me all entries
    > with 99.00 in the Amount collum but 199.00, 1991.72...anything with
    > two 9s together.
    >
    > So I tried
    >
    > sSQL = "SELECT *" & _
    > " FROM Expenses2008" & _
    > " WHERE Amount = '" & Request.Form("searchItem") & "'"
    > set rs = Connect.Execute(sSQL)
    >
    > But I get an error message
    >
    > Microsoft JET Database Engine error '80040e07'
    > Data type mismatch in criteria expression.
    > /eforms/shiprec/search.asp, line 201
    >
    >
    > Can someone help me with this?

    When you use Like, Jet converts the numeric data in your Number field to
    strings in order to do the comparison.
    When you use = no implicit conversion is performed. Since you are
    comparing data contained in a column whose datatype is number to a
    literal value contained in quotes (a string) a data type mismatch
    occurs. You need to remove the quotes from this line:
    " WHERE Amount = '" & Request.Form("searchItem") & "'"
    so that it reads:
    " WHERE Amount = " & Request.Form("searchItem")

    Further points to consider:
    Your use of dynamic sql is leaving you vulnerable to hackers using sql
    injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See here for a better, more secure way to execute your queries by using
    parameter markers (tokens):
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, or saved parameter queries
    as they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 1, 2008
    #2
    1. Advertising

  3. Guest

    wow...that was too easy.

    > " WHERE Amount = '" & Request.Form("searchItem") &  "'"
    > so that it reads:
    > " WHERE Amount = " & Request.Form("searchItem")
    >


    Not a problem...internal server used by only 15 people.

    > Further points to consider:
    > Your use of dynamic sql is leaving you vulnerable to hackers using sql
    > injection:http://mvp.unixwiz.net/techtips/sql....sqlsecurity.com/DesktopDefault.aspx?tabid=23
    >


    Thanks!
    , Jul 1, 2008
    #3
  4. wrote on Tue, 1 Jul 2008 13:37:10 -0700 (PDT):

    > wow...that was too easy.


    >> " WHERE Amount = '" & Request.Form("searchItem") & "'"
    >> so that it reads:
    >> " WHERE Amount = " & Request.Form("searchItem")



    > Not a problem...internal server used by only 15 people.


    What happens when one of those people decides they're going to leave the
    company and aren't happy and puts something in the searchItem field of the
    form that results in a SQL injection that does something to your data?

    >> Further points to consider:
    >> Your use of dynamic sql is leaving you vulnerable to hackers using
    >> sql
    >> injection:http://mvp.unixwiz.net/techtips/sql-injection.htmlhttp://
    >> www.sqlsecurity.com/DesktopDefault.aspx?tabid=23



    > Thanks!


    --
    Dan
    Daniel Crichton, Jul 2, 2008
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. data type mismatch error

    , Aug 10, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    634
    Karl Seguin
    Aug 10, 2005
  2. psychomad
    Replies:
    2
    Views:
    5,194
    Alexey Smirnov
    Apr 10, 2007
  3. Steve
    Replies:
    16
    Views:
    319
    Steven Burn
    May 30, 2006
  4. .Net Sports

    Type mismatch problems with select box list

    .Net Sports, Dec 21, 2009, in forum: ASP General
    Replies:
    10
    Views:
    1,101
    .Net Sports
    Dec 22, 2009
  5. programmingzeal
    Replies:
    0
    Views:
    1,111
    programmingzeal
    May 6, 2012
Loading...

Share This Page