Passing Stored Procedure Result to textbox

Question

I have the stored procedure below:

ALTER PROCEDURE [dbo].[GetGrossIncome]
@Date1 date, @Date2 date, @GrossTotal int OUTPUT
AS
BEGIN

SELECT @GrossTotal =  sum(GrandTotal) From ClientInvoicedItems where (InvoicePaidStatus ='FULLY PAID' and (Date >=@date1 and Date <=@Date2)) group by GrandTotal 

END

What I want is to calculate the total value in the 'GrandTotal' Column for the date range selected and then save the value in a textbox. Below is the VB code:

 Private Sub GetTotalIncome()
    Dim conn_String As String
    conn_String = "Data Source=Server-Pc;Initial Catalog=AWInformationSystem;Persist Security Info=True;User ID=sa;Password=password1"
    Dim Mycon = New SqlConnection(conn_String)
    ' Dim cmd = New SqlCommand("GetGrossIncome", Mycon)
    ' Dim dAdapter = New SqlDataAdapter(cmd)

    Mycon.Open()
    Dim cmd As SqlCommand = New SqlCommand("GetGrossIncome", Mycon)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@date1", TxtDateTo.Text)
    cmd.Parameters.AddWithValue("@date2", TxtDateFrom.Text)
    cmd.Parameters.Add("@GrossTotal", SqlDbType.Int, 500)
    cmd.Parameters("@GrossTotal").Direction = ParameterDirection.Output
    cmd.ExecuteNonQuery()
    'If Not IsDBNull(cmd.Parameters("@GrossTotal")) Then
    TxtGrossIncome.Text = (cmd.Parameters("@GrossTotal").Value())
    '  End If

    Mycon.Close()

End Sub

When i run the code, if i select the same date as the start and end date, i get a value in the text box but its always the wrong value. when I select a different start date and end date i get an error: 'System.InvalidCastException: 'Conversion from type 'DBNull' to type 'String' is not valid.'

A pointer to the right direction is welcome, thanks in advance.

Answer 1

I'm not sure on the same-day incorrect value, but this is why you're getting the null error:

cmd.Parameters.AddWithValue("@date1", TxtDateTo.Text)
cmd.Parameters.AddWithValue("@date2", TxtDateFrom.Text)

(Date >=@date1 and Date <=@Date2))

Your dates are transposed. @date1 should be TxtDateFrom, and @date2 should be TxtDateTo. For an example, in your current code a from date of 2021-02-01 and a to date of 2021-02-03 would become this SQL filter:

(Date >='2021-02-03' and Date <='2021-02-01'))

Since that's impossible, it's returning no result.

Answer 2

Some database objects like connections and commands need to disposed. Using...End Using blocks handle this even if there is an error.

Don't open the connection until directly before the .Execute...

Don't use .AddWithValue with Sql Server. See http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications Here is another https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html

I have separated the user interface code in the button and the data access code in the function. All the validation of input and message boxes are in the button code.

I am not sure if parameter names are case sensitive in Sql Server but, just for grins, I made them match. @date1 -> @Date1 etc. I don't think you need the Group By phrase in the stored procedure since Sum() is the only element in the Select.

You do not need to check for null because Sum() will return 0 even if no records match the criteria.

Private Function GetTotalIncome(startDate As Date, endDate As Date) As Integer
    Dim TotalIncome As Integer
    Dim conn_String = "Data Source=Server-Pc;Initial Catalog=AWInformationSystem;Persist Security Info=True;User ID=sa;Password=password1"
    Using Mycon = New SqlConnection(conn_String),
            cmd As SqlCommand = New SqlCommand("GetGrossIncome", Mycon)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@Date1", SqlDbType.Date).Value = startDate
        cmd.Parameters.Add("@Date2", SqlDbType.Date).Value = endDate
        cmd.Parameters.Add("@GrossTotal", SqlDbType.Int)
        cmd.Parameters("@GrossTotal").Direction = ParameterDirection.Output
        Mycon.Open()
        cmd.ExecuteNonQuery()
        TotalIncome = CInt(cmd.Parameters("@GrossTotal").Value)
    End Using
    Return TotalIncome
End Function

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim Date1, Date2 As Date

    If Not Date.TryParse(TxtDateFrom.Text, Date1) Then 'Start Date
        MessageBox.Show("Please enter a valid date in the From box")
        Exit Sub
    End If
    If Not Date.TryParse(TxtDateTo.Text, Date2) Then 'End Date
        MessageBox.Show("Please enter a valid date in the To box.")
        Exit Sub
    End If
    If Date1 > Date2 Then
        MessageBox.Show("The start date cannot be greater than the end date.")
        Exit Sub
    End If
    TxtGrossIncome.Text = GetTotalIncome(Date1, Date2).ToString
End Sub

Answer 3

So i managed to get both sets of codes working. Thanks to suggestions from members. The stored procedure was wrongly written, below is the correct one. Thanks to the solution in this link: How to do sum of col results from a SQL Stored Procedure.

ALTER PROCEDURE [dbo].[GetGrossIncome]
@Date1 date, @Date2 date, @GrossTotal int OUTPUT
AS
BEGIN

SELECT @GrossTotal  =  ISNULL(SUM(ISNULL(GrandTotal,0)),0) From 
ClientInvoicedItems 
Where (Date >=@date1 and Date <=@Date2) 

Return
    
END

The correct vb code below:

    Dim Mycon = New SqlConnection(conn_String)

    Mycon.Open()
    Dim cmd As SqlCommand = New SqlCommand
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "GetGrossIncome"
    cmd.Parameters.AddWithValue("@date1", TxtDateFrom.Text)
    cmd.Parameters.AddWithValue("@date2", TxtDateTo.Text)
    Dim sum_values As SqlParameter = New SqlParameter
    sum_values = cmd.Parameters.Add("@GrossTotal", SqlDbType.Decimal)
    sum_values.Direction = ParameterDirection.Output
    cmd.Connection = Mycon
    cmd.ExecuteNonQuery()
    Mycon.Close()
    TxtGrossIncome.Text = sum_values.Value.ToString()