user defined SQL parameters

[turnknuckle]

Greetings

Im having a problem using user input values in my SQL statement:

The following code works fine:

strSQL2 = "SELECT * FROM " & table1 &" WHERE " & table1&".ind_pref = 'All sectors considered'AND " & table1&".min_invest = '1000000'"

But the following tells me either EOF or BOF is true:

strSQL2 = "SELECT * FROM " & table1 &" WHERE " & table1&".ind_pref LIKE '& ind_value&'AND " & table1&".min_invest LIKE '& amount_value&'"

The user input values are available to the script - these values display correctly:

Response.write amount_value%><br><br><%
Response.write ind_value%><br><br><%
Response.write firm_value%><br><br><%
Response.write table1%><br><br><%

but when i try to use 'em in my SQL statement - no joy...

Any ideas?
Thanks.

[turnknuckle]

problem solved:

strSQL2 = "SELECT * FROM " & table1 &" WHERE " & table1&".ind_pref LIKE '& ind_value&'AND " & table1&".min_invest LIKE '& amount_value&'"

should be:

strSQL2 = "SELECT * FROM " & table1 &" WHERE " & table1&".ind_pref LIKE '"& ind_value&"'AND " & table1&".min_invest LIKE '"& amount_value&"'"


:)

[rcreyes]

That's why I never use single qoutes in the string query, instead I use the Chr(39) which is the ' ,

I find it more readable, for example in your query:

strSQL2 = "SELECT * FROM " & table1 &" WHERE " & table1&".ind_pref LIKE '"& ind_value&"'AND " & table1&".min_invest LIKE '"& amount_value&"'"


Using my method:

strSQL2 = "Select * From " & Table1 & " WHERE " & Table1&".ind_pref LIKE " & Chr(39) & ind_value & Chr(39) & " AND " & Table1&".min_invest LIKE " & Chr(39) & amount_value & Chr(39)

[whammy]

Ew, I would have to respectfully disagree.

I find that much harder to read (although it should work just fine!).

Not to mention I have a clip function in TextPad, and a plugin command in HTML-KIT that automatically write either:

" & & "

or

'" & & "'

for me with the click of a button. Which is really fast if you're building a SQL Statement, and much less likely to result in a typo. :)

[rcreyes]

Well, I guess it's the matter of personal preference. Thanks.......

Ray

[whammy]

Another reason is it's a larger file. :)

If you care about that... I personally don't, lol.