Filtering datagridview using dates
I am trying to filter the deadline column in a datagridview with two datetimepickers - startDate and endDate.
datagridview is TaskTable2, datetimepicker1 is startSchedule, datetimepicker2 is the final schedule and the deadline in datagridview is deadlineRow
So far, I have the following code that successfully makes lines invisible that 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.
}
}
}
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 several existing problems:
- The application crashes and I get the following error when I select a date range that will not display tasks:
'The string associated with the position of the currency manager cannot be made invisible'
- I have a print button that is supposed to print the filtered results. However, it prints all the data stored in the datagridview, even if some rows are visible = false from the schedule button clicked, so I guess I need to use a different approach to delete rows rather than hide them.
The datagridview is bound to an XML file, so data can be removed from the datagridview for filtering and printing as it is stored in the XML file.
Any help would be greatly appreciated!
Thankyou
source to share
I would use the property Filter
for bindingsource
for datagridview
. The property Filter
allows you to view a subset of the DataSource.
Example from MSDN :
private void PopulateDataViewAndFilter()
{
DataSet set1 = new DataSet();
// Some xml data to populate the DataSet with.
string musicXml =
"<?xml version='1.0' encoding='UTF-8'?>" +
"<music>" +
"<recording><artist>Coldplay</artist><cd>X&Y</cd></recording>" +
"<recording><artist>Dave Matthews</artist><cd>Under the Table and Dreaming</cd></recording>" +
"<recording><artist>Dave Matthews</artist><cd>Live at Red Rocks</cd></recording>" +
"<recording><artist>Natalie Merchant</artist><cd>Tigerlily</cd></recording>" +
"<recording><artist>U2</artist><cd>How to Dismantle an Atomic Bomb</cd></recording>" +
"</music>";
// Read the xml.
StringReader reader = new StringReader(musicXml);
set1.ReadXml(reader);
// Get a DataView of the table contained in the dataset.
DataTableCollection tables = set1.Tables;
DataView view1 = new DataView(tables[0]);
// Create a DataGridView control and add it to the form.
DataGridView datagridview1 = new DataGridView();
datagridview1.AutoGenerateColumns = true;
this.Controls.Add(datagridview1);
// Create a BindingSource and set its DataSource property to
// the DataView.
BindingSource source1 = new BindingSource();
source1.DataSource = view1;
// Set the data source for the DataGridView.
datagridview1.DataSource = source1;
//The Filter string can include Boolean expressions.
source1.Filter = "artist = 'Dave Matthews' OR cd = 'Tigerlily'";
}
I use this type Filter
to display account based data. For an account, I have a textbox where the user puts in an account number and I use TextChanged
Event to apply the filter. Then I have a button that is used to remove the filter from the binding source.
If you want to filter by date, you can follow the instructions in this SO question:
BindingSource Binding Filter by Date
Using a filter for a date that doesn't exist shouldn't crash the application, it just doesn't display anything.
source to share
An exception solution found here: http://discuss.itacumens.com/index.php?topic=16375.0
I added this to my code just before trying to establish that the line is not visible. row
is my loop variable ForEach
. I check if it is selected and if it tries to clear the row and cell selection before setting the property visible
.
If gridItems.SelectedRows.Count > 0 AndAlso row.Index = gridItems.SelectedRows(0).Index Then
'fixes dumb exception with row.visible = false
gridItems.ClearSelection()
gridItems.CurrentCell = Nothing
End If
The problem seems to be that the current row or cell is not visible.
source to share
I faced the same issue with the exception: "The string associated with the position of the currency manager could not be made invisible."
dgridView.CurrentCell = null;
dgridView.Rows[i].Visible = false;
By simply setting CurrentCell to null, I set it for me. I haven't tested it yet if it breaks something.
source to share