Select Statement help 1

[ynott]

The following select statement works and I'm using the % because this number has a hyphen and any two numbers after that that I need, for example 00010044-01:

SQL6 = "SELECT NO, LNAME, FNAME, MNAME, DOB"
SQL6 = SQL6 & " from COMP9.DBO.proc2"
SQL6 = SQL6 & " Where NO like '00010044%'"
set rs6 = my_conn.Execute(SQL6)

I'm using a query by having the user identify "EventNo". So:
Dim EventNo
and EventNo = ""
but I can't get EventNo to work with the same # of 00010044 in tho following select statement:

SQL6 = "SELECT NO, LNAME, FNAME, MNAME, DOB"
SQL6 = SQL6 & " from COMP9.DBO.proc2"
SQL6 = SQL6 & " Where NO like ' & EventNo & %'"
set rs6 = my_conn.Execute(SQL6)

Any thoughts?

 

[gmmastros]

The problem you are having is with the way you are specifying the EventNo. Quotes delimit strings in ASP. Single-quotes delimit string in SQL. Ultimately, you want a string to pass to the database that is formatted correctly.

The string you are building would look like this:

[tt][blue]SELECT NO, LNAME, FNAME, MNAME, DOB
from COMP9.DBO.proc2
Where NO like ' & EventNo & %'[/blue][/tt]

I mean... the ampersands and EventNo are hardcoded in the string, which is not what you want.

instead....

SQL6 = "SELECT NO, LNAME, FNAME, MNAME, DOB"
SQL6 = SQL6 & " from COMP9.DBO.proc2"
SQL6 = SQL6 & " Where  NO like '[!]"[/!] & EventNo & [!]"[/!]%'"
    set rs6 = my_conn.Execute(SQL6)

Notice the extra quotes I added. In ASP, the will end the string, you then concatenate the value stored in the EventNo variable, and then concatenate the rest (the percent and single quote).

This should solve your immediate problem.

Once you get this working, I strongly encourage you to do a little research on [google]SQL Injection[/google].

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ynott]

Perfect. Thanks