comparing two datagridviews

[goku99]

Hello there!

I would appreciate any help with the following problem: I have two tables: one, which would be the "wish table", and another, which would be the "supplier table". Okay, first, I would select items in the first table, that hasn't values, and search the second table with the correspondent items, their values and the different suppliers. Then I would compare the sum values of the matches, and display the information like "The search of your items returns the sum "x" from the "y" supplier...

Here, it is an example of my datatables/dgview:

Table1

idProduct Item

1 Item A
1 Item B
2 Item C
2 Item D

Table 2

idProduct Item Price IdSupplier
1 Item A 10.00 1
1 Item B 20.00 1
2 Item C 30.00 1
2 Item D 40.00 1
1 Item A 20.00 3
1 Item B 30.00 3
2 Item C 40.00 3
2 Item D 50.00 3

After a query that brings the lowest prices from each idSupplier, then I make an array with the number of idSuppliers. I do a loop though this array to put the data into two Lists: one for the supplier IDs and one for the sums of the prices, then convert each List to an array and then use Array.Sort to sort both arrays using the sums as the keys.

        Dim idSupplier As List(Of Integer) = New List(Of Integer)
        Dim sumValues As List(Of Decimal) = New List(Of Decimal)
        'Dim item As List(Of String) = New List(Of String)
        Dim value As Decimal
        'Dim item1 As String
        
        For Each element In SupplierArray


            'For i = 1 To j
            value = 0
            For Each col As DataGridViewRow In dg2.Rows
                If col.Cells(3).Value = element And col.Cells(2).Value > 0.0 Then
                    value = value + CDec(col.Cells(2).Value)
                    'item1 = col.Cells(1).Value.ToString
                    'item.Add(item1)

                End If

                'If value = 0 And col.Cells(2).Value = 0 Then


                    'Exit Sub            ' It's not working

                'End If

            Next
            sumValues.Add(value)
            idSupplier.Add(element)
            

        Next

        ' Next i

            Dim idSupplierArray() = idSupplier.ToArray()
            Dim sumValuesArray() = sumValues.ToArray()

            Array.Sort(sumValuesArray, idSupplierArray)

             'Dim itemArray() = item.ToArray()
            

            'Dim itemNumero = itemArray.Length

So, I would like to return the number of Items of wich idSupplier from dg2 and compare with the Items from the dg1. Any suggestions?

[jmcilhinney]

First things first, what do you mean by "table"? Are you talking about database tables? If not, where exactly is this data coming from and is there any good reason for it not being data-bound to the grid?

[goku99]

First things first, what do you mean by "table"? Are you talking about database tables? If not, where exactly is this data coming from and is there any good reason for it not being data-bound to the grid?

Oops! I mean datatables! These are the source of two DataGridViews. But for the first datatable, there's no need to be in a datagridview.

Sorry!

[Shaggy Hiker]

That makes it quite a bit easier...except that now you have several different alternatives that you can use to solve the problem. Since you are probably familiar with SQL syntax, there are two alternatives I would suggest looking into:

1) The first is LINQ. By using LINQ you can run what looks like a query on your table. The syntax is pretty similar, though the SELECT statement ends up last rather than first. It would look something like this:

someVariable = (From m In firstTable.Rows WHERE <whatever SQL like expression makes sense> SELECT <whatever fields, sums, etc. makes sense>)

You would be querying the rows in the table pretty much as you would with SQL. The return can be a single datarow (if you use .First or .FirstOrDefault), a collection of data rows, a single value (if you do .Sum or .Count), or more complex things.

LINQ is very versatile and very powerful. It also is not the pinnacle of speed, but that's often ok. The fact that you can perform a complex search/summarization/extraction against a datatable with a single line is fairly useful.

2) An eaiser approach is just to make use of the .Select method of the datatable. The Select method takes a string that is pretty much a WHERE clause. You don't get any summary or anything from that like you can do with LINQ, though. All you get is a collection of rows that matches the Select statement. You'd then have to sum the values from the rows in that collection. This is easier to understand than LINQ, but less versatile due to the restriction on the return options.

Two other alternatives exist, though:

3) Use a Dataview. Specifically, use the default Dataview that each datatable offers up as a property. By changing the rowfilter (which is essentially the same as the .Select method in syntax), you get some set of rows that match the filter criteria. This is faster than the .Select method if you stick with just the DefaultView and want to be changing it often. Since you may be chaging the selection set several times to summarize for different items, that might be an advantage.

4) The fastest way of all, though, is actually just to write a loop and iterate through each row in the datatable summarizing things as you go. There's little reason to do this, and it could be a bit complicated for the scenario you are discussing. I mention it because, as complicated as it can get relative to a single line of LINQ, it will actually perform faster than any of the other three. That almost never matters, though, since all options are fast enough for nearly every situation.

[goku99]

That makes it quite a bit easier...except that now you have several different alternatives that you can use to solve the problem. Since you are probably familiar with SQL syntax, there are two alternatives I would suggest looking into:

1) The first is LINQ. By using LINQ you can run what looks like a query on your table. The syntax is pretty similar, though the SELECT statement ends up last rather than first. It would look something like this:

someVariable = (From m In firstTable.Rows WHERE <whatever SQL like expression makes sense> SELECT <whatever fields, sums, etc. makes sense>)

You would be querying the rows in the table pretty much as you would with SQL. The return can be a single datarow (if you use .First or .FirstOrDefault), a collection of data rows, a single value (if you do .Sum or .Count), or more complex things.

LINQ is very versatile and very powerful. It also is not the pinnacle of speed, but that's often ok. The fact that you can perform a complex search/summarization/extraction against a datatable with a single line is fairly useful.

2) An eaiser approach is just to make use of the .Select method of the datatable. The Select method takes a string that is pretty much a WHERE clause. You don't get any summary or anything from that like you can do with LINQ, though. All you get is a collection of rows that matches the Select statement. You'd then have to sum the values from the rows in that collection. This is easier to understand than LINQ, but less versatile due to the restriction on the return options.

Two other alternatives exist, though:

3) Use a Dataview. Specifically, use the default Dataview that each datatable offers up as a property. By changing the rowfilter (which is essentially the same as the .Select method in syntax), you get some set of rows that match the filter criteria. This is faster than the .Select method if you stick with just the DefaultView and want to be changing it often. Since you may be chaging the selection set several times to summarize for different items, that might be an advantage.

4) The fastest way of all, though, is actually just to write a loop and iterate through each row in the datatable summarizing things as you go. There's little reason to do this, and it could be a bit complicated for the scenario you are discussing. I mention it because, as complicated as it can get relative to a single line of LINQ, it will actually perform faster than any of the other three. That almost never matters, though, since all options are fast enough for nearly every situation.

Hi! Thanks! I still haven't tried yours suggestions! But I am concerned that my code already gives me arrays with dgv2's data that I want, as sum of each supplier and their items. The problem is that for the items array, I have a list of all items from the suppliers and I wonder how to discriminate them and compare them with the dgv1 items.
To illustrate, from my example above, my items array would result in ({Item A, Item B, ItemC}; {Item A, Item B, Item C})

For Each element In SupplierArray


           
            value = 0
            For Each col As DataGridViewRow In dg2.Rows
                If col.Cells(3).Value = element And col.Cells(2).Value > 0.0 Then
                    value = value + CDec(col.Cells(2).Value)
                    item1 = col.Cells(1).Value.ToString
                    item.Add(item1)

                End If

                
            Next
            sumValues.Add(value)
            idSupplier.Add(element)
            

        Next

        

            Dim idSupplierArray() = idSupplier.ToArray()
            Dim sumValuesArray() = sumValues.ToArray()

            Array.Sort(sumValuesArray, idSupplierArray)

             Dim itemArray() = item.ToArray()

[goku99]

Hello shaggy! I have considered the 1-3 approaches and the problem is that I ended up with a big list, and not with a list that intersect the second column of my datagridview1 in datagridview2, for each different elements in the third column of my datagridview2. So, when I loop though the elements in the third column of my datagridview2, selecting or filtering, I would do a find to add the matches to a list, but the next element (from the loop) adds its matched items to the same list. So, there's any way to have collections of matches from datagridview1 in datagridview2 for the different elements of a column in datagridview2?

Thanks!

[goku99]

Hello there! I've tried the following code and I need help with LinQ

For Each row As DataGridViewRow In dgv1.Rows
If Not row.IsNewRow Then
Dim row1 = row
Dim myTableSummary = (From myRow As DataGridViewRow In dgv2.Rows.Cast(Of DataGridViewRow)() Where Not myRow.IsNewRow AndAlso myRow.Cells(1).Value.ToString.Equals(row1.Cells(1).Value.ToString) Select ID1 = myRow.Cells(3).Value, _
ItemValue = myRow.Cells(1).Value Group By ID1 Into G = Group, match = Count())
Dim myArrayOfAnonymousTypes() = myTableSummary.ToArray
'Array.Sort(myArrayOfAnonymousTypes)

For Each myElement In myArrayOfAnonymousTypes
MsgBox(myElement.ID1.ToString & " - " & myElement.match.ToString)

Next

End If
Next

:

Is there any function that adds elements to the variable mytablesummary?

[jmcilhinney]

Hello there! I've tried the following code and I need help with LinQ :

Is there any function that adds elements to the variable mytablesummary?

No. 'myTableSummary' is an IEnumerable, which can't be added to anyway. You could call ToList on it to create a List, which can be added to. The thing is, that is supposed to be the results of a query against the grid. Under what circumstances would you want to add to it?

[goku99]

No. 'myTableSummary' is an IEnumerable, which can't be added to anyway. You could call ToList on it to create a List, which can be added to. The thing is, that is supposed to be the results of a query against the grid. Under what circumstances would you want to add to it?

Thanks jmcilhinney! I want to add the query results of ...Where Not myRow.IsNewRow AndAlso myRow.Cells(1).Value.ToString.Equals(row1.Cells(1).Value.ToString)... , and it should be created for each "ID1" value. To list an IEnumerable should I create a function?

Thanks anyway!

[jmcilhinney]

If you want to join the results of the query with another list then you can call Concat on one of them and pass the other. If you want to arbitrarily add and/or remove items in the query results then you should do as I've already said and call ToList.

That said, your query is creating an anonymous type, so it's not really made for adding new items. If you want to be able to create instances of that type yourself then, while you could use the anonymous type, it would be more correct to define a type yourself.

[goku99]

Thanks! It works, but since I can't "filter" the results by the third column, I only have got a list of the selected items "ID1":

Dim myTableSummary = (From myRow As DataGridViewRow In dgv2.Rows.Cast(Of DataGridViewRow)() Where Not myRow.IsNewRow AndAlso myRow.Cells(1).Value.ToString.Equals(row1.Cells(1).Value.ToString) Select ID1 = myRow.Cells(1).Value _
                        ).ToList()
                Dim myArrayOfAnonymousTypes() = myTableSummary.ToArray

In fact, my ideia would be to put the desired items of my dgv1 into an array, use .rowfilter in dgv2, put the items for each filter into an dinamic array, and use a comparer method. I have checked the Except method, as follows:

Dim exceptItems = item1Array.Except(item2array(2))

When I try to cast it, I get an error message: "System.linq.Enumerable+<ExceptIterator>d_99'1[System.Object]" . Why this message? Thanks!

[jmcilhinney]

Thanks! It works, but since I can't "filter" the results by the third column, I only have got a list of the selected items "ID1":

Dim myTableSummary = (From myRow As DataGridViewRow In dgv2.Rows.Cast(Of DataGridViewRow)() Where Not myRow.IsNewRow AndAlso myRow.Cells(1).Value.ToString.Equals(row1.Cells(1).Value.ToString) Select ID1 = myRow.Cells(1).Value _
                        ).ToList()
                Dim myArrayOfAnonymousTypes() = myTableSummary.ToArray

In fact, my ideia would be to put the desired items of my dgv1 into an array, use .rowfilter in dgv2, put the items for each filter into an dinamic array, and use a comparer method. I have checked the Except method, as follows:

Dim exceptItems = item1Array.Except(item2array(2))

When I try to cast it, I get an error message: "System.linq.Enumerable+<ExceptIterator>d_99'1[System.Object]" . Why this message? Thanks!

Sure you can filter by the third column. If you want to filter then you use the Where clause, just like you would in a SQL query. Remember that a LINQ query is really just a contraction of a For Each loop that takes one list as input and creates another as output. The Where clause is just like an If block that you would include inside the loop to decide whether an item in the input list should be used to create the output. If you want more data in the output then use the Select clause, again, just as you owuld in a SQL query. If you want your anonymous type to have additional properties then add them in the Select clause.

As for using Except, firstly, that's not an error message. It might be part of one, but it's certainly not the whole thing. Regardless, Except expects another list of items to be excluded, not a single item. If you only have a single item then put it into an array and use that.

[goku99]

Thanks! Sorry, but for the except part, I am using arrays: item1Array() = {Item A, Item B, Item C, Item D}; item2Array(2) = {Item A}. So the results would be {Item B, Item C, Item D}. I think that its a problem with the variable and the casting, since I am using this code:

Dim exceptItems = item1Array.Except(item2array(2))
MsgBox(exceptItems.ToString)

I have tried another way:

Dim onlyInFirstSet As IEnumerable(Of String) = item1Array.Except(item2array(2))

            Dim output As New System.Text.StringBuilder
            For Each str As String In onlyInFirstSet
               output.AppendLine(str)
            Next
            MsgBox(output.ToString())

But now I get the err.number = 13.

[goku99]

Sure you can filter by the third column. If you want to filter then you use the Where clause, just like you would in a SQL query. Remember that a LINQ query is really just a contraction of a For Each loop that takes one list as input and creates another as output. The Where clause is just like an If block that you would include inside the loop to decide whether an item in the input list should be used to create the output. If you want more data in the output then use the Select clause, again, just as you owuld in a SQL query. If you want your anonymous type to have additional properties then add them in the Select clause.

As for using Except, firstly, that's not an error message. It might be part of one, but it's certainly not the whole thing. Regardless, Except expects another list of items to be excluded, not a single item. If you only have a single item then put it into an array and use that.

Hi! I think I cant use .except since my arrays are string objects. Thanks!