first time calling stored procedure with params

M

Mo Bandy

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
 
D

Dave Anderson

Mo Bandy said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,754
Messages
2,569,516
Members
44,984
Latest member
PeakCBDGummiesReview

Latest Threads

Top