שתף באמצעות

How to Filter BindingSource for Cells that are Null or WhiteSpace in VB.NET?

Question

Tuesday, February 7, 2012 10:35 PM

I have a BindingSource that is my DataGridViews data source.  I want to filter for all rows where column1 is blank.  For example, one row may contain a null value, some rows may contain just white space, and other rows may contain text.  How do I show just the rows with text (or numeric, or date) values?

Also, how do I filter for non-blank rows?  This would show me all rows that contain a blank cell.

Me.BindingSource.Filter = ?

Thanks in advance!

Ryan

All replies (8)

Thursday, February 9, 2012 4:14 PM ✅Answered | 1 vote

The trim function is supported in bindingsource.filter expression. Therefore, you can have a filter like this:

Me.BindingSource.Filter = "YourColumnName Is Null Or Trim(YourColumnName) = ''"

Note that it's 2 single quotes after the = sign, and then the double quote to close the whole filter string.

Wednesday, February 15, 2012 10:02 AM ✅Answered

Hi Ryan,

Please try to set the filter like this way to filter one day:

.RowFilter = "columnName >= #2/15/2012# and columnName< #2/16/2012#"

This filter will return the data with date is 2/15/2012

I hope this will be helpful.

Best regards,

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Tuesday, February 7, 2012 10:45 PM

Hello Ryan0827,

I have a BindingSource that is my DataGridViews data source.  I want to filter for all rows where column1 is blank.  For example, one row may contain a null value, some rows may contain just white space, and other rows may contain text.  How do I show just the rows with text (or numeric, or date) values?

Also, how do I filter for non-blank rows?  This would show me all rows that contain a blank cell.

Me.BindingSource.Filter = ?

Thanks in advance!

Ryan

to filter the data through the Filter property should the sql code, for example
Fltrare data that exist with a text box

         TABLE1BindingSource.Filter = "YOUR FIELDNAME LIKE '" + TextBox1.Text + "'")

Filter strings with blank spaces

         TABLE1BindingSource.Filter = "YOUR FIELDNAME LIKE '" + TextBox1.Text.Equals (String.Empty) + "'")

Filter DBNull Values

         TABLE1BindingSource.Filter = "YOUR FIELDNAME LIKE '" + TextBox1.Text.Equals (DBNull.Value) + "'")

If you want to run with a control DataeTime, instead of a text box, enter the Text property of the DateTime

Regards.

Tuesday, February 7, 2012 10:58 PM

I am not using a TextBox as a source to filter, like your example uses.  I am clicking a button on a ContextMenuStrip.  For example, the user clicks a button named "Show Blanks".  I need to build a filter string to filter my BindingSource for blanks.

Also, the other button on the ContextMenuStrip is "Show Non-Blanks".  I need to build a filter string to filter my BindingSource for non-blanks.

The code snippet below show Null rows but not white space rows.  How do I show all blank rows?

Me.DataGridView.Columns(0).DataPropertyName + " IS NULL"

Ryan

Thursday, February 9, 2012 2:55 AM

Hi Ryan,

How are you?

Here is the filter string for your reference:

        'Add data manually
        Dim dt As New DataTable
        dt.Columns.Add(New DataColumn("c1", GetType(String)))
        dt.Columns.Add(New DataColumn("c2", GetType(Boolean)))

        For i = 1 To 10
            dt.Rows.Add(i, CBool(i Mod 2))
        Next
        dt.Rows.Add(DBNull.Value, False)
        dt.Rows.Add("", False)
        Dim dv As DataView = dt.DefaultView
        dv.RowFilter = "c1 = ''"
        Me.DataGridView1.DataSource = dt
        Me.TextBox1.Text = 0

        'Retrieve data from Sql server
        Dim conn As SqlConnection = New SqlConnection("Data Source=172.18.7.49;user ID= usermf;Initial Catalog=dgvc;Persist Security Info=True;")
        Dim cmd As SqlCommand = New SqlCommand("Select * from testDBNull", conn)
        Dim ad As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim dt2 As New DataTable
        ad.Fill(dt2)
        Dim dv2 As DataView = dt2.DefaultView
        dv2.RowFilter = "c1 = '' or c1 is null"
        Me.DataGridView1.DataSource = dt2

I hope this will be helpful.

Best regards,

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Thursday, February 9, 2012 3:47 PM

I have a BindingSource that is my DataGridViews data source.  I want to filter for all rows where column1 is blank.  For example, one row may contain a null value, some rows may contain just white space, and other rows may contain text.  How do I show just the rows with text (or numeric, or date) values?

Also, how do I filter for non-blank rows?  This would show me all rows that contain a blank cell.

Me.BindingSource.Filter = ?

Thanks in advance!

Ryan

Hi Ryan

something else to note is that an empty string does not equal whitespace. An entry that has only spaces will appear "blank" in the datagridview, while in fact it is a valid string. For these you would need to use the Trim method on the item value.

Do you want to only check for an empty string and a Null value - which Mike gave an example for?

Or do you also want check if the value is also only spaces?

I don't believe this is possible with the BindingSource Filter or DataView RowFilter. You would need to use a LINQ query for that.

Friday, February 10, 2012 6:24 PM

Thank you for all the replies.  I did finally come across some documentation on what functions are supported in the BindingSource Filter property.  You can find that here:  http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.85).aspx

I starting using the filter string, which works great.

' filter for blanks
BindingSource.Filter = "TRIM(ISNULL(" & Me.dgv.Columns(ht.ColumnIndex).DataPropertyName & ", '')) LIKE ''"

' filter for non-blanks
BindingSource.Filter = "TRIM(ISNULL(" & Me.dgv.Columns(ht.ColumnIndex).DataPropertyName & ", '')) NOT LIKE ''"

But I have come across another issue trying to filter dates in a column.  I have a DataGridView column and a DateTimePicker that formats the data they contain to look like this '2/10/2012'.  But my underlying datatable has this format '2012-02-10 12:09:00.000'.  For example,

This is my what the underlying data table looks like.

How do I filter for all rows that equal '2/10/2012'?  I basically only care about the date, not the time.  I don't think the Filter property supports a Format function on the data table column, does it?  The following lines of code do not work and the reason why is commented in the code.  Is there a way to Format the column date before it is compared with the formatted DateTimePicker1 value?

' doesn't work because I get an error "Expression Contains Undefined Function call FORMAT()
BindingSource.Filter = "FORMAT(" & Date & ", 'M/dd/yyyy') = " & String.Format("'#{0:M/dd/yyyy}#'", DateTimePicker1.Value)

' returns no rows
BindingSource.Filter = "Date = #2/10/2012#"

' returns no rows
BindingSource.Filter = "Date = #" & DateTimePicker1.Value & "#"

Any ideas?

Ryan

Thursday, February 16, 2012 10:26 PM

Thanks Mike!  I should have know that already.  I guess when you are thinking hard for too long, the easy answers can excape you.

Ryan