Output parameters

Discussion in 'ASP General' started by Mike P, Aug 13, 2008.

  1. Mike P

    Mike P Guest

    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")




    *** Sent via Developersdex http://www.developersdex.com ***
     
    Mike P, Aug 13, 2008
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Aug 13, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim Owen

    Output parameters undefined? Huh?

    Jim Owen, Oct 22, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    425
    Rajesh.V
    Oct 22, 2003
  2. Jonck van der Kogel
    Replies:
    2
    Views:
    997
    Jonck van der Kogel
    May 27, 2004
  3. Jason
    Replies:
    2
    Views:
    525
    Jonathan Mcdougall
    May 13, 2006
  4. kito
    Replies:
    2
    Views:
    425
  5. Florian Loitsch
    Replies:
    11
    Views:
    254
    Michael Winter
    Mar 15, 2005
Loading...

Share This Page