first time calling stored procedure with params

Discussion in 'ASP General' started by Mo Bandy, Aug 9, 2007.

  1. Mo Bandy

    Mo Bandy Guest

    Please help. Never used Stored Procedures in my life!

    I need to call a stored procedure from a Classic ASP vbscript page. The SP
    takes two params (itemkey and qty) both integers and returns the qty
    available for that particular item.

    What I got ain't working. Getting a syntax error.

    dim cn, cmd, nextorder, ItemKey, allocQty
    Const adExecuteNoRecords = &H00000080
    Const adCmdStoredProc = &H0004
    Const adParamReturnValue = &H0004
    Const adParamInput = &H0004
    Const adInteger = 3

    ItemKey = 20599
    allocqty = 1

    set cn=createobject("adodb.connection")
    cn.open "Provider=SQLOLEDB; Data Source = webstore; Initial Catalog = EComm;
    User Id = xxxx; Password = xxxxxx"
    set cmd=createobject("adodb.command")
    with cmd
    .commandtext="ap_allocateitem " & itemkey & ", " & allocqty
    .CommandType=adCmdStoredProc
    set .activeconnection = cn
    .Parameters.Append .CreateParameter("RETURN_VALUE",
    adInteger,adParamReturnValue)
    .Parameters.Append .CreateParameter("@ItemKey", adInteger,adParamInput)
    .Parameters.Append .CreateParameter("@allocqty", adInteger,adParamInput)
    .execute
    intQty = .Parameters(0).value
    end with
    Mo Bandy, Aug 9, 2007
    #1
    1. Advertising

  2. "Mo Bandy" wrote:
    > set cmd=createobject("adodb.command")


    You probably don't need a Command Object. More on that in a bit. But if you
    do...

    > with cmd
    > .commandtext="ap_allocateitem " & itemkey & ", " & allocqty


    Command.CommandText should contain the SP name only.

    > .CommandType=adCmdStoredProc
    > set .activeconnection = cn
    > .Parameters.Append .CreateParameter("RETURN_VALUE",
    > adInteger,adParamReturnValue)
    > .Parameters.Append .CreateParameter("@ItemKey", adInteger,adParamInput)
    > .Parameters.Append .CreateParameter("@allocqty",
    > adInteger,adParamInput)


    Populate the parameters as you create them. Note the fourth argument:

    ...CreateParameter(("@ItemKey", adInteger, adParamInput, itemkey)

    > .execute
    > intQty = .Parameters(0).value
    > end with



    Now, how could you do this more easily? SP-as-method-of-CN. This requires
    you to change your SP so your RETURN statement becomes a SELECT, but that is
    trivial in comparison to the ASP-side benefits:

    Set CN = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    CN.Open {your connection string here}
    CN.ap_allocateitem itemkey, allocqty, RS

    Examine the contents of RS.Fields(0).Value for your "return" value. That's
    it.




    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms.
    Dave Anderson, Aug 10, 2007
    #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. Kerri
    Replies:
    0
    Views:
    340
    Kerri
    Aug 12, 2003
  2. JN
    Replies:
    0
    Views:
    3,169
  3. Sudhish K V

    Calling Oracle stored procedure using c#

    Sudhish K V, Nov 11, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    6,932
    David Browne
    Nov 11, 2003
  4. Mike P
    Replies:
    0
    Views:
    3,280
    Mike P
    Jun 19, 2006
  5. Barry
    Replies:
    9
    Views:
    429
    Ara.T.Howard
    Sep 15, 2005
Loading...

Share This Page