DataView.RowFilter for DataGridView

Question

Good day dear members, I'm attempting to filter a column in my datagridview to show only the empty cells (i.e. cells with null value). For that I'm using the DataView.RowFilter method. However instead of getting the column to show the empty cells, I'm getting a columnt with no cells to show. Your help and support on this issue is appreicated. Thank you in Advance, Ali

Vulpes · Answer

Amazingly, you're right! It's said on MSDN for as long as I can remember that you had to use the following construction to return only those columns with null values: IsNull(SomeColumnName,'Null Column') = 'Null Column' (See the end of the Remarks section on http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter(v=vs.110).aspx ) so it never occurred to me before that there would be a simpler way. Anyway, if you now OR that expression with your IN expression - enclosing both expressions in brackets to avoid any operator precedence problems - you should get the result you want for any type of column.

Ali Alfaraj · Answer

Thank you Vuples for sharing your code. I just used to below expression to filter for null values and it just worked fine r egardless of the Data Type: dv.RowFilter = SomeColumnName + " Is Null " ;

Vulpes · Answer

OK, I've had a look now at dates and the IsNull function works if we use a date such as '1/1/1' which is unlikely to actually occur as a column value: using System; using System.Data; using System.Collections.Generic; class Program { static void Main() { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Joined", typeof(DateTime))); dt.Rows.Add("Vulpes", new DateTime(2011, 2, 28)); dt.Rows.Add("Barack", new DateTime(2012, 1, 1)); dt.Rows.Add("Arnold", new DateTime(2013, 7, 1)); dt.Rows.Add("Edward", DBNull.Value); DataView dv = dt.DefaultView; dv.RowFilter = "IsNull([Joined], '1/1/1') = '1/1/1'"; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.WriteLine(); var filter = new List {new DateTime(2011, 2, 28), new DateTime(2013, 7, 1)}; string filterValues = "'" + string.Join("','", filter.ToArray()) + "'"; string SomeColumnName = "Joined"; dv.RowFilter = "[" + SomeColumnName + "] IN (" + filterValues + ")"; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.WriteLine(); dv.RowFilter = "([" + SomeColumnName + "] IN (" + filterValues + ")) OR (IsNull([" + SomeColumnName + "], '1/1/1') = '1/1/1') "; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.ReadKey(); } } The output this time (UK locale) is: Edward, Vulpes,28/02/2011 00:00:00 Arnold,01/07/2013 00:00:00 Vulpes,28/02/2011 00:00:00 Arnold,01/07/2013 00:00:00 Edward, So I think you're just going to have to tailor the IsNull expression depending on the datatype of the column. Unfortunately, you can't include NULL within the IN clause itself which would have made life much easier.

Vulpes · Answer

For numeric columns (int say) you'll have to change the IsNull expression to use an actual number rather than 'Null Column' otherwise you'll get an exception when you try to compare the column value with a string. In this revised example I've used -1 which couldn't occur normally in the ID column and it works OK: using System; using System.Data; using System.Collections.Generic; class Program { static void Main() { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("ID", typeof(int))); dt.Rows.Add("Vulpes", 1); dt.Rows.Add("Barack", 2); dt.Rows.Add("Arnold", 3); dt.Rows.Add("Edward", DBNull.Value); DataView dv = dt.DefaultView; dv.RowFilter = "IsNull([ID], -1) = -1"; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.WriteLine(); var filter = new List {1, 3}; //string filterValues = "'" + string.Join("','", filter.ToArray()) + "'"; string filterValues = string.Join(",", filter.ToArray()); string SomeColumnName = "ID"; dv.RowFilter = "[" + SomeColumnName + "] IN (" + filterValues + ")"; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.WriteLine(); dv.RowFilter = "([" + SomeColumnName + "] IN (" + filterValues + ")) OR (IsNull([" + SomeColumnName + "], -1) = -1) "; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.ReadKey(); } } The output now is: Edward, Vulpes,1 Arnold,3 Vulpes,1 Arnold,3 Edward, I thought that your own IN expression wouldn't work for numbers unless we removed the surrounding quotes but, curiously, it works whether the quotes are there or not :-/ As I'm short of time just now, I'll have a look at dates later today.

Vulpes · Answer

If SomeColumnName is the name of the column you want to filter on, the following RowFilter expression should filter out those rows for which that column has a null value: dataView1.RowFilter = "IsNull(SomeColumnName,'Null Column') = 'Null Column'";

Ali Alfaraj · Answer

Thank you for sharing your code. However, this seems to only work for data of type string. I tried applying it on DateTiem and numbers and I got an error.

Vulpes · Answer

I thought I'd just try and model the situation with this 'quick and silly' example: using System; using System.Data; using System.Collections.Generic; class Program { static void Main() { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Occupation")); dt.Rows.Add("Vulpes", "Programmer"); dt.Rows.Add("Barack", "President"); dt.Rows.Add("Arnold", "Movie Star"); dt.Rows.Add("Edward", DBNull.Value); DataView dv = dt.DefaultView; dv.RowFilter = "IsNull([Occupation],'Null Column') = 'Null Column'"; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.WriteLine(); var filter = new List {"Programmer", "Movie Star"}; string filterValues = "'" + string.Join("','", filter.ToArray()) + "'"; string SomeColumnName = "Occupation"; dv.RowFilter = "[" + SomeColumnName + "] IN (" + filterValues + ")"; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.WriteLine(); dv.RowFilter = "([" + SomeColumnName + "] IN (" + filterValues + ")) OR (IsNull([" + SomeColumnName + "], 'Null Column') = 'Null Column')"; foreach(DataRowView drv in dv) { Console.WriteLine(String.Join(",", drv.Row.ItemArray)); } Console.ReadKey(); } } The output is: Edward, Vulpes,Programmer Arnold,Movie Star Vulpes,Programmer Arnold,Movie Star Edward, which seems to be working OK as Edward is the only one with a null occupation. He therefore shows up in the first and third filter expressions but not in the second. Perhaps I'm misunderstanding what's needed here?

Ali Alfaraj · Answer

It did not work even before we OR'd the expressions together. The FilterValue expression is below: string filterValues = "'" + string .Join( "','" , filter.ToArray()) + "'" ; Where filter is a List .

Vulpes · Answer

Did it work OK before we OR'd the expressions together? Can you also tell me what 'filterValues' contains. Is it a string or an array?

Ali Alfaraj · Answer

Hello Vuples, Still it is giving a column with no cells (i.e. filtered out all rows!). Any thoughts? Regards, Ali

Vulpes · Answer

I'd try OR'ing the two expressions together: dataView1.RowFilter = "([" + SomeColumnName + "] IN (" + filterValues + ")) OR (IsNull([" + SomeColumnName + "], 'Null Column') = 'Null Column')";

Ali Alfaraj · Answer

Dear Vulpes, Thank you for the kind response. Now, what if I want filter my column to only show 'someValue' and empty cells (i.e. multi-variable filteration). For that I would use the below syntex: dataView1.RowFilter = "[" + SomeColumnName + "] IN (" + filterValues + ")" ; Where SomeColumnName is the name the column I want to filter on a nd filterValues is an array of values I want to apply filteration on. How can I capture the null value RowFilter expression you mentioned in your reply in this Array. Thank you in advance, Ali

DataView.RowFilter for DataGridView

Answers (12)