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

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

Mark Bakelaar

ASKER

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Bakelaar

ASKER

Thanks, then it is clear and even better, I got it to work...MB

Use of rowfilter with option &quot;all&quot;

Use of rowfilter with option "all"