ASP and SQL varchar value to data type int error

piemckay
piemckay used Ask the Experts™
on
I pass a variable through my URL which is used as part of an SQL query.

But the query doesnt seem to like the variable.

Here is the error message and the URL is visible.

Browser Error
and here is the code:

<%
Dim rec_id
rec_id = Request.QueryString("code")
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_objConnect_STRING
Recordset1_cmd.CommandText = "SELECT * FROM dbo.tblNews WHERE id='rec_id'" 
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>

Open in new window


I tried casting rec_id with CInt but then I got "type mismatch with CInt".

I have also tried it without the quotes in the URL variable and the same problem.

Same Error
Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Commented:
rec_id is an integer. Get rid of single quotes in SQL statement


<%
Dim rec_id as integer
rec_id = Request.QueryString("code")
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_objConnect_STRING
Recordset1_cmd.CommandText = "SELECT * FROM dbo.tblNews WHERE id=" & rec_id
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>

Open in new window

Author

Commented:
OFCOURSE!!! urgh thanks

Didnt click thats how we join variables to strings in VB.
Gerry BartleySoftware Engineer

Commented:
Glad to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial