Getting Return Value of Stored Procedure

Discussion in 'ASP General' started by Vipul Pathak, Jul 30, 2003.

  1. Vipul Pathak

    Vipul Pathak Guest

    Hello Friends !

    I have the Following Code, that Executes a Stored Procedure and Attempt to
    read a Returned Integer Value from the StoredProc.
    But It gives Error ...

    ADODB.Command (0x800A0BB9)
    Arguments are of the wrong type, are out of acceptable range, or are in
    conflict with one another.
    /C4U/DBOutputParameterTest.asp, line 25

    Can some one Points Out the Problem ....

    REM=====================================================================
    <BODY>
    <%
    Const adCmdStoredProc = 4

    Dim objConnection, objCommand, objParameter, sSQL

    Set objConnection = Server.CreateObject("AdoDB.Connection")
    Set objCommand = Server.CreateObject("AdoDB.Command")
    Set objParameter = Server.CreateObject("AdoDB.Parameter")

    'Dim objParameter 'As AdoDB.Parameter

    sSQL = "Execute RegisterMessage '13', 'Accounting', 'Accounts', '24304',
    'Hello By Test Message', 'P', '2492331', 'VipulP: The Tester'"

    objConnection.Open Session("CONNECTION_STRING")
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandText = sSQL
    objCommand.CommandType = adCmdStoredProc

    Set objParameter = objCommand.CreateParameter("Return", adInteger,
    adParamReturnValue,,0)
    objCommand.Parameters.Append objParameter
    Response.Write "SQL: " & sSQL & "<BR>"
    'Response.End()

    objCommand.Execute()

    IF objConnection.Errors.Count > 0 Then
    Response.Write "ERROR:<P>"
    Response.Write "Error # " & objConnection.Errors(0).Number & "<BR>"
    Response.Write "Error Description: " &
    objConnection.Errors(0).Description & "<BR>"
    Response.Write "Error Source: " & objConnection.Errors(0).Source & "<BR>"
    Else
    Response.Write "Generated Message Id: " &
    objCommand.Parameters.Item("MsgId").Value & "<P>"
    End IF

    objCommand.Parameters.Refresh()
    For Each objParameter In objCommand.Parameters
    Response.Write objParameter.Name & ": " & objParameter.Value & "<BR>"
    Next
    %>
    </BODY>

    REM=====================================================================

    Thanks !


    ------------------------------
    V I P U L P A T H A K
    eBot Technosoft Limited,
    Indore, (MP), India.
    http://www.ebotsoft.com
     
    Vipul Pathak, Jul 30, 2003
    #1
    1. Advertising

  2. Vipul Pathak

    Ken Schaefer Guest

    www.adopenstatic.com/faq/800a0bb9step2.asp?

    Do you have Option Explicit at the top of your page?

    Cheers
    Ken

    "Vipul Pathak" <> wrote in message
    news:...
    : Hello Friends !
    :
    : I have the Following Code, that Executes a Stored Procedure and Attempt to
    : read a Returned Integer Value from the StoredProc.
    : But It gives Error ...
    :
    : ADODB.Command (0x800A0BB9)
    : Arguments are of the wrong type, are out of acceptable range, or are in
    : conflict with one another.
    : /C4U/DBOutputParameterTest.asp, line 25
    :
    : Can some one Points Out the Problem ....
    :
    : REM=====================================================================
    : <BODY>
    : <%
    : Const adCmdStoredProc = 4
    :
    : Dim objConnection, objCommand, objParameter, sSQL
    :
    : Set objConnection = Server.CreateObject("AdoDB.Connection")
    : Set objCommand = Server.CreateObject("AdoDB.Command")
    : Set objParameter = Server.CreateObject("AdoDB.Parameter")
    :
    : 'Dim objParameter 'As AdoDB.Parameter
    :
    : sSQL = "Execute RegisterMessage '13', 'Accounting', 'Accounts', '24304',
    : 'Hello By Test Message', 'P', '2492331', 'VipulP: The Tester'"
    :
    : objConnection.Open Session("CONNECTION_STRING")
    : Set objCommand.ActiveConnection = objConnection
    : objCommand.CommandText = sSQL
    : objCommand.CommandType = adCmdStoredProc
    :
    : Set objParameter = objCommand.CreateParameter("Return", adInteger,
    : adParamReturnValue,,0)
    : objCommand.Parameters.Append objParameter
    : Response.Write "SQL: " & sSQL & "<BR>"
    : 'Response.End()
    :
    : objCommand.Execute()
    :
    : IF objConnection.Errors.Count > 0 Then
    : Response.Write "ERROR:<P>"
    : Response.Write "Error # " & objConnection.Errors(0).Number & "<BR>"
    : Response.Write "Error Description: " &
    : objConnection.Errors(0).Description & "<BR>"
    : Response.Write "Error Source: " & objConnection.Errors(0).Source &
    "<BR>"
    : Else
    : Response.Write "Generated Message Id: " &
    : objCommand.Parameters.Item("MsgId").Value & "<P>"
    : End IF
    :
    : objCommand.Parameters.Refresh()
    : For Each objParameter In objCommand.Parameters
    : Response.Write objParameter.Name & ": " & objParameter.Value & "<BR>"
    : Next
    : %>
    : </BODY>
    :
    : REM=====================================================================
    :
    : Thanks !
    :
    :
    : ------------------------------
    : V I P U L P A T H A K
    : eBot Technosoft Limited,
    : Indore, (MP), India.
    : http://www.ebotsoft.com
    :
    :
     
    Ken Schaefer, Jul 30, 2003
    #2
    1. Advertising

  3. Vipul Pathak

    Ray at Guest

    I personally just use:

    Set rsWhatever = objADO.Execute "EXEC sp_SomeStoredProc arg1, arg2"
    sReturnValueFromStoredProc = rs.Fields.Item(0).Value
    rsWhatever.Close
    Set rsWhatever = Nothing

    Ray at work

    "Vipul Pathak" <> wrote in message
    news:...
    > Hello Friends !
    >
    > I have the Following Code, that Executes a Stored Procedure and Attempt to
    > read a Returned Integer Value from the StoredProc.
    > But It gives Error ...
    >
    > ADODB.Command (0x800A0BB9)
    > Arguments are of the wrong type, are out of acceptable range, or are in
    > conflict with one another.
    > /C4U/DBOutputParameterTest.asp, line 25
    >
    > Can some one Points Out the Problem ....
    >
    > REM=====================================================================
     
    Ray at, Jul 30, 2003
    #3
  4. Vipul Pathak

    Bob Barrows Guest

    Vipul Pathak wrote:
    > I have the Following Code, that Executes a Stored Procedure and
    > Attempt to read a Returned Integer Value from the StoredProc.
    >

    Good! It is far better to return a single value as a parameter rather than
    using a bulky recordset.

    REM=====================================================================
    > <BODY>
    > <%
    > Const adCmdStoredProc = 4
    >
    > Dim objConnection, objCommand, objParameter, sSQL


    :) Save yourself some typing - use short object names:
    dim cn,cmd,param

    These variable names have become almost standard among developers.


    >
    > Set objConnection = Server.CreateObject("AdoDB.Connection")
    > Set objCommand = Server.CreateObject("AdoDB.Command")
    > Set objParameter = Server.CreateObject("AdoDB.Parameter")
    >
    > 'Dim objParameter 'As AdoDB.Parameter
    >
    > sSQL = "Execute RegisterMessage '13', 'Accounting', 'Accounts',
    > '24304', 'Hello By Test Message', 'P', '2492331', 'VipulP: The
    > Tester'"
    >


    :) No, no, no, no, no! You are using adCmdStoredProc, not adCmdText! That
    means you need to supply the NAME of the procedure for the CommandText
    property! Just the name!

    objCommand.CommandText = "RegisterMessage"

    > objConnection.Open Session("CONNECTION_STRING")
    > Set objCommand.ActiveConnection = objConnection
    > objCommand.CommandText = sSQL
    > objCommand.CommandType = adCmdStoredProc
    >
    > Set objParameter = objCommand.CreateParameter("Return", adInteger,
    > adParamReturnValue,,0)
    > objCommand.Parameters.Append objParameter


    Good start. Now you need to create and append the rest of the parameters
    collection! And set their values. This can be tricky, but you can make the
    task easier by using one of the stored procedure parameter code generators
    out there. I've written one myself. It's available at
    http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear

    > objCommand.Execute()


    No parentheses needed. You should tell the Command that it does not need to
    create a recordset behind the scenes:
    const adExecNoRecords = &H00000080
    objCommand.Execute ,,adExecNoRecords

    >
    > IF objConnection.Errors.Count > 0 Then
    > Response.Write "ERROR:<P>"
    > Response.Write "Error # " & objConnection.Errors(0).Number & "<BR>"
    > Response.Write "Error Description: " &
    > objConnection.Errors(0).Description & "<BR>"
    > Response.Write "Error Source: " & objConnection.Errors(0).Source &
    > "<BR>" Else
    > Response.Write "Generated Message Id: " &
    > objCommand.Parameters.Item("MsgId").Value & "<P>"
    > End IF
    >
    > objCommand.Parameters.Refresh()


    Do not use Parameters.Refresh in production code! It causes an extra
    time-consuming trip to the database. Anyways, you seem to have the wrong
    idea about what this does. Parameters.Refresh is used to create the
    Parameters collection so the individual Parameter objects' values can be set
    BEFORE the command is executed. After the Command is executed, the Return
    parameter (and any output parameters) will contain the value returned by the
    procedure. Using Refresh after execution will clear those values.


    HTH,
    Bob Barrows
     
    Bob Barrows, Jul 30, 2003
    #4
    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. Neo Chou
    Replies:
    1
    Views:
    777
  2. Paul
    Replies:
    1
    Views:
    379
    =?Utf-8?B?UmFtaXJvIENhbGRlcm9u?=
    Jan 19, 2004
  3. Nick
    Replies:
    8
    Views:
    3,271
  4. philip
    Replies:
    2
    Views:
    11,572
    bruce barker \(sqlwork.com\)
    Aug 10, 2006
  5. =?Utf-8?B?QmlzaG95?=
    Replies:
    1
    Views:
    306
    Mark Fitzpatrick
    Dec 24, 2006
Loading...

Share This Page