Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Output parameters

Reply
Thread Tools

Output parameters

 
 
Mike P
Guest
Posts: n/a
 
      08-13-2008
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",adV arChar,adParamInput
,100, request("CampaignName"))
cmdNewCampaign.Parameters.Append
cmdNewCampaign.CreateParameter("@CampaignID",adInt eger,adParamOutput)
Set rsNewCampaign = cmdNewCampaign.Execute

intNumber = cmdNewCampaign.Parameters("@CampaignID")




*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-13-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",adV arChar,adParamInput
> ,100, request("CampaignName"))
> cmdNewCampaign.Parameters.Append
> cmdNewCampaign.CreateParameter("@CampaignID",adInt eger,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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
skipping template parameters inside template parameters kito C++ 2 09-26-2010 12:04 AM
Class Member Data and Member Function Parameters - Should Parameters Be Data Members? Jason C++ 2 05-13-2006 07:11 AM
does a "parameters"-parameter overwrite the "parameters"-object? Florian Loitsch Javascript 11 03-15-2005 03:33 PM
Servlet parameters different from the command line parameters? Jonck van der Kogel Java 2 05-26-2004 11:34 PM
Output parameters undefined? Huh? Jim Owen ASP .Net 1 10-22-2003 03:35 PM



Advertisments