How to use LIKE operator in Dataview.RowFilter for Date Time or Numaric Fields using CONVERT

MenolJanuary 16, 2012C#.NetAutofilter Datagrid, C#, C#.NET, CONVERT, datagrid, Datagrid Rowfilter, Dataview, Date Time, Datetime, Fields, LIKE operator, Numaric, Row filter, RowFilter, using CONVERT

2012-01-16
//
http://pagead2.googlesyndication.com/pagead/show_ads.js

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

Name String

DateOfBirth DateTime

Data

Name DOB

John 1976-10-12

Sophie 1990-12-30

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.

The Solution!

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, "%'");

The Result

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)

Note
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”

Hi,
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 ?

LikeLike

Hi,

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.

            StringBuilder _sb = new StringBuilder();

            foreach (GridColumn _col in DxGrid.Columns)
            {
                if (_col.Visible &amp;&amp; _col.Width != 5.0)
                {
                    if (_sb.Length &gt; 0)
                    {
                        _sb.Append(&quot; OR &quot;);
                    }

                    // filter out non string columns and convert them to string before applying the filter cos' those columns don't support LIKE statements
                    if (((DataView)DxGrid.DataSource).Table.Columns[_col.FieldName].DataType != typeof(String))
                    {
                        _sb.Append(string.Concat(&quot;CONVERT(&quot;, _col.FieldName, &quot;, System.String)&quot;));
                        _sb.Append(string.Concat(&quot; LIKE '%&quot;, SearchFor, &quot;%'&quot;));
                    }
                    else
                    {
                        _sb.Append(string.Concat(_col.FieldName, &quot; LIKE '%&quot;, SearchFor, &quot;%'&quot;));
                    }
                }
            }
           
            
            ((DataView)DxGrid.DataSource).RowFilter = _sb.ToString();

LikeLike

Moeed says:

April 23, 2012 at 11:20 am

Hi Menol,

i am using VS 2008 , using C# / SQL with desktop application. my code is here under. can you help with this.

string colName = “”;
switch(cboFilter.Text)
{
case “Our Ref”:
colName = “OurCaseRef”;
break;
….
.
.
.
.
Note : Many cases are there
}

if (colName == “CDOB”)
{

dtCases.DefaultView.RowFilter = string.Concat(“CONVERT(CDOB, System.String) LIKE ‘%”, txtFilter.Text.Trim(), “%'”);

LikeLike

Reply
Menol says:

April 23, 2012 at 11:36 am

Hi Moeed,

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?

Menol

LikeLike

Reply
.net performance says:

July 14, 2012 at 9:45 am

Hey thank you so much it works brilliantly.

LikeLike

Reply
daniela says:

July 17, 2012 at 11:50 am

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.

LikeLike

Reply
sandeep says:

September 11, 2014 at 6:03 am

Thanks it works and saved my life……

LikeLike

Reply
Subic says:

September 22, 2015 at 1:21 pm

Thank you, Menol!

LikeLike

Reply
1. Menol says:
  
  September 23, 2015 at 10:08 am
  
  Thanks Subic, for taking time to appreciate this.
  
  LikeLike
  
  Reply
Lieh says:

September 23, 2015 at 12:27 pm

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.:)

LikeLike

Reply
1. Menol says:
  
  September 24, 2015 at 7:59 am
  
  Hi Lieh,
  
  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.
  
  Menol
  
  LikeLike
  
  Reply
  1. Lieh says:
    
    September 24, 2015 at 4:27 pm
    
    Hi Menol,
    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 !
    thank you.
    
    LikeLike
Barış says:

December 22, 2015 at 6:09 pm

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:

CustomerDataGridView.DataSource =
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

LikeLike

Reply
1. Menol says:
  
  January 7, 2016 at 10:15 am
  
  Hi Barış,
  
  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,
  Menol
  
  LikeLike
  
  Reply
Goat says:

January 21, 2016 at 5:05 am

Thanks a lot Menol… had been looking for a solution to this for quite sometime now.
Worked perfect 🙂

LikeLike

Reply
Rajko says:

April 28, 2016 at 3:20 pm

Thanks dude!!

LikeLike

Reply