ADO ASP classic, Output parameters anomally.

First a couple of apologies,
Sorry if this message looks over-complicated but I'm uncertain of
precisely where things are going wrong...
and secondly, if I've double-posted I didn't mean to but I'm using a
web-based news client and I'm not sure if it worked when I first clicked
submit :(

I have a Stored Proc that works in Query Analyzer, but for some reason
is not properly receiving parameters when called from my asp app. I
don't know which side it's going wrong at since my calling methods
*appear* to work elsewhere.
I'm passing parameters using an array technique (robbed from someplace
so maybe some will recognise it):

Sub collectParams(ByRef cmd, ByVal argparams, ByRef OutPutParms)
Dim params, v, i
If Not IsArray(argparams) Then
Exit Sub
End If

OutPutParms = False
params = argparams
For i = LBound(params) To UBound(params)
If params(i)(1) = adVarchar Then
If params(i)(4) = "" Then
v=null
Else
v=params(i)(4)
End If
Else
v = params(i)(4)
End If
If params(i)(2) = adParamOutput Then
OutPutParms = True
End If
cmd.Parameters.Append cmd.CreateParameter(params(i)(0), params(i)(1),
params(i)(2), params(i)(3), v)
Next
End Sub


Function RunSP(ByVal strSP, params, byRef OutArray)
Dim cmd, OutPutParms
Set cmd = Server.CreateObject("adodb.Command")
cmd.ActiveConnection = GetConnectionString()
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
collectParams cmd, params, OutPutParms
cmd.Execute , , adExecuteNoRecords
If OutPutParms Then
OutArray = collectOutputParms(cmd, params)
End If
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
RunSP = 0
End Function

I'm calling this function in the following way:
str_SQL = "prcGetCustomerAndActiveBasket"
a_Params = array(_
array("@CustomerIndex", adVarchar, adParamOutput, 10,
lCustomerIndex),_
array("@BasketDetailsIndex", adInteger, adParamOutput, ,
lBasketDetailsIndex),_
array("@bDefaultAccount", adBoolean, adParamOutput, ,
lDefaultAccount),_
array("@strError", adVarChar, adParamOutput, 1000, strError))
Call RunSP(str_Sql, a_Params, OutArray)

The definition of the SQL query is as follows:
PROCEDURE [dbo].[prcGetCustomerAndActiveBasket]@CustomerIndex
tpCustomerIndex OUTPUT, @BasketDetailsIndex tpBasketDetailsIndex = NULL
OUTPUT, @bDefaultAccount bit = NULL OUTPUT,
@strError tpErrorMsg = NULL OUTPUT

The symptoms are:
When called from Query Analyzer it behaves just as expected.

DECLARE @CustomerIndex tpCustomerIndex, @BasketDetailsIndex
tpbasketDetailsIndex, @bDefaultAccount bit, @strError tpErrorMsg
SET @CustomerIndex = 'AEI282705c'
SET @strError = ''
EXEC [dbo].[prcGetCustomerAndActiveBasket]@CustomerIndex =
@CustomerIndex OUTPUT, @BasketDetailsIndex = @BasketDetailsIndex OUTPUT,
@bDefaultAccount = @bDefaultAccount, @strError = @strError OUTPUT
PRINT ( 'CustomerIndex = ' + @CustomerIndex)
PRINT ( 'BasketDetailsIndex = ' + CAST(@BasketDetailsIndex AS varchar))
PRINT ( 'Error = ' + @strError )

But when called from the asp app, debugging shows the proc to have only
NULLs passed. It runs and returns the output params as if NULLs have
been passed.
I have tested to ensure that the parameters get as far as the
collectParams() sub. Names and values are correct.

Other procs whose parameters are compiled in the same way are behaving
perfectly.

Any ideas?



For interested parties' testing purposes, I am including the
collectOutputParms() function:
Function collectOutputParms(ByRef cmd, ByRef argparams)
Dim i, params, OutArray()
params = argparams
ReDim OutArray(UBound(argparams))
For i = LBound(params) To UBound(params)
OutArray(i) = cmd.Parameters(i).Value
Next
collectOutputParms = OutArray
End Function

*** Sent via Developersdex http://www.developersdex.com ***

Re: ADO ASP classic, Output parameters anomally.

MattSmith wrote:
> First a couple of apologies,
> Sorry if this message looks over-complicated but I'm uncertain of
> precisely where things are going wrong...
> and secondly, if I've double-posted I didn't mean to but I'm using a
> web-based news client and I'm not sure if it worked when I first
> clicked submit :(
>
> I have a Stored Proc that works in Query Analyzer, but for some reason
> is not properly receiving parameters when called from my asp app. I
> don't know which side it's going wrong at since my calling methods
> *appear* to work elsewhere.
> I'm passing parameters using an array technique (robbed from someplace
> so maybe some will recognise it):

There's your first mistake. You cannot retrieve output and return parameters
when using this method of passing parameter values. You need to build the
Parameters collection, assigning the relevant parameter-direction values for
each parameter object. I've created a code generator that does this for you
.. You can get it here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp

>
> Sub collectParams(ByRef cmd, ByVal argparams, ByRef OutPutParms)


> PROCEDURE [dbo].[prcGetCustomerAndActiveBasket]@CustomerIndex
> tpCustomerIndex OUTPUT, @BasketDetailsIndex tpBasketDetailsIndex =
> NULL OUTPUT, @bDefaultAccount bit = NULL OUTPUT,
> @strError tpErrorMsg = NULL OUTPUT
>

You stopped too soon. You need to make sure the first line in your proc is
"SET NOCOUNT ON" which will suppress the informational messages (x rows were
affected) that are returned as resultsets to the client. Output and Return
values are not sent to the client until all resultsets generated by the
procedure have been sent to the clietn. When you execute the query via ADO,
it will receive these resultsets, but will do nothing with them resulting in
the output and return values never being sent.

The idea is, build your parameter objects, adding them to the Parameters
collection. Execute your procedure. If the procedure returns one or more res
ultsets, process them, closing your recordset object when done. Then, read
the values of the output parameter objects.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.