How to pass return value to ASP from a stored procedure

[zhe song]

I am trying to return value from a stored procedure to ASP.
But I always got the error msg said "ADODB.Command error '800a0bb9' The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another."

Here is my code in ASP to call the stored proc.
<%
Set objConnection = Server.CreateObject(&#34;ADODB.Connection&#34 ConnString = &#34;DSN=;UID=;PWD=;&#34;
objConnection.Open ConnString

Set cmd = Server.CreateObject(&#34;ADODB.Command&#34
Set cmd.ActiveConnection = objConnection
cmd.CommandText = &#34;{call sp_test(?,?,?,?,?,?)}&#34;

cmd.Parameters.Append cmd.CreateParameter(&#34;RetVal&#34;, adInteger, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter(&#34;tranType&#34;, adVarChar, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&#34;debitAcct&#34;, adVarChar, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&#34;creditAcct&#34;, adVarChar, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&#34;amount&#34;, adVarChar, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&#34;reference&#34;, adVarChar, adParamInput)

cmd.Parameters(&#34;tranType&#34 = TransactionType
cmd.parameters(&#34;debitAcct&#34 = DebitAcct
cmd.parameters(&#34;creditAcct&#34 = CreditAcct
cmd.parameters(&#34;amount&#34 = Amount
cmd.parameters(&#34;reference&#34 = Reference
cmd.Execute
Response.write cmd.parameters(&#34;RetVal&#34
%>
and in the stored proc, I declared @RetVal as the output parameter, and assigned it to a variable @REstatus, since I used this variable several times within this stored proc.

create procdeure sp_test
(
@ReVal int output,
@tranType varchar(3),
@debitAcct varchar(8),
@creditAcct varchar(8),
@amount varchar(32),
@reference varchar(80)
)
As
begin
declare @REstatus int,
select @RetVal = @REstatus
------------
begin transaction
update table_name
--------------
if @@error <> 0
rollback transaction
select @REstatus = -1
return -1
else
commit transaction
select @REstatus = 0
return 0

end
go

I guess there must be some problems with my stored procedure, since when I execute it inside SQL server, I used:

exec sp_test @ReVal=@REstatus output,
@tranType=&#39;-&#39;,@debitAcct=&#39;-&#39;,@creditAcct=&#39;-&#39;,@amount=&#39;-&#39;,@reference=&#39;-&#39;

then returned me wrong msg: you must declare variable @REstatus, but I declared it in the procedure.

Could someone help me out of this? Thanks in advance.

zhe

[John]

Hi Zhe,

Try posting this question on the ASP support forum. Here we help solve problems with the ASP-db product which does all that work you&#39;re referring to for you. Check out www.aspdb.com for more info.

Thanks,
John


------------
zhe song at 7/27/99 1:40:17 PM

I am trying to return value from a stored procedure to ASP.
But I always got the error msg said &#34;ADODB.Command error &#39;800a0bb9&#39; The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.&#34;

Here is my code in ASP to call the stored proc.
<%
Set objConnection = Server.CreateObject(&#34;ADODB.Connection&#34 ConnString = &#34;DSN=;UID=;PWD=;&#34;
objConnection.Open ConnString

Set cmd = Server.CreateObject(&#34;ADODB.Command&#34
Set cmd.ActiveConnection = objConnection
cmd.CommandText = &#34;{call sp_test(?,?,?,?,?,?)}&#34;

cmd.Parameters.Append cmd.CreateParameter(&#34;RetVal&#34;, adInteger, adParamOutput)
cmd.Parameters.Append cmd.CreateParameter(&#34;tranType&#34;, adVarChar, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&#34;debitAcct&#34;, adVarChar, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&#34;creditAcct&#34;, adVarChar, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&#34;amount&#34;, adVarChar, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&#34;reference&#34;, adVarChar, adParamInput)

cmd.Parameters(&#34;tranType&#34 = TransactionType
cmd.parameters(&#34;debitAcct&#34 = DebitAcct
cmd.parameters(&#34;creditAcct&#34 = CreditAcct
cmd.parameters(&#34;amount&#34 = Amount
cmd.parameters(&#34;reference&#34 = Reference
cmd.Execute
Response.write cmd.parameters(&#34;RetVal&#34
%>
and in the stored proc, I declared @RetVal as the output parameter, and assigned it to a variable @REstatus, since I used this variable several times within this stored proc.

create procdeure sp_test
(
@ReVal int output,
@tranType varchar(3),
@debitAcct varchar(8),
@creditAcct varchar(8),
@amount varchar(32),
@reference varchar(80)
)
As
begin
declare @REstatus int,
select @RetVal = @REstatus
------------
begin transaction
update table_name
--------------
if @@error <> 0
rollback transaction
select @REstatus = -1
return -1
else
commit transaction
select @REstatus = 0
return 0

end
go

I guess there must be some problems with my stored procedure, since when I execute it inside SQL server, I used:

exec sp_test @ReVal=@REstatus output,
@tranType=&#39;-&#39;,@debitAcct=&#39;-&#39;,@creditAcct=&#39;-&#39;,@amount=&#39;-&#39;,@reference=&#39;-&#39;

then returned me wrong msg: you must declare variable @REstatus, but I declared it in the procedure.

Could someone help me out of this? Thanks in advance.

zhe