inetserver asp db: How can I get my parameters collection after a SP call? -- inetserver asp db

How can I get my parameters collection after a SP call?

Zenobia
6/27/2004 1:17:32 PM

Apart from the obvious (the style of the function is all wrong,
etc.) can someone tell me what's wrong here. I can't get the
values of the return and output parameter from my stored
procedrue. After:

Set localrs = cmd.Execute

I expect the values of all 3 parameters to be available but I
can only get the INPUT parameter. For instance, the value of:

outP

remains unchanged at 999

I've checked the SP (shown below) and it's OK.

I know this has been done to death but...

<%@ Language=VBScript %>
<%Option Explicit
Response.Buffer = True
Dim rs
Dim params(2)

Function doAdoSpRs(spName, cmdArgs)
Dim conn, cmd, spReturn, iCount, arg, localrs
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamReturnValue = &H0004
Const adInteger = 3
Const adStoredProcedure = 4
Dim inP, outP

Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout = 30
conn.Open "Driver={SQL
Server};server=(local);Uid=sa;Pwd=secret;Database=Pubs", "sa",
"secret"

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandType = adStoredProcedure
cmd.CommandText = spName

inP = cmdArgs(1)
outP = 999 ' dummy default value

cmd.Parameters.Append
cmd.CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue, 4)
cmd.Parameters.Append cmd.CreateParameter("@pub_id",
adInteger, adParamInput, 4, inP)
cmd.Parameters.Append cmd.CreateParameter("@no_recs",
adInteger, adParamOutput, outP )

Set localrs = cmd.Execute

If (localrs.BOF = True) And (localrs.EOF = True) Then
Set doAdoSpRs = Nothing
conn.Close
Set conn = Nothing
Else
' Display the parameters
Response.Write "<html>" & vbCrLf
Response.Write "<table>" & vbCrLf
Response.Write
"<tr><td>iCount</td><td>arg</td><td>cmd.Parameters(iCount)</td><td>cmd.Parameters(iCount).value</td></tr>"
& vbCrLf
Response.Write "<tr><td>" & 0 & "</td><td>" & 0
& "</td><td>" & cmd.Parameters("RETURN_VALUE").Value &
"</td><td>" & cmd.Parameters(0).value & "</td></tr>" & vbCrLf
Response.Write "<tr><td>" & 1 & "</td><td>" & 1
& "</td><td>" & cmd.Parameters(1) & "</td><td>" &
cmd.Parameters(1).value & "</td></tr>" & vbCrLf
Response.Write "<tr><td>" & 2 & "</td><td>" & 2
& "</td><td>" & cmd.Parameters("@no_recs") & "</td><td>" &
cmd.Parameters(2).value & "</td></tr>" & vbCrLf
Response.Write "</table>" & vbCrLf
Response.Write "</html>" & vbCrLf
Set cmd = Nothing
Set doAdoSpRs = localrs
End If
End Function

params(0) = Null
params(1) = 877
params(2) = Null

Set rs = doAdoSpRs("selTitles", params)

%>

USE Pubs

CREATE PROCEDURE selTitles
@pub_id INT,
@no_recs INT OUTPUT
AS
SELECT * FROM titles WHERE pub_id = @pub_id
SET @no_recs = @@ROWCOUNT
RETURN

DECLARE @no INT, @ret_val INT
EXEC @ret_val = selTitles @pub_id = 877, @no_recs = @no OUTPUT
SELECT @no, @ret_val

Re: How can I get my parameters collection after a SP call?

Zenobia
6/27/2004 2:06:18 PM

I have the answer now.

Microsoft Knowledge Base Article - 256234

PRB: SQL Server Returns Output Parameters Only After Resultsets

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q256/2/34.asp&NoWebContent=1

The parameters are not all available until after all the records
have been returned and this only happened when one uses a client
side cursor.

so one needs to add the following 2 lines of code below:

Const adUseClient = 3
conn.CursorLocation = adUseClient

after that all is swanky.

On Sun, 27 Jun 2004 13:17:32 +0100, Zenobia
[quoted text, click to view]

Re: How can I get my parameters collection after a SP call?

William (Bill) Vaughn
6/28/2004 10:50:01 AM

See my article on handling parameters.
http://www.betav.com/msdn_magazine.htm

Or... read the my book. ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

[quoted text, click to view]
"<tr><td>iCount</td><td>arg</td><td>cmd.Parameters(iCount)</td><td>cmd.Param
eters(iCount).value</td></tr>"
[quoted text, click to view]

Re: How can I get my parameters collection after a SP call?

Bob Barrows [MVP]
6/28/2004 1:57:45 PM

[quoted text, click to view]
Depending on which book you are talking about, can second that statement.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the inetserver asp db group.