Cannot get return value from sql procedure

[Reena NET]

I have tried ADO, now I've moved to SQLClient and I still cannot retrieve the return value from my procedure.

Can someone please help me??! Thanks.

Here is my VB code:
cmd.CommandText = "usp_executeMer ge"
cmd.CommandType = CommandType.Sto redProcedure

Dim retparm As New SqlClient.SqlPa rameter
retparm.Paramet erName = "@retval"
retparm.SqlDbTy pe = SqlDbType.Int
retparm.Directi on = ParameterDirect ion.ReturnValue

cmd.Parameters. Add("@dbSrc", SqlDbType.VarCh ar, 50).Direction = ParameterDirect ion.Input
cmd.Parameters( "@dbSrc").V alue = sSrc
cmd.Parameters. Add("@dbDest", SqlDbType.VarCh ar, 50).Direction = ParameterDirect ion.Input
cmd.Parameters( "@dbDest").Valu e = sDest
cmd.Parameters. Add("@prefix", SqlDbType.VarCh ar, 50).Direction = ParameterDirect ion.Input
cmd.Parameters( "@prefix").Valu e = sCaseNumber + "_"
cmd.Parameters. Add("@facIDtoCo py", SqlDbType.Int, 0).Direction = ParameterDirect ion.Input
cmd.Parameters( "@facIDtoCopy") .Value = iFacIDtoCopy
cmd.Parameters. Add("@regIDtoCo py", SqlDbType.Int, 0).Direction = ParameterDirect ion.Input
cmd.Parameters( "@regIDtoCopy") .Value = iRegIDtoCopy
cmd.Parameters. Add("@facIDtoCo pyTo", SqlDbType.Int, 0).Direction = ParameterDirect ion.Input
cmd.Parameters( "@facIDtoCopyTo ").Value = iFacIDtoCopyTo
cmd.Parameters. Add("@userName" , SqlDbType.VarCh ar, 50).Direction = ParameterDirect ion.Input
cmd.Parameters( "@userName").Va lue = sUsername
cmd.Parameters. Add("@orgID", SqlDbType.Int, 0).Direction = ParameterDirect ion.Input
cmd.Parameters( "@orgID").V alue = iOrgID
cmd.Parameters. Add("@bTest", SqlDbType.Bit, 1).Direction = ParameterDirect ion.Input
cmd.Parameters( "@bTest").V alue = bTest
cmd.Parameters. Add("@returnVal ue", SqlDbType.Int, 0).Direction = ParameterDirect ion.Output

reader = cmd.ExecuteRead er()

MsgBox("retval = " + CStr(CInt(retpa rm.Value)))
MsgBox("returnV alue = " + CStr(CInt(cmd.P arameters("@ret urnValue").Valu e)))
reader.Close()

Both msgboxes show 0 as the value.

My sql stored procedure:
CREATE PROCEDURE usp_executeMerg e @dbSrc VARCHAR(50)
,@dbDest VARCHAR(50)
,@prefix VARCHAR(50)
,@facIDtoCopy INTEGER
,@regIDtoCopy INTEGER = NULL
,@facIDtoCopyTo INTEGER = NULL
,@userName VARCHAR(50)
,@orgID INTEGER
,@bTest BIT
,@returnValue INTEGER OUTPUT
AS
BEGIN

SET NOCOUNT ON

DECLARE @systemErrorMes sage VARCHAR(1000)

IF @regIDtoCopy = 0
SET @regIDtoCopy = NULL

IF @facIDtoCopyTo = 0
SET @facIDtoCopyTo = NULL

BEGIN TRY
EXEC @returnValue = usp_mergeMain @OriginalDB = @dbSrc,
@DestinationDB = @dbDest,
@prefix = @prefix,
@fac_idToCopy = @facIDtoCopy,
@reg_idToCopy = @regIDtoCopy,
@fac_idToCopyTo = @facIDtoCopyTo, --NULL if script is creating the facility
@userName = @userName --for created_by and revision_by
END TRY
BEGIN CATCH
SET @systemErrorMes sage = 'ERROR ' + LTRIM(STR(ERROR _NUMBER())) + ': ' + ERROR_MESSAGE()
INSERT INTO mergeResults (message) VALUES (@systemErrorMe ssage)
RAISERROR(@syst emErrorMessage, 10,1)
SET @returnValue = 99
INSERT INTO mergeResults (message) VALUES (@returnValue)
RETURN @returnValue
END CATCH

IF @returnValue <> 0
INSERT INTO mergeResults (message) VALUES (@returnValue)
RETURN @returnValue

UPDATE facility
SET org_id = @orgID
WHERE org_id <> @orgID

SET @returnValue = 0
INSERT INTO mergeResults (message) VALUES (@returnValue)
RETURN @returnValue

END

My mergeResults table shows the return value of 99 but I cannot get any value back to my VB program.

Please help!

[Reena NET]

Any help would be GREATLY appreciated!! Please respond...

Thanks,
Reena