Need Help Running Stored Procedure in ASP

[bake1234]

I have a web page in which I would like to use a query string like http://ereply.us/q/l.asp?id=383966476

This takes the user to a page with the following code:

<!-- #include file="database_connection.asp" -->
<%
if not isempty(request.querystring("id")) then
  sql="exec dbo.fp_qr_code @sequence =(request.querystring("id"))"
 
if rs.state=1 then rs.close
   rs.open sql,conn,3,2
  
   if rs.recordcount<>0 then
     session("name") = rs("name")
  session("jobid") = rs("jobid")
  session("seq") = rs("seq")
 
if rs("id")<>"" then response.Redirect("index.asp")
end if
end if
%>

This queries a SQL database to return the "Name" value. In turn, as the code implies the user should be directed to "Index.asp" which contains:

Welcome  <% response.write(session("name"))%>

And the page should render the page with "Welcome Name".  Instead I get a 500 Server Error.  The Stored Procedure works as a stand-alone query in SQL Management Studio.

Any ideas would be greatly appreciated.

[bregent]

You have a variable inside your sql string which will never be evaluated. I think you'd be much better off using the command object, but if you still want to use the Open method, try this:

  sql="exec dbo.fp_qr_code @sequence =" & request.querystring("id")

[bake01]

I made that change, but it is still creating a 500 Server Error.  I do appreciate your effort, however.

[bregent]

I always used the command object for stored procedures. That's what I would suggest at this point.

[bake1234 · Accepted solution]

I resolved this.  Here is the query that worked:

  <%
  sql="exec fp_qr_code '" & request.querystring("id") & "'"
set rs = Conn.Execute(sql)
if rs.eof then response.write("No Records")
while not rs.eof
response.write (rs("name"))

response.write (rs("imgname"))

response.write (rs("url"))

rs.movenext
wend
%>

[bregent]

Of course. The sproc parameter needed to be quoted. Good catch.