This is a multi-part message in MIME format.

------=_NextPart_000_000B_01C1CED5.5BD0C8D0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 =3D Server.CreateObject("ADODB.Command")
cmd.ActiveConnection=3Dconn                                         'a 
connenction was set up earlier
cmd.CommandText=3D"usp_CreateAccount"
cmd.CommandType=3D adCmdStoredProc

pinNum=3Dnull                                                            
    '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"))
cmd.Parameters.Append 
cmd.CreateParameter("@MailNum",adVarChar,adParamInput,6,zeroString&mailbo
xNum)
cmd.Parameters.Append 
cmd.CreateParameter("@PinNum",adVarChar,adParamInput,6,pinNum)
cmd.Execute , ,adExecuteNoRecords

return_value=3Dcmd.Parameters("RETURN_VALUE")
set cmd =3D nothing

Response.write "return_value=3D"&return_value
conn.close
set conn=3Dnothing
Response.end

This produces: "return_value=3D"

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=3D@@error
          if @errorbin=3D0 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