[RESOLVED] Return values from PROC to VB.NET application to display on labels.

[BrailleSchool]

I have created a PROC and it gets all the information, but not in a table (not sure why). But, I need to return the values and then display those values in labels.

This is what I have so far. I am not sure what I am doing and have been on Google all night trying to figure it out.

ALTER PROCEDURE [dbo].[SP_GetTicketCountByStatus]
(
@CreatedBy AS VARCHAR(250)
)
AS

DECLARE @MyCount AS INT
DECLARE @OpenCount AS INT
DECLARE @PendingCount AS INT
DECLARE @WaitingResponseCount AS INT
DECLARE @WorkInProgressCount AS INT

SET NOCOUNT ON

BEGIN
SELECT @MyCount = COUNT(*)
FROM [TicketManager].[dbo].Tickets AS TM
WHERE TM.TicketStatus <> 'Resolved' AND TM.CreatedBy = @CreatedBy
RETURN @MyCount
END

BEGIN
SELECT @OpenCount = COUNT(*)
FROM [TicketManager].[dbo].Tickets AS TM
WHERE TM.TicketStatus = 'Open'
RETURN @OpenCount
END

BEGIN
SELECT @PendingCount = COUNT(*)
FROM [TicketManager].[dbo].Tickets AS TM
WHERE TM.TicketStatus = 'Pending'
RETURN @PendingCount
END

BEGIN
SELECT @WaitingResponseCount = COUNT(*)
FROM [TicketManager].[dbo].Tickets AS TM
WHERE TM.TicketStatus = 'Waiting Response'
RETURN @WaitingResponseCount
END

BEGIN
SELECT @WorkInProgressCount = COUNT(*)
FROM [TicketManager].[dbo].Tickets AS TM
WHERE TM.TicketStatus = 'WorkInProgress'
RETURN @WorkInProgressCount
END

        Using SetDatabaseConnection As New SqlConnection(MSSQLConnection)
            Try
                Dim GetTicketCounts As SqlCommand = New SqlCommand

                SetDatabaseConnection.Open()

                With GetTicketCounts
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "SP_GetTicketCountByStatus"
                    .Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = MDIContainer.PersonsName.Text
                    .Parameters("@CreatedBy").Direction = ParameterDirection.Input

                    .Parameters("@MyCount").Direction = ParameterDirection.Output
                    .Parameters("@OpenCount").Direction = ParameterDirection.Output
                    .Parameters("@PendingCount").Direction = ParameterDirection.Output
                    .Parameters("@WaitingResponseCount").Direction = ParameterDirection.Output
                    .Parameters("@WorkInProgressCount").Direction = ParameterDirection.Output

                    Dim RetVal As Integer = CInt(.ExecuteScalar)
                End With

                Dim Ds As DataSet = New DataSet
                Dim Da As SqlDataAdapter = New SqlDataAdapter(GetTicketCounts)
                Ds.Clear()
                Da.Fill(Ds)

                With Ds.Tables(0)
                    If .Rows.Count > 0 Then
                        For Each TheDataRow As DataRow In .Rows
                            TicketsByStatusMine.Text = "Mine: " & CStr(TheDataRow.Item("MyCount"))
                            TicketsByStatusOpen.Text = "Open: " & CStr(TheDataRow.Item("OpenCount"))
                            TicketsByStatusPending.Text = "Pending: " & CStr(TheDataRow.Item("PendingCount"))
                            TicketsByStatusWaitingResponse.Text = "Waiting Response: " & CStr(TheDataRow.Item("WaitingResponseCount"))
                            TicketsByStatusWorkInProgress.Text = "Work in Progress: " & CStr(TheDataRow.Item("WorkInProgressCount"))
                        Next TheDataRow
                    End If
                End With
            Catch CastEx As InvalidCastException
                MessageBox.Show(CastEx.Message, _
                                "Invalid Cast Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch ArgEx As ArgumentException
                MessageBox.Show(ArgEx.Message, _
                                "Argument Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch NullRefEx As NullReferenceException
                MessageBox.Show(NullRefEx.Message, _
                                "Null Reference Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch SQLEx As SqlException
                MessageBox.Show(SQLEx.Message, _
                                "SQL Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch FormatEx As FormatException
                MessageBox.Show(FormatEx.Message, _
                                "Format Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch OverflowEx As OverflowException
                MessageBox.Show(OverflowEx.Message, _
                                "Overflow Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Finally
                SetDatabaseConnection.Close()
            End Try
        End Using
Error is in the screenshot.
What I need to do is get the counts and display each one in particular labels.

[BrailleSchool]

If I try the following, I am told "Too many arguments specified."

                With GetTicketCounts
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "SP_GetTicketCountByStatus"
                    .Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = MDIContainer.PersonsName.Text
                    .Parameters("@CreatedBy").Direction = ParameterDirection.Input

                    .Parameters.Add("@MyCount", SqlDbType.Int)
                    .Parameters("@MyCount").Direction = ParameterDirection.Output
                    .Parameters.Add("@OpenCount", SqlDbType.Int)
                    .Parameters("@OpenCount").Direction = ParameterDirection.Output
                    .Parameters.Add("@PendingCount", SqlDbType.Int)
                    .Parameters("@PendingCount").Direction = ParameterDirection.Output
                    .Parameters.Add("@WaitingResponseCount", SqlDbType.Int)
                    .Parameters("@WaitingResponseCount").Direction = ParameterDirection.Output
                    .Parameters.Add("@WorkInProgressCount", SqlDbType.Int)
                    .Parameters("@WorkInProgressCount").Direction = ParameterDirection.Output

                    Dim RetVal As Integer = CInt(.ExecuteScalar)
                End With

I get the same error with the following code, too.

                With GetTicketCounts
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "SP_GetTicketCountByStatus"
                    .Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = MDIContainer.PersonsName.Text
                    .Parameters("@CreatedBy").Direction = ParameterDirection.Input

                    .Parameters.Add("@MyCount", SqlDbType.Int).Direction = ParameterDirection.Output
                    .Parameters.Add("@OpenCount", SqlDbType.Int).Direction = ParameterDirection.Output
                    .Parameters.Add("@PendingCount", SqlDbType.Int).Direction = ParameterDirection.Output
                    .Parameters.Add("@WaitingResponseCount", SqlDbType.Int).Direction = ParameterDirection.Output
                    .Parameters.Add("@WorkInProgressCount", SqlDbType.Int).Direction = ParameterDirection.Output

                    Dim RetVal As Integer = CInt(.ExecuteScalar)
                End With

[jmcilhinney]

You seem to think that using a RETURN statement makes something an output parameter. It does not. Think about a VB method. It can have as many ByVal parameters and ByRef parameters as you like but it can only Return one value. Output parameters in your sproc are like ByRef parameters in your method. Just like the VB method, the sproc can only return one value. If you want output parameters then you have to declare them as such in the sproc. If your sproc does return a value then you need to add a parameter and set its Direction to ReturnValue.

[BrailleSchool]

You seem to think that using a RETURN statement makes something an output parameter. It does not. Think about a VB method. It can have as many ByVal parameters and ByRef parameters as you like but it can only Return one value. Output parameters in your sproc are like ByRef parameters in your method. Just like the VB method, the sproc can only return one value. If you want output parameters then you have to declare them as such in the sproc. If your sproc does return a value then you need to add a parameter and set its Direction to ReturnValue.

I am not sure if I am doing this right or not... Shows Parameter value not supplied for @MyCount. Still working on it...

ALTER PROCEDURE [dbo].[SP_GetTicketCountByStatus]
(
	@CreatedBy AS VARCHAR(250),
	@MyCount AS INT OUTPUT,
	@OpenCount AS INT OUTPUT,
	@PendingCount AS INT OUTPUT,
	@WaitingResponseCount AS INT OUTPUT,
	@WorkInProgressCount AS INT OUTPUT
)
AS
BEGIN

SELECT @MyCount = COUNT(TicketStatus) 
FROM [TicketManager].[dbo].Tickets 
WHERE TicketStatus <> 'Resolved' AND CreatedBy = @CreatedBy
RETURN @MyCount

SELECT @OpenCount = COUNT(TicketStatus) 
FROM [TicketManager].[dbo].Tickets 
WHERE TicketStatus = 'Open'
RETURN @OpenCount

SELECT @PendingCount = COUNT(TicketStatus) 
FROM [TicketManager].[dbo].Tickets 
WHERE TicketStatus = 'Pending'
RETURN @PendingCount

SELECT @WaitingResponseCount = COUNT(TicketStatus) 
FROM [TicketManager].[dbo].Tickets 
WHERE TicketStatus = 'Waiting Response'
RETURN @WaitingResponseCount

SELECT @WorkInProgressCount = COUNT(TicketStatus)
FROM [TicketManager].[dbo].Tickets 
WHERE TicketStatus = 'Work In Progress'
RETURN @WorkInProgressCount
END

                With GetTicketCounts
                    .Connection = SetDatabaseConnection
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "SP_GetTicketCountByStatus"
                    .Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = MDIContainer.PersonsName.Text
                    .Parameters("@CreatedBy").Direction = ParameterDirection.Input

                    .Parameters.Add("@MyCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                    .Parameters.Add("@OpenCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                    .Parameters.Add("@PendingCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                    .Parameters.Add("@WaitingResponseCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
                    .Parameters.Add("@WorkInProgressCount", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

                    Dim RetVal As Integer = CInt(.ExecuteScalar)
                End With

[BrailleSchool]

Okay. This was tough!
I have been able to tabulate the information into columns. I can then get the data into a DataReader and access the information that way.

ALTER PROCEDURE [dbo].[SP_GetTicketCountByStatus]
(
	@CreatedBy AS VARCHAR(250)
)
AS
BEGIN
SELECT
	(SELECT COUNT(TicketStatus) FROM Tickets WHERE TicketStatus <> 'Resolved' AND CreatedBy = @CreatedBy) AS 'MyCount',
	(SELECT COUNT(TicketStatus) FROM Tickets WHERE TicketStatus = 'Open') AS 'OpenCount',
	(SELECT COUNT(TicketStatus) FROM Tickets WHERE TicketStatus = 'Pending') AS 'PendingCount',
	(SELECT COUNT(TicketStatus) FROM Tickets WHERE TicketStatus = 'Waiting Response') AS 'WaitingResponseCount',
	(SELECT COUNT(TicketStatus) FROM Tickets WHERE TicketStatus = 'Work In Progress') AS 'WorkInProgressCount';
END

I know where to go from here.