Filtering the Data Using BindingSource

Level: Beginner
Knowledge Required: To understand the following solution you must have knowledge of:

• Data Binding

Description:
We usually use BindingSource to bind the Data with Controls. BindingSource is a powerful tool which not only used for binding but can also be used to quickly Filter and Sort the Data.
We use Filter property of BindingSource to filter the data,

KeywordBindingSource.Filter = "Keyword='Computer'"

In the above statement, word "Keyword" is the Column Name. Therefore the typical syntax for Filtering the Data is

ColumnName (comparison operator) Value

For String and Date Columns we close the Value in Single Quotes and also we can use LIKE operator with String Columns to partially search the value. We can group together multiple Column Filtering by using AND and/or OR operators.

KeywordBindingSource.Filter = "Keyword LIKE 'C%' AND IsSelected=True"

This statement will display those rows in which Keyword starts with "C" and only selected ones.

Other Filtering examples:

MyBindingSource.Filter = "Qty >= 50 AND Order_ID <= 20"
MyBindingSource.Filter = "OrderDate = #1-Jan-2008 3:23:00 PM#"

Please note that in above statement we used "#" sign for Date column therefore Date column can be closed with either Single Quotes or hash (#) sign.

Also consider this:

MyBindingSource.Filter = "CompName='Ahmed''s Bakery'"

In the above example notice the 2 Single Quotes after Ahmed. The actual String Value is Ahmed's Bakery but whenever in string, a Single Quote comes we must replace it with 2 Single Quotes otherwise an exception will be thrown by BindingSource, that's how BindingSource recognizes that String is closed in Single Quotes and any 2 Single Quotes within String is actually part of that String.

Here I have created a simple project which displays a list of Keywords and user has choice to select the Keywords. I have given an option to filter out the Keywords by typing the initial letters and can also filter down the Selected Keywords only by click on the "Display Selected Keywords only" check box. Here is the picture of the Form1




I have created a single method to set filtering:

Private Sub SetFilter()
    Dim sFilter As String = ""
    ' if Display Only Selected Keywords is checked
    If Me.chkIsSelected.Checked Then
        sFilter = "IsSelected=True";
    End If
    ' if some text is given to filter Keywords
    If Me.txtKeywordFilter.Text <> "" Then
        If sFilter <> "" Then sFilter &= " AND "
        sFilter &= "Keyword LIKE '" & Me.txtKeywordFilter.Text.Replace("'", "''") & "%'"
    End If
    ' If NO Filter is found to be created
    If sFilter = "" Then
        ' remove current filter
        Me.bsKeyword.RemoveFilter()
    Else
        ' set that filter
        Me.bsKeyword.Filter = sFilter
    End If
End Sub

This method is called when chkIsSelected CheckBox's Checked State is changed or whenever txtKeywordFilter TextBox's Text property is changed (See Project for detail).

Download the full source code from here

KeywordSelector.rar