Re: Output parameters by Bob
Bob
Wed Aug 13 06:10:36 CDT 2008
Mike P wrote:
> I am trying to return an output parameter to my code on executing a
> stored procedure. In Query Analyzer, it works with no problem, but
> when I run my ASP code below, the output parameter never seems to
> return anything. Can anybody help?
>
> Dim cmdNewCampaign, rsNewCampaign, intNumber
> Const adCmdStoredProc = &H0004
> Const adParamInput = &H0001
> Const adParamOutput = &H0002
> Const adVarChar = 200
> Const adInteger = 3
>
> Set cmdNewCampaign = Server.CreateObject ("ADODB.Command")
> cmdNewCampaign.ActiveConnection = strConnection
> cmdNewCampaign.CommandText = "AddNewCampaign"
> cmdNewCampaign.CommandType = adCmdStoredProc
> cmdNewCampaign.Parameters.Append
> cmdNewCampaign.CreateParameter("@CampaignName",adVarChar,adParamInput
> ,100, request("CampaignName"))
> cmdNewCampaign.Parameters.Append
> cmdNewCampaign.CreateParameter("@CampaignID",adInteger,adParamOutput)
> Set rsNewCampaign = cmdNewCampaign.Execute
>
> intNumber = cmdNewCampaign.Parameters("@CampaignID")
>
>
1. SQL Server does not send return or output parameter values until all
resultsets generated by the stored procedure are consumed by the caller. It
appears, by your use of "Set rsNewCampaign = cmdNewCampaign.Execute" that
this procedure is intended to return a resultset. This means that you will
not see your output parameter value until you either close the recordset or
retrieve all the records being returned by the procedure (typically done by
navigating to the last record). I will typically use GetRows to pull all
the records into an array, allowing me to close the recordset and get my
output parameter values, but if you want to avoid using an array, and you
need to use the recordset data after retrieving the output value, you will
need to use a client-side cursor (set the recordset's cursorlocation
property to adUseClient).
2. Those informational "x rows were affected" messages that you see in Query
Analyzer are sent to the caller as resultsets. Those resultsets also need to
be consumed before output and return values are sent. You should make a
practice of suppressing those informational messages by including the line
"SET NOCOUNT ON" in every stored procedure that you write ... unless your
application needs those messages.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"