[Resolved] Get return value from stored procedure

snufse · August 8th, 2008, 07:22 AM

I am having difficulties getting a result back after calling a sp. Most likely this is due to me not understanding the interaction. Wonder if someone could help?

Here is my VB 2005 code:

Code:

EquentialCommand.Parameters.Add(New Data.SqlClient.SqlParameter("@RetValue", Data.SqlDbType.Char, 50))
EquentialCommand.Parameters("@RetValue").Direction = Data.ParameterDirection.Output
EquentialCommand.CommandType = Data.CommandType.StoredProcedure
EquentialCommand.CommandText = ("sp_Vecellio_JobInquiry1")
EquentialCommand.Connection = EquentialConnection
mySqlDataAdapter.SelectCommand = EquentialCommand
RetValue = EquentialCommand.Parameters("@RetValue").Value.ToString
If RetValue <> Nothing Then
                myError = True
                ASPNET_MsgBox(RetValue)
            Else
                mySqlDataAdapter.Fill(mySqlDataTable)
                EquentialCommand.Parameters.Clear()
                EquentialConnection.Close()
End If

Her is my query snippet:

Code:

CREATE PROCEDURE dbo.sp_Vecellio_JobInquiry1
(
    @JobNumber INT,
    @UserID VARCHAR(10),
    @Text VARCHAR(20) = NULL,
    @DateFrom DATETIME = NULL,
    @DateTo DATETIME = NULL,
        @RetValue CHAR(50) OUTPUT
)

AS

SET NOCOUNT ON

CREATE TABLE    #AuthorityTable
        (
            busunit_from BIGINT,
            busunit_to BIGINT
        )

INSERT    #AuthorityTable
    (
        busunit_from,
        busunit_to
    )

SELECT    msmcuf, msmcut
FROM    OPENQUERY(vgsys400, 'SELECT * FROM vgisec.f0001')
WHERE    msuser = @UserID

IF @@ROWCOUNT = 0
    BEGIN
    SET @RetValue = 'You are not authorized to any selections.'
    RETURN
    END

If I do not find any records from the 'openquery' then I would like to return message to my program

snufse · August 8th, 2008, 11:05 AM

Finally solved my vb code this way:

Code:

EquentialCommand.Parameters.Add(New Data.SqlClient.SqlParameter("@RetValue", Data.SqlDbType.Char, 50)).Value = Nothing
            EquentialCommand.Parameters("@RetValue").Direction = Data.ParameterDirection.Output
            EquentialCommand.CommandType = Data.CommandType.StoredProcedure
            EquentialCommand.CommandText = ("sp_Vecellio_JobInquiry1")
            EquentialCommand.Connection = EquentialConnection
            mySqlDataAdapter.SelectCommand = EquentialCommand
            mySqlDataAdapter.Fill(mySqlDataTable)
            Dim ReturnValue As String = EquentialCommand.Parameters("@RetValue").Value.ToString
            If ReturnValue <> Nothing Then
                myError = True
                ASPNET_MsgBox(ReturnValue)
            Else
                EquentialCommand.Parameters.Clear()
                EquentialConnection.Close()
            End If
        Catch ex As Exception
            ErrorString = ex.ToString
            ASPNET_MsgBox("An error has occurred connection to server and loading table: " + vbCrLf + ErrorString)
        End Try