how to link one datagridview to another datagridview to give a summary

Question

Hi All,

How can i link one datagridview (details) to another datagridview (summary) to give the summary of first datagridview (details).

find the attached image for clarity.

In the above image top datagridview shows the summary of bottom datagridview.

Person is unique in summary.

Count is total shots count of Person.

Quantity = shots * quantity

let me know in case if you need any clarification.

---

PBL (Visual Studio 2010 Professional, Win 7 64 bit Ultimate)

Answer 1

Each grid should have it's own datasource, correct?  Then have your SQL logic for each grid on the database, fill a DataSet with each DataTable, set each DataGridView with it's appropriate datasource.

---

James Crandall ~ http://javitechnologies.com Spatial Database Solutions

Answer 2

I dont want to have 2 different datasourses.

Summary grid should be effected if i change the data in the detail grid.

For suppose if i change the record of Shot 18 from B to A

then the summary grid should be like below for A and B records

Person     Count     Quantity

A                 6            15

B                 5            18

C                 7            19

TOTAL        18            52

---

PBL (Visual Studio 2010 Professional, Win 7 64 bit Ultimate)

Answer 3

So what is/are the datasource(s) of each DataGridView?  or what do you want the sources to be exactly? 

---

James Crandall ~ http://javitechnologies.com Spatial Database Solutions

Answer 4

Hello buddy, How are you.

                      Read the reply from James he is right probably if you are using different datasources(table,collections etc). Maintain them as per your need and change the query or if you are manually creating them then create logic for creating these sources

You can implement your logic on CellEndEdit event of datagridview. here you can make checks for specific column so that it will execute ur logic for specific columns only.

hope it will help you.

---

Want to add MVP with my name.

Answer 5

Hello, even thou you only want one data source here is an example using two DataTables.

d1 DataTable is the main table, dt2 is a temp DataTable to get a list of persons in dt1 while dt3 is the summary. Note in dt1 the last DataColumn uses an Expression to calc Shot * Count. If you were obtaining data into dt1 via a backend database you would tack on the expression column after you loaded the data into a DataTable.

To update summary DataTable we hook into the RowChanged event of this table. In this example I do not allow a new person to be added after the fact although this could be done via tableNewRow event of the summary DataTable.

Requires two DataGridView controls.

VS2010 code which surely could be optimized if so desired.

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt1 As New DataTable With {.TableName = "ShotsTable"}
        Dim Col1 As New DataColumn With {.ColumnName = "Person", .DataType = GetType(System.String)}
        Dim Col2 As New DataColumn With {.ColumnName = "Shot", .DataType = GetType(System.Int32)}
        Dim Col3 As New DataColumn With {.ColumnName = "Count", .DataType = GetType(System.Int32)}
        Dim Col4 As New DataColumn With
            {
                .ColumnName = "PersonTotal",
                .DataType = GetType(System.Int32),
                .Expression = "Shot * Count"
            }
        dt1.Columns.AddRange(New DataColumn() {Col1, Col2, Col3, Col4})
        dt1.Rows.Add(New Object() {"A", 1, 1})
        dt1.Rows.Add(New Object() {"A", 12, 2})
        dt1.Rows.Add(New Object() {"B", 2, 3})
        dt1.Rows.Add(New Object() {"A", 4, 4})
        dt1.Rows.Add(New Object() {"B", 5, 3})
        Dim dt2 = New DataView(dt1.DefaultView.ToTable("T1", True, "Person")).Table
        Dim dt3 As New DataTable With {.TableName = "Summary"}
        dt3.Columns.Add(New DataColumn With {.ColumnName = "Person", .DataType = GetType(System.String)})
        dt3.Columns.Add(New DataColumn With {.ColumnName = "Count", .DataType = GetType(System.Int32)})
        dt3.Columns.Add(New DataColumn With {.ColumnName = "Quantity", .DataType = GetType(System.Int32)})
        dt2.Rows.Add(New Object() {"Total"})
        For Each row As DataRow In dt2.Rows
            If row(0).ToString <> "Total" Then
                Dim PersonID = row(0).ToString
                Dim Count =
                    (
                        From T In dt1.AsEnumerable
                        Where T.Field(Of String)("Person") = PersonID
                        Select T.Field(Of Int32)("Count")
                    ).Sum
                Dim Quantity =
                    (
                        From T In dt1.AsEnumerable
                        Where T.Field(Of String)("Person") = PersonID
                        Select T.Field(Of Int32)("PersonTotal")
                    ).Sum
                dt3.Rows.Add(New Object() {PersonID, Count, Quantity})
            End If
        Next
        Dim TotalCount = (From T In dt3.AsEnumerable Select T.Field(Of Int32)("Count")).Sum
        Dim TotalQuantity = (From T In dt3.AsEnumerable Select T.Field(Of Int32)("Quantity")).Sum
        dt3.Rows.Add(New Object() {"TOTAL", TotalCount, TotalQuantity})
        DataGridView1.DataSource = dt1
        DataGridView1.Columns(0).ReadOnly = True
        DataGridView2.DataSource = dt3
        AddHandler dt1.RowChanged, AddressOf MonitorTable_RowChanged
        AddHandler dt1.TableNewRow, AddressOf MonitorTable_TableNewRow
    End Sub
    Private Sub MonitorTable_RowChanged(ByVal sender As Object, ByVal e As System.Data.DataRowChangeEventArgs)
        If e.Row.RowState <> DataRowState.Detached Then
            Dim Shot As Int32
            Dim Count As Int32
            If Integer.TryParse(e.Row("Shot").ToString, Shot) AndAlso Integer.TryParse(e.Row("Count").ToString, Count) Then
                Dim PersonID As String = e.Row("Person").ToString
                Dim dt = CType(DataGridView1.DataSource, DataTable)
                Dim Count1 =
                    (
                        From T In dt.AsEnumerable
                        Where T.Field(Of String)("Person") = PersonID
                        Select T.Field(Of Int32)("Count")
                    ).Sum
                Dim Quantity =
                    (
                        From T In dt.AsEnumerable
                        Where T.Field(Of String)("Person") = PersonID
                        Select T.Field(Of Int32)("PersonTotal")
                    ).Sum
                Dim CurrentRow = CType(DataGridView2.DataSource, DataTable).Select("Person = '" & PersonID & "'")(0)
                CurrentRow(1) = Count1
                CurrentRow(2) = Quantity
                CType(DataGridView2.DataSource, DataTable).AcceptChanges()
            End If
        End If
    End Sub
    Private Sub MonitorTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs)
        MessageBox.Show("Not programmed for this")
    End Sub
End Class

---

KSG

Answer 6

Hello Kevin thanks for the code can you please edit it and paste the VB.NET converted code as it would be much helpful for people who know only VB.

By the way below is the converter utility 

http://www.developerfusion.com/tools/convert/csharp-to-vb/

---

Want to add MVP with my name.

Hello, the code I posted is VB.NET for VS2010 as the OP indicated VS2010 in their signature.

---

KSG

Answer 7

here's an example that populates a datagridview with some test data, then uses LINQ to group by person + calculate count + sum quantity:

Public Class Form1

    Dim r As New Random

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim identifiers() As String = {"A", "B", "C"}
        Dim usedShots As New List(Of Integer)

        For x As Integer = 1 To 25
            Dim s As Integer = r.Next(1, 26)
            While usedShots.Contains(s)
                s = r.Next(1, 26)
            End While
            
usedShots.Add(s)
 
            DataGridView1.Rows.Add(s, identifiers(r.Next(0, 3)), r.Next(1, 21))

        Next

        Dim groups = From row As DataGridViewRow In DataGridView1.Rows.Cast(Of DataGridViewRow).Take(DataGridView1.Rows.Count - 1) _
                     Group row By id = row.Cells(1).Value Into Group _
                     Select New With { _
                     .p = id, _
                     .c = Group.Count, _
                     .q = Group.Sum(Function(r) CInt(r.Cells(2).Value))}

        For Each g In groups.OrderBy(Function(r) r.p)
            DataGridView2.Rows.Add(g.p, g.c, g.q)
        Next

    End Sub

End Class

---

thanks for any help