The RowFilter property of the DataView allows to use flexible string similar to SQL or LINQ to easily filter rows wihtout having to iterate through them.
I had to implement a fully flexible search module for a project I’m working on. The user had to be able to perform a string search on any field displayed on the gridview. The above mentioned RowFilter method is really handy to provide such a functionality due to increased efficiency.
Assume following example:
The data table (in the database)
Field Name Data Type
If you want to use the RowFilter to enable flexible searching (i.e. if the user type “j” in the search textbox the search grid view would only show the record for John) you can use following code:
string SearchFor = SearchTextBox.Text; ((DataView)SearchGrid.DataContext).RowFilter = string.Concat("Name LIKE '%", SearchFor, "%'");
This will allow the above explained behaviour so if the user now enter “h” in the textbox it will show both records because both John and Sophie have the letter “h” in their names.
So what if we repeat the same and use the following code for the date of birth field?
// WRONG CODE string SearchFor = SearchTextBox.Text; ((DataView)SearchGrid.DataContext).RowFilter = string.Concat("DateOfBirth LIKE '%", SearchFor, "%'");
We would expect the program to filter records similarly. However, if you enter “1990” in the search textbox hoping it would filter Sophie, it would give you an error instead!
This is because the LIKE operator cannot work with non-charactor types.
We have to use a converter to convert the datetime field into a string just before the RowFilter is applied.
Here’s the code:
string SearchFor = SearchTextBox.Text; ((DataView)SearchGrid.DataContext).RowFilter = string.Concat("CONVERT(DateOfBirth, System.String) LIKE '%", SearchFor, "%'");
The Convert function will cast the datetime value into string just before the LIKE operation takes place. And since the datetime value is only temperory converted, the original data are not affected as well.
So if you add this code to the previously worked code for the Name column as described below:
string SearchFor = SearchTextBox.Text; ((DataView)SearchGrid.DataContext).RowFilter = string.Concat("Name LIKE '%", SearchFor, "%'"); ((DataView)SearchGrid.DataContext).RowFilter = string.Concat("CONVERT(DateOfBirth, System.String) LIKE '%", SearchFor, "%'");
http://pagead2.googlesyndication.com/pagead/show_ads.jsNow if you type “John” the grid will only show records that match that value and if you type a digit (e.g. 30) it will show the record that has 30 in the dob field (i.e. the record for Sophie)
You can use the same method for any other field type which doesn’t support use of LIKE directly.
Was this post helpful to you? How can I improve? – Your comment is highly appreciated!
Cassian Menol Razeek
16 thoughts on “How to use LIKE operator in Dataview.RowFilter for Date Time or Numaric Fields using CONVERT”
It works, but sort function will not work with this technique.
when ever you click on any column. it will through an error “Cannot perform ‘Like’ operation on System.DateTime and System.String.”.
Any solution ?
Non string data columns are converted Only in the filtering process. This filtering doesn’t affect the actual data column so your datetime data column will remain date time. Therefore this doesn’t affect (tested in my application) your sorting or any other grid operation.
This solution was introduced to handle “Cannot perform ‘Like’ operation on System.DateTime and System.String.”. problem so are you sure you did following:
1) added a different row filter for each column that you want to search in?
2) added a convert function for each non-string column?
if it helps, following is the actual code I used in my application which runs perfectly at the moment.
i am using VS 2008 , using C# / SQL with desktop application. my code is here under. can you help with this.
string colName = “”;
case “Our Ref”:
colName = “OurCaseRef”;
Note : Many cases are there
if (colName == “CDOB”)
dtCases.DefaultView.RowFilter = string.Concat(“CONVERT(CDOB, System.String) LIKE ‘%”, txtFilter.Text.Trim(), “%'”);
I don’t see anything wrong in setting the RowFilter value.
Can you debug/trace the click event that causes to throw the exception and see if the value you set has not changed by the time you click on the grid?
Hey thank you so much it works brilliantly.
Thanks , I’ve just been searching for information about this subject for a while and yours is the greatest I have came upon till now.
Thanks it works and saved my life……
Thank you, Menol!
Thanks Subic, for taking time to appreciate this.
only one problem…if i choose to filter date contain chars ’12’ then the result wrong
i think because in convert datetime type also include “xx:xx:xx” for time value.
how can i eliminated this time value ? please help.:)
Thanks for finding the bug. I am busy with different projects but will try to fix this when I have time.
Meanwhile please feel free to fix the bug as you are actively working on this area now.
And please let us know about what you come up with.
thx for respond my question.
i came with little add rowfilter function from your solution,..and just working like i want.
the filter for datecolumn just became :
“SUBSTRING(CONVERT(date,System.String),1,10) LIKE ‘%text_to_find%'”
anyway…i’m glad find your solution here…this is great !
Hello there i saw your solution but i am having hard time on implementing it to my project.
I have a database and from that i am pulling a date value but in my software i need to convert it to a string value so i can put it into textboxes etc. for the name and surname i have this line of code:
Me.CFSDataSet.Customer.Select((“name LIKE'” & nmtxt.Text & “%’ AND surname LIKE'” & sntxt.Text & “%'”)
i need to do this for the dob but since its not a string and cannot be bothered like any other data types, my only idea is the conversion but i couldnt be able to do it. Thanks
I’m sorry I am a little busy with other work right now. I will take a look when I get some free time.
meanwhile please let me know if you solve this yourself so that your solution will help others including me.
Thanks a lot Menol… had been looking for a solution to this for quite sometime now.
Worked perfect 🙂