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

[Nicolae Fieraru]

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

 

[Randy R]

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

I think this should be...
strQ = "SELECT * FROM tbFreightPrices WHERE MinWeight < " & weight

 

[Phill. W]

.. . .

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.

 

[Aaron Bertrand - MVP]

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)