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_executeMerge"
cmd.CommandType = CommandType.StoredProcedure
Dim retparm As New SqlClient.SqlParameter
retparm.ParameterName = "@retval"
retparm.SqlDbType = SqlDbType.Int
retparm.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add("@dbSrc", SqlDbType.VarChar, 50).Direction = ParameterDirection.Input
cmd.Parameters("@dbSrc").Value = sSrc
cmd.Parameters.Add("@dbDest", SqlDbType.VarChar, 50).Direction = ParameterDirection.Input
cmd.Parameters("@dbDest").Value = sDest
cmd.Parameters.Add("@prefix", SqlDbType.VarChar, 50).Direction = ParameterDirection.Input
cmd.Parameters("@prefix").Value = sCaseNumber + "_"
cmd.Parameters.Add("@facIDtoCopy", SqlDbType.Int, 0).Direction = ParameterDirection.Input
cmd.Parameters("@facIDtoCopy").Value = iFacIDtoCopy
cmd.Parameters.Add("@regIDtoCopy", SqlDbType.Int, 0).Direction = ParameterDirection.Input
cmd.Parameters("@regIDtoCopy").Value = iRegIDtoCopy
cmd.Parameters.Add("@facIDtoCopyTo", SqlDbType.Int, 0).Direction = ParameterDirection.Input
cmd.Parameters("@facIDtoCopyTo").Value = iFacIDtoCopyTo
cmd.Parameters.Add("@userName", SqlDbType.VarChar, 50).Direction = ParameterDirection.Input
cmd.Parameters("@userName").Value = sUsername
cmd.Parameters.Add("@orgID", SqlDbType.Int, 0).Direction = ParameterDirection.Input
cmd.Parameters("@orgID").Value = iOrgID
cmd.Parameters.Add("@bTest", SqlDbType.Bit, 1).Direction = ParameterDirection.Input
cmd.Parameters("@bTest").Value = bTest
cmd.Parameters.Add("@returnValue", SqlDbType.Int, 0).Direction = ParameterDirection.Output
reader = cmd.ExecuteReader()
MsgBox("retval = " + CStr(CInt(retparm.Value)))
MsgBox("returnValue = " + CStr(CInt(cmd.Parameters("@returnValue").Value)))
reader.Close()
Both msgboxes show 0 as the value.
My sql stored procedure:
CREATE PROCEDURE usp_executeMerge @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 @systemErrorMessage 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 @systemErrorMessage = 'ERROR ' + LTRIM(STR(ERROR_NUMBER())) + ': ' + ERROR_MESSAGE()
INSERT INTO mergeResults (message) VALUES (@systemErrorMessage)
RAISERROR(@systemErrorMessage,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!