collect return value of stored procedure from application

I got a procedure in MS Sql 7.0 which returns 1 or 2 depending on the execution of the statements in the proc. It takes a parameter also. How can my application recieve this return value. This is how I called the proc.

dim dup as integer
sqlstr = "{dup=CALL dupcode (" & Trim(txt_scancode.Text) & ")} "
cn.Execute sqlstr

This gives error. If I remove the 'dup=', then it works fine, but I dont get the return value.

Please help

Thanks
James

[Mikesc]

You can do this with ADO. Create a Command object and use the Command object's CreateParameter method, set the Direction property to adParamReturnValue.


This should point you in the right direction. For more information you might wanna check the help files

[czimmerman]

I'm assuming you're using ADO.

The steps you need to follow are:

dim oCmd as new ADODB.Command
dim oInParam as new ADODB.Parameter
dim oReturnValue as ADODB.Parameter
dim iReturnValue as integer
dim oRs as ADODB.Recordset


oCmd.CommandText = "MySP" 'Put the name of your stored procedure here
oCmd.CommandType = adCmdStoredProc

'input parameter
oInParam.Type = adInteger set to a different type if not integer
oInParam.Direction = adParamInput
oInParamValue = 3 'whatever your value is
oCmd.Parameters.Append oInParam

'Return Value
oReturnValue.Type = adInteger
oReturnValue.Direction = adParamReturnValue

set oRs = oCmd.Execute
iReturnValue = oReturnValue.value





Charlie Zimmerman
http://www.freevbcode.com