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".