Calculating total sum of money between dates and displaying the result in label control

Question

Hello,

I have been trying to calculate the sum of a particular column in database and display the result in a label control. The column is set as Nvarchar(max). I also need to search for and display the sum based on two dates. I only have one date column in the database.

When a user adds dates which he or she wants to search between, the user will input the "FROM" AND "TO" dates in the provided boxes and clicks on search button and the total sum of the figures according to the dates will show in a label.

The issues I have are:

1. The display total sum does not return the correct summation figures
2. After I make the first search, and try to search for other dates period, I get an error that says: "object cannot be cast from DBNull to other types

Please what should I do here?

Table example

ID Uid	Due CreatedDate
1 1	23000.00 Nov 18 2024 1:39AM
2 1	1000.00 Nov 23 2024 1:39AM

Here is my HTML

<div class="row col-sm-11" style="margin-right: auto; margin-left: auto;">
                                <div class="col-sm-1">
                                    <label style="font-weight: 500; color: #5f5f5f;">FROM</label>
                                </div>
                                <div class="col-sm-4">
                                    <input type="date" id="datepicker" name="dateclosed" runat="server" class="form-control" style="font-size: 9pt" placeholder="Select date" />
                                </div>
                                <div class="col-sm-1">
                                    <label style="font-weight: 500; color: #5f5f5f;">TO</label>
                                </div>
                                <div class="col-sm-4">
                                    <input type="date" id="datepicker1" name="dateclosed" runat="server" class="form-control" style="font-size: 9pt" placeholder="Select date" />
                                </div>
                                <div class="col-sm-2">
                                    <asp:Button ID="Button1" runat="server" CssClass="btn btn-primary" Text="Search" OnClick="Button2_Click" />
                                </div>
                                <br />
                                <div class="cardbody" style="text-align: center;">
                                    <asp:Label ID="TotalRev" runat="server" Text="500"></asp:Label>
                                </div>
                            </div>

C#

protected void Button2_Click(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection con = new SqlConnection())
                {
                    int count = 0;
                    con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = "SELECT SUM(CAST(due as money)) FROM TableRecords WHERE Uid = @Uid AND DateCreated BETWEEN @StartDate AND @EndDate";
                        cmd.Parameters.AddWithValue("@Uid", 1);
                        cmd.Parameters.AddWithValue("@StartDate", DateTime.Parse(datepicker.Value));
                        cmd.Parameters.AddWithValue("@EndDate", DateTime.Parse(datepicker1.Value));
                        cmd.Connection = con;
                        con.Open();
                        count = Convert.ToInt32(cmd.ExecuteScalar());
                        TotalRev.Text = count.ToString("###,##0");
                        con.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

Answer 1

if no rows are found, sum() returns null. this can not be converted to int. if you want 0, then you can fix query:

SELECT ISNULL(SUM(CAST(due as money)),0) FROM TableRecords WHERE Uid = @Uid AND DateCreated BETWEEN @StartDate AND @EndDate";

also because you are casting money to an int, you lose the pennies.

also be sure the start date is less than the end date.

if the picker date does not include the time, then no entries past midnight will be included. maybe you want to cast DateCreated to a date.

SELECT ISNULL(SUM(CAST(due as money)),0) 
FROM TableRecords WHERE Uid = @Uid 
   AND CAST(DateCreated as DATE) BETWEEN @StartDate AND @EndDate