Avatar of Mark Bakelaar
Mark BakelaarFlag for Norway asked on

Use of rowfilter with option "all"

Hi Experts,
I have two comboboxes in which the user can select items used for a rowfilter. Both comboboxes have the option "all" in it. There are 4 options (all - all, all - specific, specific - all and specific - specific). I attached the code that works for the rowfilter.

However, I need to add to 2 additional comboboxes. The number of options now increases to 16, thus a lot of code with if statements. It there a way to make a single statement that recognizes the "all" option and does not use a filter in this case.

Hope somebody can prevent building a monstercode... thanks in advance, MB
Dim BookingView As DataView = Me.DataGridView1.DataSource
 
        If Me.ComboBox1.Text = "all" And Me.ComboBox2.Text = "all" Then
            BookingView.RowFilter = ""
        ElseIf Me.ComboBox1.Text = "all" Then
            BookingView.RowFilter = "Loadport LIKE '%" & Me.ComboBox2.Text & "%'"
        ElseIf Me.ComboBox2.Text = "all" Then
            BookingView.RowFilter = "Voyage LIKE '%" & Me.ComboBox1.Text & "%'"
        Else
            BookingView.RowFilter = "Voyage LIKE '%" & Me.ComboBox1.Text & "%' AND Loadport LIKE '%" & Me.ComboBox2.Text & "%'"
        End If

Open in new window

.NET ProgrammingVisual Basic.NET

Avatar of undefined
Last Comment
Mark Bakelaar

8/22/2022 - Mon
Sancler

First, you can simplify things by recognising that "LIKE '%" & <texttofind> & "%'" is equivalent to "" (i.e. no row filter) when <texttofind> = "".  So, provided that when the choice is "all", you pass "" rather than ComboBox.Text into the filter expression, you do not need a "special case"

            BookingView.RowFilter = ""

separate from

            BookingView.RowFilter = "Loadport LIKE '%" & Me.ComboBox2.Text & "%'"

And, and this is important for the third point below.  Two, or more, lots of "LIKE '%%'" joined by AND still resolve, in effect, to "".

Second, you can facilitate that by setting up the binding for your comboboxes so that you can set both a DisplayMember and a ValueMember.  So you could have a two column datatable, with the same value in both columns for all entries except "all", for which you would have "" in the second column.  Then you could use ComboBox.SelectedValue to pass into the filter expression.

Third, you could nest the filtering.  Start off with one filter string in the form "<firstfield> LIKE '%" & Me.ComboBox1.SelectedValue & "%' AND <secondfield> " LIKE '%" & Me.ComboBox2.SelectedValue & "%' AND ... <lastfield> LIKE '%" & Me.ComboBoxN.SelectedValue & "%'".  When the various combos' selectedvalues change, re-fill it.  You will need to handle the mechanics carefully so that you do not get yourself into an infinite loop.  As I recall, you might be adjusting the contents of lower level comboboxes as higher level ones change.  So you may need to use some flags to stop procedures firing at inappropriate times.

But I think, at least in outline, that is the approach I would take.

Roger
ASKER
Mark Bakelaar

Hi Roger,

The mechanism is clear and I have it working for items that are not "all". For my understanding:
If a selection is all, I can not use this in the filter string, I first need to convert it to "". After this I can make one filter string with some "" and some me.combobox.text.

Do you suggest to first make some statements that convert selections showing "all" to "" and then put everything in one filter string?

Regards, Mark
ASKER CERTIFIED SOLUTION
Sancler

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Mark Bakelaar

Thanks, then it is clear and even better, I got it to work...MB
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes