Filtering Binding Source with DateTime as data type

Question

I am stuck.

I am using DataGridView to show my BindingSource. Previously, my BindingSource had only string columns. Due to this, the date-time column was not getting sorted properly. Now when I changed my BindingSource column type to DateTime, my Sorting works, but filtering (a searching feature of my product) fails, I get the following exception:- 

                        The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.

Eg:- If the date is "10\21\2011" and I tried to search "10", I must get the column filtered.

 

Following is the source:- 

                public static void FilterRows(this DataGridView dataGridView, string filter)
		{
			if (dataGridView == null || dataGridView.DataSource == null)
				return;

			filter = filter.Replace("%", "").Replace("*", "");

			if (dataGridView.DataSource is BindingSource)
			{
				BindingSource bindingSource = (BindingSource)dataGridView.DataSource;

				string filterExpression = "";
				foreach (DataGridViewColumn dgvColumn in dataGridView.Columns)
				{
                    			if (!string.IsNullOrEmpty(filterExpression))
							filterExpression += " OR ";
                   			if (dgvColumn.ValueType == typeof(DateTime))
                    			{   
                        			filterExpression += String.Format("[{0}] >= #{1}#", dgvColumn.Name, filter.Trim());
                    			}
                    			else
                   			{
						
						filterExpression += String.Format("[{0}] LIKE '*{1}*'", dgvColumn.Name, filter.Trim());
					}
				}
				try
				{					
					bindingSource.Filter = filterExpression;
				}
				catch (Exception e)
				{

					// Trace Action
				}
			}
		}

 

Is there any way I can filter strings as numbers in DateTime column? A work around is to keep 2 Data Source (One for searching and other for sorting). But I don't want to do this. Also "#" and "*" crashes bindingSource.Filter. Is there any way I can successfully filter these? As you may find, currently I am skipping these characters.

Please Help. :-)

---

CHEERS!!!

Answer 1

If your code uses back-slashed inside a date to seperate date, time and a year, the whole string cannot be recognized as a valid datetime. Iam talking about "\". DateTime may have slashes (not back-slashed), so the contrary - "/"- to be recognized as a valid datetime.

How come that these strings have back-slashes?

There is no problem when the string is only a string. The problem occurs when you want to use this string (these strings) as dates, to do some filtering. And you shouldn`t filter only by some strings (like you did by filtering of "10"). Thats why the type DateTime (or Date) is. To do the decent filtering as well (not only to show dates).

Solution: I would suggest you 1st to use values of strings that can be converted to dates, and if they will, you will have to hard work to do the filetering, based on days, months or years (or some other time unit). But as long a your "date" string will have a back-slashes inside of it, this value cannot be recognized as a valid date.

---

Mitja

Answer 2

Oh.... I am extremely Sorry...   date is "10/21/2011". Wrote backslash by mistake.

I didn't get your solution much but I have a Vista Style search box for my DataGridView. Is there no way I can filter dateTime and String Type simultaneously???

 

Also, DateTime Expression uses > , < etc (other than =) operator.

Will it be accurate????

 

Thanks for a quick reply :-) 

---

CHEERS!!!

Answer 3

If you string CAN be recognized as a valid datetime (like now it surely must be while using slashes), I dont see any reason why to bother using strings. Convert stirng to datetime, and use it to do the appropriate filtering.

Is you datagridview databound? Is this dataTable? If so, you can use Select method if dataTable, which will do the filterting (this is one of the possibilities).

---

Mitja

Answer 4

Yes my DataGridView is DataBound and it is a DataTable.

I tried that (converting string to DateTime), but it raises exception. Plus, "10" may not be a valid DateTime but it should still filter the Date mentioned.

 

---

CHEERS!!!

Answer 5

When converting provide 2nd argument to "ToDateTime() method and use InvarianztCulture and do it this way:

 string str = "10/21/2011";
 DateTime date = Convert.ToDateTime(str, System.Globalization.CultureInfo.InvariantCulture);

---

Mitja

Answer 6

Same Exception:- 

The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.

---

CHEERS!!!

Answer 7

So there is some other char on index 0 in the string, like exception says.

It must one like:

"a10/21/2011". --> index 0 is "a" in this example.

--------------------

If all the strings include some char at index 0 then you can try to remove it, and then parse it to date:

 string str = "a10/21/2011";
 DateTime date = DateTime.MinValue;
 if (!DateTime.TryParse(str, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out date))
 {
         str = str.Remove(0, 1);
         try
         {
                 date = Convert.ToDateTime(str, System.Globalization.CultureInfo.InvariantCulture);
         }
         catch
         {
                  //still error
         }
 }

---

Mitja

Answer 8

Yes you are absolutely correct. But, I can not rely on such exceptions because the list may not contain any value. In fact, only one column is suppose to have proper Date-Time value in format - DD/MM/YYYY HH:MM:SS AM/PM and searching must be common to all the columns of my GridView... 

---

CHEERS!!!

Answer 9

Hi Varun Pandey,

Welcome to the MSDN Forum.

If you want to sort the DateTime column, you can set the expressions like columnTypeName > "Your specified time" or you can use "<" operations here. It is similar to T-SQL.

Here are the specifications on the expressions:

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx 

Or you can put the demonstrated data for us to test, we will be appreciate to help you to create the filter expressions.

Best Regards

---

Neddy Ren [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 10

Thanks Neddy for replying. Exactly this is what I have done. Just that it is not always that certain that the user will type valid DateTime.

The user my enter "10" and the date  "10/11/2011" should get filtered. Although "10" isn't a date, the date I mentioned has the digits that qualifies it.

Please help!!!

 

---

CHEERS!!!

Answer 11

Hi Varun Pandey,

It is a spam inputs for the DateTime type filter that the user just enter a "10". So, I think you will need to add an validating method to check the input string can be converted to a DateTime before you filter the data. If the input can not be converted, than, you will need to show the infomation to the user.

Best Regards

---

Neddy Ren [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 12

Absolutely true. If I do that code works fine and doesn't throws any catch. But a basic Issue with this is that I can still not filter the date. I mean, you are right that the value "10" is spam. But the Date "10/11/2011" still contains "10" and thus it should be Filtered properly. If I check for DateTime validation, of course doesn't return a true, due to which DateTime column does not get filtered.

Is there any way I can try to compare DateTime with the Span i/p????

 

---

CHEERS!!!

Answer 13

Absolutely true. If I do that code works fine and doesn't throws any catch. But a basic Issue with this is that I can still not filter the date. I mean, you are right that the value "10" is spam. But the Date "10/11/2011" still contains "10" and thus it should be Filtered properly. If I check for DateTime validation, of course doesn't return a true, due to which DateTime column does not get filtered.

Is there any way I can try to compare DateTime with the Span i/p????

 

---

CHEERS!!!

I have a workrgound is:

Create an another column in the Datatable which store all the formatted DateTime strings based on the DateTime column. And hide the column and don't show it to the users.

when you want to filter the Datetime, you can search it from that formatted column.

suppost you the dateTime is "10/11/2011", you can formate it as "20111011".

if user enter "10", you can use "newColumnName like '%10%'" as  a filter in the bindingSource.

Hope it helps.

---

Knowledge will change the destiny.

Answer 14

Oh.... why didn't I think of this before? Probably, human mind gets stuck some times. Smart and Simple work around.

And I was hoping we can do any thing to the Expression to get the Value...

I am marking you as the answer. Thanks a lot.

Could you also tell me how to filter "%" and "'"... 

 

 

---

CHEERS!!!