How to filter gridview with multiple checkboxes?

Question

Hi!

How do I filter my DataGridView with multiple checkboxes names?

I want to filter in which region the suppliers are located. ex. RegionTable.Regionrow.
And I have 4 different regions(checkboxes text name) I want to filter with. Compatible to use multiple choices in filtering.
So. Checking 2 of the 4 regions and pushing the filter button should filter the gridview after checked checkboxes.
I have tried different methods for 4 hours and search the net but can't find any good method.

This is what i have so far: (only able to filter with one checkbox)
        If RegionEuropeCheckBox.Checked = True Then
        Try
               Me.SupplierTableAdapter.FillByRegion(Me.TraficatorSet.Supplier, RegionEuropeCheckBox.Text)
        Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
        End If

Thanks in Advance!
Roberth M

Answer 1

Roberth,

I'm not a database guy (even though I do end up working with them), and given that, what I'm about to offer here may be full of holes! I see your issue here: The multiple checkboxes, any of which may be checked or unchecked, and trying to then use that for a table adapter's fill query.

Just as a thought - and I've never tried it, so fair warning - what about this: Create a List (Of String) and each time a checkbox is changed, you modify the list so that in it are the names of the regions the user has checked. Now use LINQ to create a dataset in memory of those records (rows) matching the region name being contained IN that list.

If that part works, then the next would be to get your DataGridView (or whatever you're using) to "see" that. How about if you then create a bindingsource where it's DataSource was that dataset created by the LINQ expression and you then set the DGV's datasource to the new BindingSource. When they want to see them all again, just change the DGV's data source back to the original BindingSource that it's currently set for.

Think it over - I'll be curious to know if it works now!

---

Please call me Frank :)

Answer 2

Robert,

Is it for a DataGridView or a GridView (two complete different controls)?

Your header says GridView while your text says DataGridView

---

Success
Cor

Answer 3

Robert,

Is it for a DataGridView or a GridView (two complete different controls)?

Your header says GridView while your text says DataGridView

---

Success
Cor

Cor Lighert, It's a DataGridView.

Frank, I'll think about It and do what I can, and thank you for the tip.

Answer 4

Roberth,

I think this will do what you want. I got to thinking about it last night and I decided to try this technique on my own - it seems to work ok on my end.

I set up a form with a DataGridView based on the following DataSet/DataTable:

My thought was that if I can set something up to let a user select any combination of over 80,000 zip codes, then yours should be a walk in the park! I put two forms together:

Form1.vb

SelectZips.vb

I have the code on two webpages on my site and you can view it by clicking the links below:

Form1.vb

SelectZips.vb

I don't have any comments in any of it, so if you're unsure about something just ask, but I did like I was suggesting last night. In the second form which is shown modally, when the user clicks the "OK" button, it builds a list (of string) of the zip codes that the user chose and returns back to Form1.

There I use a LINQ expression to select the rows from the DataTable where the zip code in the DataRow is in that list. I then create a new BindingSource bound to that LINQ query, set the DataGridView's DataSource to that new BindingSource, and then finally, set the BindingNavigator's BindingSource to that newly created BindingSource also.

When the user clicks the "View All Data" button, I just reset the DGV and the BindingNavigator to be what they originally were. Here's how it looks in action:

When the program first loads, all data is shown:

When they click the button to be able to choose which zip codes they want to see, they're presented with the second form:

Once they make their selections and click "OK", it's returned back to Form1 to show only the rows which have the zip codes they chose:

I hope that helps some. :)

---

Please call me Frank :)

Answer 5

Thanks alot Frank. This looks really promising to me, and I will try it out as soon as I have time. I will reply again if I got it all working or if I met some problem.

Thank you!
Roberth M

Answer 6

Thanks alot Frank. This looks really promising to me, and I will try it out as soon as I have time. I will reply again if I got it all working or if I met some problem.

Thank you!
Roberth M

LINQ is a very power tool but it's somewhat weird to work with. There's information here:

http://msdn.microsoft.com/en-us/library/bb386976.aspx

Honestly though, reading that even now makes my head hurt - it has a very odd syntax that takes a while to get used to and I've really just begun using it lately.

At any rate, I'm glad it helped!

---

Please call me Frank :)

Answer 7

Hi again Frank!

I have tried your code, but unfortunally I failed. It all stops at when the "Filter form" pops up, and I dont get any values into the CheckedListBox. I dont get any error messages which I think is a bit wierd.

I have posted some screenshots of my code and design below.

Filter Form - http://pastebin.com/YhkXjKMU
Form1 - http://pastebin.com/rjKSpFvP

Filter Form design: Looks exactly like your example but some different names on button and form.

Form1 design:

So what I have done is that I put the "Filter" button and "View All" button there to try out your example with a new DataGridView.
Originally my plan is to build a Supplier system for a friends company where he can store and search through his supplier data. I wanted to have many CheckedListboxes in the form and when I pushed the Search button I would get the results in the DataGridView.
My DataSet looks like the following below. The query in Supplier Table is a Query based to show all data in the gridview and the selected row is displayed to the right in the textboxes and that have worked out fine.

Take a look at it if you feel you have the intrest and time :)
Thanks in advance

Regards
Roberth Magnusson

Answer 8

Roberth,

In your second form, put a breakpoint at the form's load event, specifically where it uses LINQ to create the dataset. The fact that I then use a "For/Each", if the resultant set is empty (which is my hunch that it is), then there's no error, it just doesn't do anything from that point forward.

Once the code is one line beyond the LINQ statement, hover your mouse over it and expand it. See if it shows an empty dataset. If so, then let's take it from there.

---

Please call me Frank :)

Answer 9

Frank,

Now it works, but It does not fully solve my problem.

You see, I want to filter the same way of doing a SELECT query, but with CheckBoxes.

Now I get a list of all the regions all my suppliers have (eg. just a list over all regions for every supplier.)

Answer 10

Frank,

Now it works, but It does not fully solve my problem.

You see, I want to filter the same way of doing a SELECT query, but with CheckBoxes.

Now I get a list of all the regions all my suppliers have (eg. just a list over all regions for every supplier.)

If I understand correctly, then you can do that using that list. Have a look at the LINQ query that's on Form1 when the dialog is closed (in other words, when it's returned back to Form1).

Is that not what you're wanting to do? I might be confused. ;-o

---

Please call me Frank :)

Answer 11

Frank,

I found a way to filter the way I wanted to. I'm using the following code on a Button.

http://pastebin.com/0FsQA0PR

If I combind Asia and Europe checkbox I get all Suppliers from Europe and Asia.

The problem is that when i combind for example Europe checkbox with Steel checkbox it does not build them together. So it only shows European Suppliers.
I had an idea that I had to include that "Supplier.SupID = Capacity.SupID" in the query but I dont know how really. Any ideas?

Thanks in advance.

Answer 12

Frank,

I found a way to filter the way I wanted to. I'm using the following code on a Button.

http://pastebin.com/0FsQA0PR

If I combind Asia and Europe checkbox I get all Suppliers from Europe and Asia.

The problem is that when i combind for example Europe checkbox with Steel checkbox it does not build them together. So it only shows European Suppliers.
I had an idea that I had to include that "Supplier.SupID = Capacity.SupID" in the query but I dont know how really. Any ideas?

Thanks in advance.

I think I understand if so, yes you can. In the LINQ query (on Form1, not on the second form), use an "AndAlso" after it looks at the list and then follow that by the variable name DOT field name equals (use the equal sign) whatever you want it to filter by, so essentially you're looking only to return whichever datarows it found with the country or contried they checked AND ALSO whatever this other thing is.

Experiment with that and see if it gets you what you want.

---

Please call me Frank :)

Answer 13

Frank,

I solved the problem myself with another solution. With IF/ELSE and some String Arrays.

Thank you for your help!

Roberth

Answer 14

Frank,

I solved the problem myself with another solution. With IF/ELSE and some String Arrays.

Thank you for your help!

Roberth

Glad it helped.

:)

---

Please call me Frank :)