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

From: Zenobia (6.20.zenobia_at_spamgourmet.com)
Date: 06/27/04 

Date: Sun, 27 Jun 2004 13:17:32 +0100

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

Relevant Pages

  • How can I get my parameters collection after a SP call?
    ... Dim params ... cmd.CreateParameter("RETURN_VALUE", adInteger, ... adParamReturnValue, 4) ... DECLARE @no INT, @ret_val INT ...
    (microsoft.public.inetserver.asp.db)
  • Re: Parameters.Refresh
    ... I suspect that it is somehow initializing the Parameter oject ... Dim cnn As New ADODB.Connection ... cmd.Parameters.Type = adInteger ...
    (microsoft.public.excel.programming)
  • Re: Parameters.Refresh
    ... Dim cnn As ADODB.Connection ... Dim cnn As New ADODB.Connection ... Dim cmd As New ADODB.Command ... cmd.Parameters.Type = adInteger ...
    (microsoft.public.excel.programming)
  • Re: Adding column description to Access table, HOW?
    ... Dim col As ADOX.Column = New ADOX.Column ... objTable.Columns.Append("RefId", adInteger) ... however I get the following exception: ... Looking at the Column using a debugger, I can see that the column doesn't ...
    (microsoft.public.dotnet.framework.adonet)