Re: 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 14:06:18 +0100

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
<6.20.zenobia@spamgourmet.com> wrote:

>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