Filtering the specified records in Webforms

Question

Problem:

I'm working on an ASP.NET WebForms application where I need to filter records in a DataTable based on a date range. The date range is selected using FromMonth, ToMonth, FromYear, and ToYear dropdowns. The goal is to display the filtered data in a RadGrid, but my filtering logic is not working as expected. Sometimes, no data is displayed, or the results are incorrect.

Issue:

The filtering logic based on the PADate field is not working as expected. Even when I select a valid date range using the dropdowns, the grid either shows no data or the wrong results.

Debugging Steps I've Taken:

Verified that the PADate field in the database is in the correct DateTime format.
Checked that the values from the dropdowns (FromMonth, ToMonth, FromYear, and ToYear) are correct.
Ensured that the PADate field is correctly parsed as a DateTime value in the filtering logic.

Expected Outcome:

I want the grid to display only the records where the PADate falls within the selected date range (from FromMonth/FromYear to ToMonth/ToYear).

Code:

Here is the method I'm using to filter the data:

protected void PCApplicationStatus()
{
    try
    {
        // Get the selected values from dropdowns
        int fromMonth = int.Parse(FromMonth.SelectedValue);
        int toMonth = int.Parse(ToMonth.SelectedValue);
        int fromYear = int.Parse(FromYear.SelectedValue);
        int toYear = int.Parse(ToYear.SelectedValue);

        // Create date range
        DateTime startDate = new DateTime(fromYear, fromMonth, 1);
        DateTime endDate = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));

        // Validate that 'From' date is earlier than 'To' date
        if (fromYear > toYear || (fromYear == toYear && fromMonth > toMonth))
        {
            throw new InvalidOperationException("The 'From' date must be earlier than the 'To' date.");
        }

        // Fetch data from database
        AdDAL obj = new AdDAL();
        DataSet ds = obj.ExecutePaymentClaimQuery();

        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
        {
            DataTable dt = ds.Tables[0];
            DataTable filteredDt = dt.Clone();  // Clone the structure of the original table

            // Filter rows based on the date range
            foreach (DataRow dr in dt.Rows)
            {
                DateTime? paDate = dr["PADate"] is DBNull ? (DateTime?)null : Convert.ToDateTime(dr["PADate"]);
                
                if (paDate.HasValue && paDate.Value >= startDate && paDate.Value <= endDate)
                {
                    filteredDt.ImportRow(dr);  // Import rows that fall within the date range
                }
            }

            // Bind the filtered data to the grid
            if (filteredDt.Rows.Count > 0)
            {
                Grid.DataSource = filteredDt;
            }
            else
            {
                Grid.DataSource = null;  // No matching rows found
            }

            Grid.DataBind();  // Rebind the grid with the filtered data
        }
        else
        {
            // No data available
            Grid.DataSource = null;
            Grid.DataBind();
        }
    }
    catch (Exception ex)
    {
        // Log the error
        Console.WriteLine("Error: " + ex.ToString());
    }
}

DateTime endDate = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));' Don't do that. Get the first day of the next month, and then use a < parameter. That way things will work for things at 2pm on the last day. — mjwills
– mjwills, Commented Oct 24, 2024 at 5:13
Grid.DataSource = null; // No matching rows found Why do that? Why not always just do Grid.DataSource = filteredDt;? — mjwills
– mjwills, Commented Oct 24, 2024 at 5:14

Albert D. Kallal · Accepted Answer · 2024-10-27 22:01:51Z

You have a database engine, and thus attempting to write looping code to filter data is simply the wrong approach, and too much work.

And, it looks like you using the dataset designer, and that allows you add custom methods to the datasets you create with the dataset designer.

Hence, build (add) a new adaptor method to the existing data adaptor you have.

For example, this:

And then this:

So, now the table adapter has parameters.

Hence, you code would now become this:

        // Create date range
        DateTime startDate = new DateTime(fromYear, fromMonth, 1);
        DateTime endDate = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));

        if (startDate > endDate)
        {
            // display message to user and exit
            string sJava = @"alert('starting date cannot be\ngreater then end date')";

            ScriptManager.RegisterStartupScript(this, this.GetType(), "myjava", sJava, true);
            return;
        }

        GridView1.DataSource = da.GetDataByDate(startDate, endDate);
        GridView1.DataBind();

And, if for some reason you can't add or change the existing dataset and method?

Then once again, no need for looping code. Assuming you like extra work, and don't change the dataset as per above? Then you can filter against the table.

Our code would thus become this:

        // Create date range
        DateTime startDate = new DateTime(fromYear, fromMonth, 1);
        DateTime endDate = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));

        if (startDate > endDate)
        {
            // display message to user and exit
            string sJava = @"alert('starting date cannot be\ngreater then end date')";

            ScriptManager.RegisterStartupScript(this, this.GetType(), "myjava", sJava, true);
            return;
        }

        DataTable dtHotels = da.GetData();
        string strFiler =
            $"[BookingDate] >= '{startDate.ToShortDateString()}' " +
            $"AND [BookingDate] <= '{endDate.ToShortDateString()}'";

        dtHotels.DefaultView.RowFilter = strFiler;

        GridView1.DataSource = dtHotels.DefaultView;
        GridView1.DataBind();

So, makes little sense to write looping code when you have a whole database system designed for such tasks.

And a bad input date range should not trigger a code error, and you don't give any feed back to the end user.

So, say this markup for above code:

        <asp:GridView ID="GridView1" runat="server"
            AutoGenerateColumns="False" DataKeyNames="ID"
            CssClass="table table-hover" Width="50%"
            >
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="HotelName" HeaderText="HotelName" />
                <asp:BoundField DataField="Description" HeaderText="Descripiton" />
                <asp:BoundField DataField="BookingDate" DataFormatString="{0:d}"  
                    HeaderText="Booking Date" 
                    ItemStyle-Width="120"
                    />
            </Columns>
        </asp:GridView>

So, for entering a start date > end date, then we have this effect:

And, assuming a valid date, then we have this effect:

Also, if that date column includes date + time, then you need to add +1 to the final date you are using.

Collectives™ on Stack Overflow

Filtering the specified records in Webforms

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related