-
Mar 2nd, 2022, 09:50 AM
#1
Thread Starter
Junior Member
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.
-
Mar 2nd, 2022, 10:03 AM
#2
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 & "' "
-
Mar 2nd, 2022, 11:11 AM
#3
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.
-
Mar 3rd, 2022, 12:07 AM
#4
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:
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:
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:
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:
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:
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:
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.
-
Mar 3rd, 2022, 06:04 AM
#5
Thread Starter
Junior Member
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.
-
Mar 3rd, 2022, 07:18 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|