469,328 Members | 1,327 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

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

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


Jul 19 '05 #1
3 1750
> strQ = "SELECT * FROM tbFreightPrices WHERE MinWeight < weight"

I think this should be...
strQ = "SELECT * FROM tbFreightPrices WHERE MinWeight < " & weight
Jul 19 '05 #2
"Nicolae Fieraru" <no****@please.com> wrote in message
news:3f**********@news.iprimus.com.au...
.. . .
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.
Jul 19 '05 #3
> 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)
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

23 posts views Thread by ian justice | last post: by
3 posts views Thread by ribchr00 | last post: by
16 posts views Thread by Tantale | last post: by
36 posts views Thread by Roedy Green | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.