"Select * from table where MyFormVar > FieldValue" doesn't work

Discussion in 'ASP General' started by Nicolae Fieraru, Jul 17, 2003.

  1. Hi All,

    I try to build an asp page and I try to execute this sql string:

    dim weight
    weight = CLng(Request.Form("Weight")
    strQ = "SELECT * FROM tbFreightPrices WHERE MinWeight < weight"
    objRS.Open strQ, objConn

    What happens is that although I get corectly the weight from a form, I can't
    use it in the strQ.
    If weight = 345 I can display it in the page, I can do math operations with
    it, but I can't use it in strQ. If I modify strQ = "SELECT * FROM
    tbFreightPrices WHERE MinWeight < 345"
    then I can execute the query. I tried to convert weight to a string, using
    CStr but I still get an error.

    Any help would be appreciated.

    Regards,
    Nicolae
    Nicolae Fieraru, Jul 17, 2003
    #1
    1. Advertising

  2. Nicolae Fieraru

    Randy R Guest

    > strQ = "SELECT * FROM tbFreightPrices WHERE MinWeight < weight"

    I think this should be...
    strQ = "SELECT * FROM tbFreightPrices WHERE MinWeight < " & weight
    Randy R, Jul 17, 2003
    #2
    1. Advertising

  3. Nicolae Fieraru

    Phill. W Guest

    "Nicolae Fieraru" <> wrote in message
    news:3f169b02$...
    .. . .
    > dim weight
    > weight = CLng(Request.Form("Weight")
    > strQ = "SELECT * FROM tbFreightPrices WHERE MinWeight < weight"


    When constructing SQL in this way remember - you are doing nothing
    more than building up as String that just /happens/ to have some text
    in it that your database will understand). You need to build it up from
    the variables you are using; there's no clever variable substitution done
    for you, so

    Dim sWeight ' as String
    sWeight = Request.Form( "weight" )
    ' Validate sWeight - must be a valid number!!!

    ' BTW: NEVER use "Select *"
    strQ = "SELECT c1, c2, c3, c4 " _
    & "FROM tbFreightPrices " _
    & "WHERE MinWeight < " & sWeight & " "
    ' Always drop in debugging code to help find problems later
    ' Response.Write "<p>SQL(" & strQ & ")</p>"

    HTH,
    Phill W.
    Phill. W, Jul 17, 2003
    #3
  4. > strQ = "SELECT * FROM tbFreightPrices WHERE MinWeight < weight"

    How does strQ know that weight is actually a variable? You've just included
    it in a string here. What happens if you have a variable named MinWeight,
    should ASP replace that value in your string also? What if you have a
    variable named SELECT?

    strQ = "SELECT ... WHERE MinWeight < " & weight

    And SELECT * is awful, by the way... name your columns, and don't use SELECT
    * in production code. (See http://www.aspfaq.com/2096)
    Aaron Bertrand - MVP, Jul 17, 2003
    #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. VB Programmer
    Replies:
    1
    Views:
    619
    VB Programmer
    Jan 26, 2006
  2. MZ
    Replies:
    7
    Views:
    815
    Ed Mullen
    Mar 17, 2008
  3. Vasu
    Replies:
    2
    Views:
    579
    Knute Johnson
    Oct 18, 2008
  4. Tilman
    Replies:
    0
    Views:
    403
    Tilman
    Mar 19, 2008
  5. rigo
    Replies:
    0
    Views:
    169
Loading...

Share This Page