Setting ByRef variable to stored proc output in vb.net

155 Views Asked by At

Below is a function I have in VB that will run a stored proc. The stored proc has 2 out parameters. One called @Success and one called @Message.

I am trying to set the success and message parameters to the values of the corresponding output params from the stored proc. I don't know much about VB.NET. How can I achieve this based off of the function I have currently written?

Public Function InvalidateCertificate(ByVal context As DbContextBase,
                                      ByVal certificateId As System.String,
                                      ByRef success As System.Boolean,
                                      ByRef message As System.String) As Int32

    Dim successParameter As New SqlParameter("@Success", success) With {.Direction = ParameterDirection.InputOutput, .Value = success}
    Dim messageParameter As New SqlParameter("@Message", message) With {.Direction = ParameterDirection.InputOutput, .Value = message}

    Dim parameters() As SqlParameter = {New SqlParameter("@CertificationValue", certificateId), successParameter, messageParameter}

    Dim results As Int32 = context.ExecuteProcedure("Orders.spInvalidateCertificate", parameters)

    success = DirectCast(successParameter.Value, System.Boolean)
    message = DirectCast(messageParameter.Value, System.String)

    Return 0
End Function

Stored proc:

ALTER PROCEDURE [Orders].[spInvalidateCertificate] 
-- Add the parameters for the stored procedure here
 @CertificationValue VARCHAR(20), 
 @Success BIT OUTPUT,
 @Message VARCHAR(50) OUTPUT
AS
BEGIN 
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   -- Log the call to this procedure
   EXEC [Logs].[spLogStoredProcedureCall] @@PROCID;

   --Check to see if CertificationValue exists or is invalid
   IF(select CertificationValue from 
   [Certificate].CertificateOrderDetailImageXref where CertificationValue = 
   @CertificationValue) is null
   BEGIN
       SET @Success = 0
       SET @Message = 'Certification does not exist or is already 
   invalidated.'
   END
   ELSE
   BEGIN
        -- Insert statements for procedure here
       UPDATE Certificate.CertificateOrderDetailImageXref
       SET CertificationValue = CONCAT(@CertificationValue,'-VOID')
       WHERE CertificationValue = @CertificationValue

       SET @Success = 1
       SET  @Message = 'Success, certification has been invalidated.' 
   END;
END
GO

To note, what's actually happening is that only the first letter of the message is being returned. so if its a success, and the sql output should be "success!" I just get "S". If the certification does not exist I just get "C".

0

There are 0 best solutions below