Can I get RS and output param value of SP in ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can I get RS and output param value of SP in ASP

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-11-05 : 17:27:44

Hello,
I have a SP that will return records and that SP has an ouput paramater that will return the recordcount.

In my ASP code I am trying to get the records and the output paramter at the same time. But I couldn't get them.

I get the ouput paramater if I do this:


set cmd = server.CreateObject("adodb.command")
set cmd.ActiveConnection=conn
cmd.CommandType = 4		
cmd.CommandText = "s_test"
cmd.Parameters.Append cmd.CreateParameter("intNumID",adInteger,adParamInput,4,20)
cmd.Parameters.Append cmd.CreateParameter("numRecs",adInteger,adParamOutput)

cmd.Execute
Response.Write cmd.Parameters("numRecs").Value & "<br>"

But if I try to do with use Recordset I will not get the output parameter but I can loop through all the records.


Set rs = Server.CreateObject("ADODB.recordset")

set cmd = server.CreateObject("adodb.command")
set cmd.ActiveConnection=conn
cmd.CommandType = 4		
cmd.CommandText = "s_test"
cmd.Parameters.Append cmd.CreateParameter("intNumID",adInteger,adParamInput,4,20)
cmd.Parameters.Append cmd.CreateParameter("numRecs",adInteger,adParamOutput)

Set rs = cmd.Execute
If Not rs.EOF Then
Response.Write cmd.Parameters("numRecs").Value & "<br>"

while Not rs.EOF 
Response.Write rs("Num_ID") & "<br>"

rs.MoveNext
Wend

End If
rs.Close
Set rs = Nothing

Thanks for your help,
maximus

scullee
Posting Yak Master

103 Posts

Posted - 2004-11-05 : 17:45:57

I know we have done it with Java so i assume you can do it with ASP as well. We found you had to read the parameters after you got the recordset so have a play around with the order and see what happens.

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-11-05 : 17:54:49

Hello scullee,

You are correct. I tried accessing the output parameter after closing the recordset and it worked.

This is the modified code:


Set rs = Server.CreateObject("ADODB.recordset")

set cmd = server.CreateObject("adodb.command")
set cmd.ActiveConnection=conn
cmd.CommandType = 4		
cmd.CommandText = "s_test"
cmd.Parameters.Append cmd.CreateParameter("intNumID",adInteger,adParamInput,4,20)
cmd.Parameters.Append cmd.CreateParameter("numRecs",adInteger,adParamOutput)

Set rs = cmd.Execute
If Not rs.EOF Then

while Not rs.EOF 
Response.Write rs("Num_ID") & "<br>"

rs.MoveNext
Wend

End If
rs.Close
Response.Write cmd.Parameters("numRecs").Value & "<br>"

Set rs = Nothing

Thnx
Maximus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources