none
Filtering datagridview with combobox RRS feed

  • Question

  • Hi, I've got a datagridview that I want to filter with three combobox: I need it to filter with data from only one combo, with two or with the three. I've got this code:

    AccessBDBindingSource.Filter = String.Format("[Field1] like '%{0}%' AND  [Field2] like '%{1}%' AND [Field3] like '%{2}%'", Me.ComboBox1.Text, Me.ComboBox2.Text, Me.ComboBox3.Text)

    This works but I have a problem: In "Field1" I've got these values: 1r, 2n, 3r, 4t, 5e, 6e (they show up in the combobox1) but the code doesn't filter if I choose 3r or 5e in the combobox1

    If I remove the % in the code (just in "Field1") then it does filter those values but it doesn't filter the other fields on their own, only if I filter them with field1.

    If I remove all the % nothing works.

    Help, please!

    Friday, January 26, 2018 12:48 PM

Answers

  • One suggestion, create the filter then create an SQL SELECT statement from the filter and try it out in MS-Access.

    Here I do just that, I load a table into a DataTable which becomes the DataSource of a BindingSource which in turn becomes the data source of a DataGridView.

    I use three TextBox controls to keep things simple and under control. You can always mix it up by using different values in the TextBox controls and run them in MS-Access query window. You should get the same results in code as you do in ms-access and if not modify the ms-access query till it works then adapt this to your code for the BindingSource filter.

    Public Class FilterForm
        Private bsCustomers As New BindingSource
        Private ops As New DataOperations
        Private Sub FilterForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            bsCustomers.DataSource = ops.GetCustomers
            DataGridView1.DataSource = bsCustomers
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            bsCustomers.Filter = $"CompanyName like '%{txtCompany.Text}%' AND ContactName like " &
                $"'%{txtContact.Text}%' AND Country like '%{txtCountry.Text}%'"
    
            Dim sql As String = "SELECT Identifier, CompanyName, ContactName, Country " &
                $"FROM Customers WHERE {bsCustomers.Filter.Replace("%", "*").Replace("'", """")}"
            Console.WriteLine(sql)
        End Sub
    End Class

    My resulting value from the sql variable

    SELECT Identifier, CompanyName, ContactName, Country FROM Customers WHERE CompanyName like "*an*" AND ContactName like "*ant*" AND Country like "*me*"

    Results in ms-access

    In VB.NET prior to filter

    Then

    Let's do a filter with no matches

    Another working filter


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, January 26, 2018 2:17 PM
    Moderator
  • Do you need to use the Like operator? From what you are describing it sounds like you are looking for an exact match.

    AccessBDBindingSource.Filter = String.Format("[Field1] = '{0}' AND [Field2] = '{1}' AND [Field3] = '{2}'", Me.ComboBox1.Text, Me.ComboBox2.Text, Me.ComboBox3.Text) 

    You may need to indicate what the other fields and field types are if this does not address your issue. It might help to see what the data in the other fields looks like as well.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    • Edited by Paul P Clement IV Friday, January 26, 2018 9:40 PM sp
    • Marked as answer by Patapont Saturday, January 27, 2018 6:57 PM
    Friday, January 26, 2018 9:39 PM

All replies

  • One suggestion, create the filter then create an SQL SELECT statement from the filter and try it out in MS-Access.

    Here I do just that, I load a table into a DataTable which becomes the DataSource of a BindingSource which in turn becomes the data source of a DataGridView.

    I use three TextBox controls to keep things simple and under control. You can always mix it up by using different values in the TextBox controls and run them in MS-Access query window. You should get the same results in code as you do in ms-access and if not modify the ms-access query till it works then adapt this to your code for the BindingSource filter.

    Public Class FilterForm
        Private bsCustomers As New BindingSource
        Private ops As New DataOperations
        Private Sub FilterForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            bsCustomers.DataSource = ops.GetCustomers
            DataGridView1.DataSource = bsCustomers
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            bsCustomers.Filter = $"CompanyName like '%{txtCompany.Text}%' AND ContactName like " &
                $"'%{txtContact.Text}%' AND Country like '%{txtCountry.Text}%'"
    
            Dim sql As String = "SELECT Identifier, CompanyName, ContactName, Country " &
                $"FROM Customers WHERE {bsCustomers.Filter.Replace("%", "*").Replace("'", """")}"
            Console.WriteLine(sql)
        End Sub
    End Class

    My resulting value from the sql variable

    SELECT Identifier, CompanyName, ContactName, Country FROM Customers WHERE CompanyName like "*an*" AND ContactName like "*ant*" AND Country like "*me*"

    Results in ms-access

    In VB.NET prior to filter

    Then

    Let's do a filter with no matches

    Another working filter


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, January 26, 2018 2:17 PM
    Moderator
  • The select statment works in access but not in visual studio 2010... maybe I'm doing it wrong...

    thanks for the answer!

    Friday, January 26, 2018 7:00 PM
  • The select statment works in access but not in visual studio 2010... maybe I'm doing it wrong...

    thanks for the answer!

    The filter command is the same for SQL and OleDB. However the like for the SQL transact Select it is different. Search on Internet for Paul Clement about Access Like if he is not answering soon. He knows.

    Success Cor


    Friday, January 26, 2018 7:26 PM
  • The select statment works in access but not in visual studio 2010... maybe I'm doing it wrong...

    thanks for the answer!

    Are you sure the same database used in the project is the same database used to run the SELECT statement? Also are you positive there are no spaces in the ComboBox items. 

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Friday, January 26, 2018 7:49 PM
    Moderator
  • Yes, same database. And no spaces in combobox :(
    Friday, January 26, 2018 8:53 PM
  • Do you need to use the Like operator? From what you are describing it sounds like you are looking for an exact match.

    AccessBDBindingSource.Filter = String.Format("[Field1] = '{0}' AND [Field2] = '{1}' AND [Field3] = '{2}'", Me.ComboBox1.Text, Me.ComboBox2.Text, Me.ComboBox3.Text) 

    You may need to indicate what the other fields and field types are if this does not address your issue. It might help to see what the data in the other fields looks like as well.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    • Edited by Paul P Clement IV Friday, January 26, 2018 9:40 PM sp
    • Marked as answer by Patapont Saturday, January 27, 2018 6:57 PM
    Friday, January 26, 2018 9:39 PM
  • With this code it works only if I choose the three combobox, and I would need to be able to pick just one combo, or two. 

    The problem is with the field1 data, I remember that I modified it. The fields that say 3r, before where 3a. I changed it directly in the access database, but since then it shows this problem. 

    I've tried to replace de DB with the original (same name) but I don't know why my program doesn´t recognice it... 

    For the time being I'm changing the form, I will put the field1 filter apart with its own button. That way it works. But I would lika to know why!! :)

    Thanks for your time!

    P.S. When I modified the DB I didn't change the field 5e, and that one doesn't work either...
    • Edited by Patapont Saturday, January 27, 2018 5:24 PM
    Saturday, January 27, 2018 5:21 PM
  • The following uses the Tag property of each TextBox to have the like statement within, same can be done with ComboBox controls.

    This is done in form load

    txtCompany.Tag = "CompanyName like '%Value%'"
    txtContact.Tag = "ContactName like '%Value%'"
    txtCountry.Tag = "Country like '%Value%'"

    I have my TextBox controls on a form so remove Panel1 if your ComboBoxes are on the form (remember you can replace TextBox as I'm doing with ComboBoxes)

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim result = Panel1.Controls.OfType(Of TextBox).Where(Function(tb) Not String.IsNullOrWhiteSpace(tb.Text)).ToList
    
        If result.Count > 0 Then
            Dim selectStatement = "SELECT Identifier, CompanyName, ContactName, Country FROM Customers WHERE" &
                $" {String.Join(" AND ", result.Select(Function(tb) CStr(tb.Tag).Replace("Value", tb.Text)).ToArray)}"
    
            Console.WriteLine(selectStatement)
        End If
    End Sub

    Now I press the button with all three TextBoxes populated, then two then one then none.

    SELECT Identifier, CompanyName, ContactName, Country FROM Customers WHERE Country like '%me%' AND ContactName like '%ant%' AND CompanyName like '%an%'
    SELECT Identifier, CompanyName, ContactName, Country FROM Customers WHERE Country like '%me%' AND CompanyName like '%an%'
    SELECT Identifier, CompanyName, ContactName, Country FROM Customers WHERE CompanyName like '%an%'
    All three are suitable for OleDb data provider. To test them inside of ms-access replace % with * in the like conditions.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, January 27, 2018 6:03 PM
    Moderator
  • Well, at last I've been able to bind the original data base (the one without changes) and my first code works all right... soooo... this is crazy!!

    I will have to find a way to make the changes without causing errors in the program... but that is an access issue I think.

    Thank you all for your time!! 

    Saturday, January 27, 2018 6:57 PM