Why doesn't SELECT statement work?

[bigzebra]

Good afternoon,

Can someone tell me why my SELECT statement is not returning a record? When I hard code the record ID (12) in the SELECT statement, the record is returned. I want to grab any record ID from a form and use that to search the database, so I have a variable in the SELECT statement, but have been unsuccessful getting it to return a record.

Here is some of the applicable code

Dim strSearch ' The text being looked for
Dim strEvalRid ' evaluator record ID field

' Retreive the term being searched for.
strSearch = Request.QueryString("search")
strEvalrid = strSearch

from the form: <input name="search" value="<%= strSearch %>" />

Response.Write(93) (line number in code to debug)
Response.Write "strSearch"
Response.Write(strSearch)
Response.Write "strEvalrid"
Response.Write(strEvalrid)

93 strSearch12 strEvalrid12 (returned value of 12 from the form, so I know it's there)

1 strSQL = "SELECT last_name, email FROM roster WHERE recid='12'; "

2 strSQL = "SELECT last_name, email FROM roster WHERE recid='strEvalrid'; "

3 strSQL = "SELECT last_name, email FROM roster WHERE recid='strSearch'; "

Option 1 returns the record; Options 2 & do not. recid is a text field in the database.

Thank you!!

 

[WayneRyan]

big,

2 strSQL = "SELECT last_name, email FROM roster WHERE recid = '" & strEvalrid & "';"

3 strSQL = "SELECT last_name, email FROM roster WHERE recid = '" & strSearch & "';"

hth,
Wayne

 

[Banana]

Are you using VB.NET? The syntax you showed doesn't seem to be VBA, and it's usually good idea to either state the environment you're using so we don't give out answers that may not apply.

Fortunately, the answer should work regardless of environment. The problem is that you are not properly embedding the variables.

It should look like this:

"SELECT last_name, email FROM roster WHERE recid='" & strEvalrid & "'; "

Furthermore, if it's a numeric data type, you don't need the delimiters (in this case the single quote mark), so omit them as well.

 

[bigzebra]

WayneRyan & Banana,
Thank you both! both suggestions work! (of course!)