Help with DataGridView Filtering

Question

Hi all,

I was hoping someone could give me some advice on DataGridView filtering.

I have this data bound to a combobox:
Product        Index
Prod1         1,2
Prod2         7,8,9
Prod3         10,11

Actual Table structure is:
  index    Product
    1      prod1
    2      prod1
    7      prod2
    8      prod2
    9      prod2
    10     prod2
    11     prod3

(Product is the Display Member, Index is the ValueMember)

On the combobox's SelectedIndexChanged event, i'm filtering the DataGridView:

DGVCustomers.DataSource.DefaultView.RowFilter = "indexes like '%" + MyDropDown.SelectedValue + "%'"

If MyDropDown.SelectedValue is "1,2" then any record without either number will not be shown , in addition "7,8,9" will only show customers with that string... etc etc.

How can I filter the DGV correctly with the CSV style data? (It's not my data unfortunately!)

Thanks

Answer 1

Do you want to filter on the basis of index or Product?

Also, how many columns are there in the DGV. You can also use dataview to filter the records.

---

Amit Govil | Email

"Weeks of coding can save you hours of planning"

Answer 2

The customer table is in the following format, this populates the DGV directly (Lots of columns but indexes is the only one that matches up against products):

  ID    customer_name    indexes
1667071    Customer1     1,2
1681104    Customer2    1,2,3,4
20733    Customer3    1,2,0,0,0,0
20779    Customer4    1,2,3
20801    Customer5    2,3,0,0,0
20816    Customer6    11,2,7,8

I need to filter the indexes column of the DGV with the selected combobox - I can't see a way to do it!

Answer 3

How can you bind more indexes to one item of comboBox? Where is the point?

I dont actully understand what you would like to do here...

One important thing to add:

you actually dont need to set and ValueMember to comboBox, since there are multiple values of index for products.

ONE QUESTION:

do you wanna filter by index, or by product name? This is very important issue.

In my opinion, you can filter by product name (and leave indexes - no need to deal with them here).

---

Mitja

Answer 4

The reason I bind them is to stop duplicate entries in the combobox, if I didn't there would be an entry for each line (I just want distinct products). I also need to know all of the related indexes for each product, so I add them as a csv to a 2nd column.

I want to filter by product name, but a customer record can have multiple products which is only defined by the indexes.

Here is the database structure I'm working with:

http://www.xlightftpd.com/help/odbc_struct.html

Answer 5

Try code below:

    Private Sub MyDropDown_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyDropDown.SelectedIndexChanged
        Dim dtCust, dtProducts As DataTable
        Dim strProdIndexes As String = ""

        dtProducts = MyDropDown.DataSource
        For Each dr As DataRow In dtProducts.Select("Product = '" & MyDropDown.SelectedItem & "'")
            strProdIndexes = strProdIndexes & dr("index").ToString() & ","
        Next
        strProdIndexes = strProdIndexes.Substring(0, strProdIndexes.Length - 1) 'to remove the last ,

        dtCust = DGVCustomers.DataSource

        Dim dvCust As New DataView(dtCust, "Indexes IN (" & strProdIndexes & ")", "CustomerName", DataViewRowState.CurrentRows)
        DGVCustomers.DataSource = dvCust

    End Sub

---

Amit Govil | Email

"Weeks of coding can save you hours of planning"

Answer 6

hmm, hard task, especially because of multiple indexes. Thats the code looks damn huge. There is almost no there (shorter) way to get it work.

I used almost 2 hour to get this code working.

And it works pretty damn good.

Check it out:

Public Partial Class Form1
	Inherits Form
	Private products As DataTable
	Private customers As DataTable
	Private temp As DataTable
	Public Sub New()
		InitializeComponent()
		'creating columns:
		products = New DataTable("Products")
		products.Columns.AddRange(New DataColumn() {New DataColumn("Index", GetType(Integer)), New DataColumn("ProductName", GetType(String))})
		customers = New DataTable("Customers")
		customers.Columns.AddRange(New DataColumn() {New DataColumn("ID", GetType(Integer)), New DataColumn("CustomerName", GetType(String)), New DataColumn("Indexes", GetType(List(Of Integer)))})
		temp = New DataTable("tempData")
		temp.Columns.AddRange(New DataColumn() {New DataColumn("ID", GetType(Integer)), New DataColumn("CustomerName", GetType(String))})

		'add some example data:
		products.Rows.Add(1, "Product 1")
		products.Rows.Add(2, "Product 1")
		products.Rows.Add(7, "Product 2")
		products.Rows.Add(8, "Product 2")
		products.Rows.Add(9, "Product 2")
		products.Rows.Add(10, "Product 3")
		products.Rows.Add(11, "Product 3")

		customers.Rows.Add(1667071, "Customer 1", New List(Of Integer)() From { _
			1, _
			2 _
		})
		customers.Rows.Add(1681104, "Customer 2", New List(Of Integer)() From { _
			1, _
			2, _
			3, _
			4 _
		})
		customers.Rows.Add(20733, "Customer 3", New List(Of Integer)() From { _
			1, _
			2, _
			0, _
			0, _
			0, _
			0 _
		})
		customers.Rows.Add(20779, "Customer 4", New List(Of Integer)() From { _
			1, _
			2, _
			3 _
		})
		customers.Rows.Add(20801, "Customer 5", New List(Of Integer)() From { _
			2, _
			3, _
			0, _
			0, _
			0 _
		})
		customers.Rows.Add(20816, "Customer 6", New List(Of Integer)() From { _
			11, _
			2, _
			7, _
			8 _
		})

		'so now get different products and show them in comboBox:
		'List<string> query_products = products.AsEnumerable().Select(s => (string)s["ProductName"]).Distinct().ToList();
		comboBox1.DataSource = products.AsEnumerable().[Select](Function(s) DirectCast(s("ProductName"), String)).Distinct().ToList()
		comboBox1.DisplayMember = "ProductName"

		dataGridView1.DataSource = customers
	End Sub

	Private Sub comboBox1_SelectedIndexChanged(sender As Object, e As EventArgs)
		'get ids of the product:
		Dim query_productIDs As List(Of Integer) = products.AsEnumerable().Where(Function(w) DirectCast(w("ProductName"), String) = comboBox1.SelectedItem.ToString()).[Select](Function(s) CInt(s("Index"))).ToList()

		Dim rows As New List(Of DataRow)()
		If dataGridView1.Columns.Count > 0 Then
			For Each row As DataRow In customers.Rows
				Dim _indexes As List(Of Integer) = DirectCast(row("Indexes"), List(Of Integer))
				For Each _index As Integer In _indexes
					For Each productID As Integer In query_productIDs
						If _index = productID Then
							rows.Add(row)
						End If
					Next
				Next
			Next
			temp.Rows.Clear()
			For Each row As DataRow In rows
				temp.ImportRow(row)
			Next
			'var distintRows = temp.AsEnumerable().Distinct(DataRowComparer.Default);
			Dim view As New DataView(temp)
			temp = view.ToTable(True, "ID", "CustomerName")
			dataGridView1.DataSource = temp.DefaultView
		End If
	End Sub
End Class

NOTE: Please try only this code, with no other. And put on the form onyl datagridview and a combobox. To test the code.

Then let me know what do you think.

bye

---

Mitja

Answer 7

Hi Mitja,

Thanks for the hard work, it works great except for one small problem.
My data is comma seperated (Index column), so using my actual data causes a failure:

Unable to cast object of type 'System.String' to type 'System.Collections.Generic.List`1[System.Int32]'.

On this line:  Dim _indexes As List(Of Integer) = DirectCast(row("virtual_path_indexes"), List(Of Integer))

Thanks.

Answer 8

I've converted the array to int, although my code is not the best... Is there a better way to convert the array? It seems really inefficient to loop each value:

Dim MyArray() As String = row("indexes").Split(",")

Dim iarInfo As Integer() = New Integer(MyArray.Length - 1) {}
     
     For i As Integer = 0 To MyArray.Length - 1
           iarInfo(i) = Convert.ToInt32(MyArray(i))
     Next

Dim MyList As List(Of Integer) = iarInfo.ToList()
Dim _indexes As List(Of Integer) = MyList

Answer 9

I've converted the array to int, although my code is not the best... Is there a better way to convert the array? It seems really inefficient to loop each value:

Dim MyArray() As String = row("indexes").Split(",")

Dim iarInfo As Integer() = New Integer(MyArray.Length - 1) {}
     
     For i As Integer = 0 To MyArray.Length - 1
           iarInfo(i) = Convert.ToInt32(MyArray(i))
     Next

Dim MyList As List(Of Integer) = iarInfo.ToList()
Dim _indexes As List(Of Integer) = MyList

If you don't want to cause more trouble than necessary, use Option Strict On. You've switched off compiler checks that could prevent obvious faults. You can now even write

      Dim MyArray() As String = row("indexes").Solit(",")

and the compiler would accept it even though it knows that an Object does not have a member called "Solit".

Regarding your question: You're code is ok. Another possible version is:

      Dim Indexes = (From Index In row("indexes").ToString.Split(","c) _
                    Select Integer.Parse(Index)).ToList

---

Armin

Answer 10

Yep, thx Armin for helping me.. I wasnt around.

---

Mitja