Problem with stored procedure and parameter

[SchuhTL]

All,

It's been a long time since I have worked with asp and I can't figure out what I am doing wrong. I am trying to execute a stored procedure and store the results in a session variable. On my .asp page I have a include file<!--#include file = "../common/Connect.inc" -->

The Connect.inc file contains the following:
<% strConnect = "Provider=SQLOLEDB.1;test_user;pwd=test_pass;database=test;server=testserver;driver={sql server};dsn=''"%>

The .asp page looks something like this:
Dim objConn
Dim objCmd
Dim conn
Dim rsRequestor

Set objConn = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")
conn.Open Application("strConnect")

With objCmd
.ActiveConnection = conn
.CommandText = "sp_Requestor"
.CommandType = adCmdStoredProc

'Add Input Parameters
.Parameters.Append .CreateParameter("@loginname", adVarChar, adParamInput, 50, "abc@abc.com")

'Execute the function
.Execute

End With


if not rsRequestor.EOF then
session("RequestID") = rsRequestor("Requestid")
End if

rsRequestor.close
Set rsRequestor = nothing
Set Connect = nothing
Set objParam = nothing

I have several known issues:
1. This does not work
2. When I execute objCmd how do I populate the recordset so I can use the values in my if statement?

Thanks in advance.

 

[vbkris]

Set objConn = Server.CreateObject("ADODB.Connection")
set rs=server.createobject("ADODB.Recordset")

rs.open "exec ProcedureName 'Arg1','Arg2'",con

u must have the results now in a recordset...

Known is handfull, Unknown is worldfull

 

[SchuhTL]

vbkris,

Thanks for the reply. Any idea what is wrong with the syntax I had posted?

 

[vbkris]

sorry, never used that method, so couldnt debug it...

Known is handfull, Unknown is worldfull

 

[SchuhTL]

Still trying to get this right, I have revised it a bit but it still does not work. I have used query profiler to verify that the stored procedure is not executing.

Set objConn = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")
objConn.Open strConnect

objCmd.ActiveConnection = objConn
objCmd.CommandText = "usp_GetRequestor"
objCmd.CommandType = adCmdStoredProc

Set paramId = objCmd.CreateParameter("@loginname", adVarChar, adParamInput, 50)
paramId.Value = "test@test.com"
objCmd.Parameters.Append paramId
Set rsRequestor = objCmd.Execute

 

[SchuhTL]

What is the difference between the following other than one is using a stored proc? The first one works and the second one does not.

set connect = server.CreateObject("ADODB.Connection")
Connect.Open strConnect
sSql = "SELECT * from Test where loginname = '" & session("userid") & "'"
set rsTest = server.CreateObject("ADODB.recordset")
rsTest.Open sSql ,connect,adOpenKeyset


and this


Set connect = Server.CreateObject("ADODB.Connection")
set objCmd = Server.CreateObject("ADODB.Command")
set rsTest = server.CreateObject("ADODB.recordset")
connect.Open strConnect

objCmd.ActiveConnection = connect
objCmd.CommandText = "usp_GetTest"
objCmd.CommandType = adCmdStoredProc

Set paramId = objCmd.CreateParameter("@loginname", adVarChar, adParamInput, 50)
paramId.Value = "Test_Login"
objCmd.Parameters.Append paramId
Set rsTest = objCmd.Execute