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

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 10:50:01 -0700

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.
__________________________________
"Zenobia" <6.20.zenobia@spamgourmet.com> wrote in message
news:17etd05onctaea7ploasai7s1phucs9i83@4ax.com...
> 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.Param
eters(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
>