first time calling stored procedure with params

[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

 

[Dave Anderson]

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.