Problem with return value from Stored Procedure

[MarcusH]

Hi,

I'm having an unusal problem with getting a return value from my stored procedure. To test it, I'm deliberately putting a null into pinnum which does not allow nulls.
When I return the value in SQL Server Query Analyzer, it gives me the correct value i.e not 0, but printing the value in my ASP page returns nothing at all.

Here is the ASP code which calls the sproc.

set cmd = Server.CreateObject("ADODB.Command&quot
cmd.ActiveConnection=conn 'a connenction was set up earlier
cmd.CommandText="usp_CreateAccount"
cmd.CommandType= adCmdStoredProc

pinNum=null 'deliberate error. Sproc will not allow the insert of a null

cmd.Parameters.Append cmd.CreateParameter("RETURN_VALUE",adInteger,adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("@MemberID",adInteger,adParamInput,,Session("ID&quot)
cmd.Parameters.Append cmd.CreateParameter("@MailNum",adVarChar,adParamInput,6,zeroString&mailboxNum)
cmd.Parameters.Append cmd.CreateParameter("@PinNum",adVarChar,adParamInput,6,pinNum)
cmd.Execute , ,adExecuteNoRecords

return_value=cmd.Parameters("RETURN_VALUE&quot
set cmd = nothing

Response.write "return_value="&return_value
conn.close
set conn=nothing
Response.end

This produces: "return_value="

Here is the sproc

ALTER proc usp_CreateAccount

@memberid int,
@mailNum char(6),
@pinNum char(6)

as
declare @errorbin int
begin
set nocount on
begin tran
insert into MembersMail(MemberID,MailNum,PinNum)
values(@memberid,@mailNum,@pinNum)

set @errorbin=@@error
if @errorbin=0 commit tran
else rollback tran

set nocount off
return @errorbin
end

Although it doesn't return a value to my ASP page, it does rollback.

Any help would be much appreciated.

Marcus

 

[cmmrfrds]

Try this to check all your parameters. The return parameter may not being recognized.

Response.write "parm 1="cmd(0)
Response.write "parm 2="cmd(1)
Response.write "parm 3="cmd(2)
Response.write "parm 4="cmd(3)
Response.End

Why do you set nocount off before the return? That could cause a recordset to be returned to ADO which would require a close of the recordset before reading the return parameter. ADO will implicitly create a recordset if it requires one.

 

[MarcusH]

Thanks for your response. I'll try your suggestion.

 

[MarcusH]

Found a solution to my problem.

It can be seen here:support.microsoft.com/support/kb/articles/Q253/2/40.asp

Marcus