Results 1 to 6 of 6

Thread: Bindingsource filter multiple columns

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2003
    Posts
    16

    Bindingsource filter multiple columns

    Hi,

    I'm currently struggling with using a single command button which will filter 3 different combo inputs via a bindingsource filter. Sometimes the combo's will be blank other times nots, and I have a few more different combo's which I need to implement.

    If I use the first line you can see that I have manually entered the filtered data for each column, when I run this code it works without any issues. When running the 2nd line it gives me an error, eventually I will need to add the remaining combo boxes so this filter list will grow.

    Working
    Me.BindingSource1.Filter = String.Format("Year ='Year 2001' AND Gender ='M' And Ethnicity ='British' ")

    Not Working
    Me.BindingSource1.Filter = "Year =" & "'" & ComboBox7.Text & "'" And "Gender = & '" & ComboBox3.Text & "' " And "Ethnicity= & '" & ComboBox2.Text & "' "

    Error returned when running code whereby I have specified to look directly in the the combo box text;
    Conversion from string "Year ='Year 2001'" to type 'Long' is not valid.'

    Can someone advise please ?
    Last edited by Serial; Mar 2nd, 2022 at 10:22 AM.

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Bindingsource filter multiple columns

    You've over-complicated the second example by needlessly separating parts of the String that are literal. You've missed the fact that the And doesn't end up being part of the string but is rather been treated as a conditional. And you've included an & inside of quotes, treating that as literal as well. You also don't seem to have any code addressing the ethnicity piece of the filter. We also have no idea how the text in your comboboxes are formatted, so we can't know if simply inserting their text value matches the way you've formatted the "working" filter.

    All that being said, and me not fixing the bulk of that, this should follow your existing format and should work. Or, at the very least, it should be less broken.

    Code:
    Me.BindingSource1.Filter = "Year =" & "'" & ComboBox7.Text & "'" & " AND " & "Gender = '" & ComboBox3.Text & "' "

  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Bindingsource filter multiple columns

    Take a look at this example:
    Code:
    Dim filters = New Dictionary(Of String, String)() From {
        { "Year", ComboBox7.Text },
        { "Gender", ComboBox3.Text },
        { "Ethnicity", ComboBox2.Text }
    }
    
    Dim filter = String.Join(" AND ", filters.Select(Function(kvp) String.Format("{0}='{1}'", kvp.Key, kvp.Value)))
    Fiddle: https://dotnetfiddle.net/159YFB

    Doing it like this, you can add/remove values to the dictionary pretty easily.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Bindingsource filter multiple columns

    There's so much wrong here. Let's take it step by step.

    Firstly, there's zero point using String.Format here:
    vb.net Code:
    1. Me.BindingSource1.Filter = String.Format("Year ='Year 2001' AND Gender ='M' And Ethnicity ='British' ")
    You're not doing any formatting, so you could have just done this:
    vb.net Code:
    1. Me.BindingSource1.Filter = "Year ='Year 2001' AND Gender ='M' And Ethnicity ='British' "
    When you wanted to insert the values, then you could have done some actual formatting, but you chose to remove the method call then and use concatenation instead. Bad. You should have kept the String.Format and actually used it:
    vb.net Code:
    1. Me.BindingSource1.Filter = String.Format("Year ='{0}' AND Gender ='{1}' And Ethnicity ='{2}' ", ComboBox7.Text, ComboBox3.Text, ComboBox2.Text)
    Even better, use string interpolation:
    vb.net Code:
    1. Me.BindingSource1.Filter = $"Year ='{ComboBox7.Text}' AND Gender ='{ComboBox3.Text}' And Ethnicity ='{ComboBox2.Text}' "
    As you can see, both of those are far more readable than what you wrote and thus will make it less likely that you make a mistake because you can't read what you wrote properly.

    Given that VS highlights keywords with different colours, you really should have been able to see what the problem was anyway. Here are the operators in your VB code:
    Code:
    Me.BindingSource1.Filter = "Year =" & "'" & ComboBox7.Text & "'" And "Gender = & '" & ComboBox3.Text & "' " And "Ethnicity= & '" & ComboBox2.Text & "' "
    See the problem? You've got And operators outside the String that should be inside and & operators inside the String that should be outside. You've written so much garbage that you can't even see it for the mess. Part of the reason it's so messy is that you are concatenating two literals together in various places. That's ludicrous! Would you do this:
    vb.net Code:
    1. Dim str = "Hello" & " " & "World"
    I would certainly hope not, so why would you do this:
    Code:
    Me.BindingSource1.Filter = "Year =" & "'" & ComboBox7.Text & "'" And "Gender = & '" & ComboBox3.Text & "' " And "Ethnicity= & '" & ComboBox2.Text & "' "
    If you were going to use straight concatenation (which you shouldn't) then it should have looked like this:
    vb.net Code:
    1. Me.BindingSource1.Filter = "Year ='" & ComboBox7.Text & "' AND Gender ='" & ComboBox3.Text & "' And Ethnicity ='" & ComboBox2.Text & "' "
    Still harder to read because of all the quotes but still not as hard as what you wrote.

    Finally, on an unrelated note, the fact that you have controls with those names is very bad. DO NOT accept the default names for controls and components that you add in the designer except for quick and dirty tests and demos. You should not have seven ComboBox controls that are differentiated only by a meaningless number. Given them proper, meaningful names, e.g. yesrComboBox, genderComboBox and ethnicityComboBox. That mnakes the code largely self-documenting and it also makes it less likely that you use the wrong control by mistake. You ought to do the same thing with the BindingSource. It's less important there if there is only one, but it still makes the code more self-explanatory if you can see what the thing is for when you see its Filter being set.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jan 2003
    Posts
    16

    Re: Bindingsource filter multiple columns

    Hi all,

    Thank you very much for responding and assisting with detailed information in how and which way would be best going forward.

    One other query which I have is that when I run the code it doesn't return data for a single combo box which I have selected, I have to ensure that all 3 combo boxes have entries before it returns a valid filtered search. Does this have something to do with the 'AND', if it does what do I need to do.

    For example:
    If I select the year only, and leave the gender and ethnicity combo boxes blank, it returns nothing. If I select and have all 3 combo boxes with entries, it then return the correct totals.
    If I select the gender and ethnicity only, and leave the year combo box blank, it returns nothing. If I select and have all 3 combo boxes with entries, it then return the correct totals.

    Is there a way or how can I ensure that the code works by selecting any or multiple combo boxes ?


    Many thanks.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Bindingsource filter multiple columns

    Instead of having a simple condition for each field like this:
    Code:
    Year ='" & ComboBox7.Text & "' AND ...
    ...you can extend them to also allow a blank value, eg:
    Code:
    (Year ='" & ComboBox7.Text & "' Or '" & ComboBox7.Text & "' = ''") AND ...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width