none
Bindingsource.Filter multiple criteria RRS feed

  • Question

  • I have data table bound to a binding source and i am trying to filter using following code but it does not give me the result i was expecting. Could you please tell me how to filter the right way?


    thanks


    • Edited by Shan1986 Saturday, September 14, 2019 4:39 PM
    Saturday, September 14, 2019 4:38 PM

Answers

  • Hi, Thanks for both the answers.

    Les , i tried your suggestion but it did't work but i solved it with following code . it seems all operand for the same column must be in brackets () like below.  

    "Col1 Like 'a' and (Col2 Like '2' or Col2 Like '3')"
    • Marked as answer by Shan1986 Sunday, September 15, 2019 8:53 AM
    Sunday, September 15, 2019 7:20 AM

All replies

  • Hi

    This gives the expected result. I suppose your problem stemmed from the construction of the Filter. Operands need to be part of the Filter.

    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim dt As New DataTable("Freddy")
    	Dim bs As New BindingSource
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		With dt
    			.Columns.Add("Col1")
    			.Columns.Add("Col2")
    			.Columns.Add("Col3")
    
    			.Rows.Add("a", 1, "a1")
    			.Rows.Add("a", 2, "a2")
    			.Rows.Add("b", 2, "b2")
    			.Rows.Add("b", 2, "b2")
    			.Rows.Add("a", 3, "a3")
    		End With
    		bs.DataSource = dt
    		DataGridView1.DataSource = bs
    		bs.Filter = "Col1 Like 'a' and Col2 Like '2' or Col2 Like '3'"
    	End Sub
    End Class


    Regards Les, Livingston, Scotland

    Saturday, September 14, 2019 5:30 PM
  • Hello,

    The first thing is to understand the LIKE operator, here is a simple reference. Then note the syntax, all use a percent symbol, for the BindingSource filter is an asterisk.

    Example I want  

    • CompanyName to start with a
    • Title to start with o
    • Address to start with m

    Before filtering is the first image, second is with the filter.

    bsCustomers.Filter = 
        "CompanyName LIKE 'a*' AND Title LIKE 'o*' AND Address LIKE 'm*'"


    Also they can be dynamic e.g. get text from TextBox controls as per below.

    bsCustomers.Filter = $"CompanyName LIKE '{CompanyNameTextBox.Text}*' AND " & 
                         $"Title LIKE '{TitleTextBox.Text}*' AND " & 
                         $"Address LIKE '{AddressTextBox.Text}*'"

    The * could be left out so the filter can be even more flexible.

    Lastly see my extension methods for filtering a BindingSource.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, September 15, 2019 1:12 AM
    Moderator
  • Hi, Thanks for both the answers.

    Les , i tried your suggestion but it did't work but i solved it with following code . it seems all operand for the same column must be in brackets () like below.  

    "Col1 Like 'a' and (Col2 Like '2' or Col2 Like '3')"
    • Marked as answer by Shan1986 Sunday, September 15, 2019 8:53 AM
    Sunday, September 15, 2019 7:20 AM