DataSet Filter between 2 dates

Question

Hello I posted a few days ago a similar question and I was told to use a dataset filter to filter my datagridview. I've had a go but I'm struggling to work out how to do it.

I'm trying to filter a deadline column in a datagridview by 2 datetimepickers - startDate and endDate.

datagridview is TaskTable2, datetimepicker1 is startSchedule, datetimepicker2 is endSchedule and deadline in datagridview is deadlineRow

TaskDataSet in the underlying datasource.

So far I have got the following code which is successfully making the rows invisible which are not between the selected start and end date.

private void scheduleButton_Click(object sender, EventArgs e)
    {

        DateTime startSchedule = startDate.Value.Date;
        DateTime endSchedule = endDate.Value.Date;

        if (startSchedule <= endSchedule)// runs foreach loop if startdate and enddate are valid
        {
            foreach (DataGridViewRow dr in TaskTable2.Rows)// loops through rows of datagridview
            {
                string deadline = dr.Cells["Deadline"].Value.ToString(); // gets deadline values
                DateTime deadlineRow = Convert.ToDateTime(deadline); // converts deadline string to datetime and stores in deadlineRow variable

                if (startSchedule <= deadlineRow && deadlineRow <= endSchedule) // filters deadlines that are => startDate and <= endDate
                {
                    dr.Visible = true; // display filtered rows here.
                }
                else
                {
                    dr.Visible = false; // hide rows that are not beteen start and end date.
                    TaskTable2.CurrentCell = null;
                }

            }
        }
        else
        {    
            MessageBox.Show("Please ensure Start Date is set before End Date."); // ensures user selects an end date after the start date.
        }
    }

However, I have a few existing problems:

1. Using the visible property is the wrong approach, I need to filter the dataset using something like the following (not sure how to do it)

   TaskDataSet.Filter = "startSchedule <= Deadline AND Deadline <= endSchedule";
   

2. I have a print button that is supposed to print the filtered results. However, it is printing all data stored in the datagridview, even if some rows are visible=false from pressing the schedule button so this is why I need to filter the dataset and then use that in the print event.

The datagridview is bound to an XML file so data can be removed from the datagridview for filtering and printing aslong as they remain in the XML file.

If someone could amend my code with a dataset filter rather than using the visible property it would be greatly appreciated.

Thanks!

Answer 1

You filter code could be:

DateTime startSchedule = startDate.Value.Date;
DateTime endSchedule = endDate.Value.Date;

TaskDataSet.Filter = "Deadline >='" + startSchedule + "' AND Deadline <= '" + endSchedule + "'";

As far as your second issue with the printing of the filtered results - found this link online in VB.NET but you could convert it to C#

Print DataSet or DataTable contents from VB.NET

You can also try the DataSet.Clone() and loop through the rows to remove the filtered items then print the remaining records.

DataGridView Printing by Selecting Columns/Rows

How can I print data from a DataGridView in C#?

Answer 2

suppose that your dataset is named 'ds' and the data is stored in the first table, you could do this:

ds.Tables[0].DefaultView.RowFilter = 
    string.Format("Deadline between ( {0}, {1})",startSchedule,endSchedule );

Note that I didn't test this.