variable set in recordset

[taoist]

I setup a variable to pass data to the recordset to run the query. If I use the name in the query it works fine but if I use the variable it does not work.

I think it has to do with syntax but I'm not sure

can someone help me out with this.


This works
Recordset1.Source = "SELECT firstname FROM dbo.task WHERE firstname = 'Bob Smith'"

This does not work
'Recordset1.Source = "SELECT firstname FROM dbo.task WHERE firstname = '" & Recordset1__varstr & "'"

please help

 

[Sheco]

The line that doesnt work is commented out.

Other than that, the next thing to look at is the value of variable Recordset1__varstr, perhaps it is blank or the value it contains is not found in the table.

 

[taoist]

I did a

response.write Recordset1__varstr

there is a value
and there is data in the table

is the syntax correct?
even with the commented out

 

[taoist]

also column in the table is varchar

 

[Sheco]

for debugging purposes only, immediately before executing the query, try outputting the value of the SQL to the screen. Something like this:
[tt]
Response.Write Recordset1.Source[/tt]

This should produce the exact same string as when you hard-code the value.

 

[DotNetGnat]

try this:

Recordset1.Source = "SELECT firstname  FROM dbo.task  WHERE firstname = '" & trim(Recordset1__varstr) & "'"

-DNG