Accessing a RETURN code from SP

[tbode2006]

Dim Status_Parm As Parameter

cmd.CommandText = "spAddQuantity"
cmd.CommandType = adCmdStoredProc
    
Set Status_Parm = cmd.CreateParameter("", adInteger, adParamReturnValue)
cmd.Parameters.Append Status_Parm

cmd.Execute

Given the above code fragment, how do I get a RETURN code that was set in the stored procedure? Sorry about not searching the forum but it is not working.

 

[dtqbterror]

Put the return value into an output parameter and then read the value from the parameter from your code.

http://www.chiefscoalition.com

 

[dtqbterror]

intParamValue = cmd.Parameters(0).Value

http://www.chiefscoalition.com

 

[tbode2006]

I know how to use the output parameter syntax. What I am looking for is the use of RETURN{value} in my stored procedure.

' Set up a return parameter.
Set param1 = cmd.CreateParameter("", adInteger, adParamReturnValue)
cmd.Parameters.Append param1
            
' Set up an output parameter.
Set param2 = cmd.CreateParameter("", adInteger, adParamOutput)
cmd.Parameters.Append param2
  
' Set up an input parameter.
Set param3 = cmd.CreateParameter("", adInteger, adParamInput)

in a stored procedure

IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Returns 0 if neither SELECT statement had
-- an error, otherwise returns the last error.
RETURN @ErrorSave
GO

so now in my VB code

    Set Status_Parm = cmd.CreateParameter("", adInteger, adParamReturnValue)
    cmd.Parameters.Append Status_Parm

    Status_Parm = cmd.Execute

does not work.
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"
[COLOR=RED YELLOW]Status_Parm = cmd.Execute[/COLOR]

 

[tbode2006]

I found my answer!

  Set Status_Parm = cmd.CreateParameter[COLOR=yellow highlight]("Status"[/color], adInteger, adParamReturnValue)
    cmd.Parameters.Append Status_Parm

    cmd.Execute
    MsgBox cmd.Parameters([COLOR=yellow highlight]"Status").Value[/color]

Whatever the value of RETURN is in my stored procedure is now available to me.