none
Grouping DatagridView Columns and add records to other DatagridView RRS feed

  • Question

  • Lets say we have a datagridview that is looking similar to this -

    I want that when I click the button, my program should look for uinique TaxRate and sum up the records so that I could get the following result -

    Can someone help ?


    Sunday, December 24, 2017 12:19 PM

Answers

  • Hello,

    Well I always recommend to never load a DataGridView via rows.add other than for a view only function but if there are calculations or groupings to be done load a dataGridView from a DataTable or List(Of T).

    In this case I'm loading the top DataGridView with a DataTable (A DataTable as you can see does not need to work from a database table). Then in Button1 click event I do a Lambda statement with a GroupBy to get sums per group.

    Note in this case the top DataGridView obtained it's column names from the DataTable although I could had overridden the column names also. The bottom DataGridView I created columns in the IDE. I didn't do any formatting, left that to you as that is easy to do.

    Public Class Form1
        ''' <summary>
        ''' Rather than loading via DataGridView.Rows.Add a DataTable is used.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable With {.TableName = "MyTable"}
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "TaxRate", .DataType = GetType(Decimal)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Value", .DataType = GetType(Decimal)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "TaxAmount", .DataType = GetType(Decimal)})
    
            dt.Rows.Add(New Object() {5D, 1000D, 50D})
            dt.Rows.Add(New Object() {5D, 2000D, 100D})
            dt.Rows.Add(New Object() {10D, 1000D, 100D})
            dt.Rows.Add(New Object() {10D, 2000D, 200D})
            dt.Rows.Add(New Object() {15D, 1000D, 150D})
            dt.Rows.Add(New Object() {15D, 2000D, 300D})
    
            DataGridView1.DataSource = dt
    
        End Sub
        ''' <summary>
        ''' Here we first cast the top DataGridView DataSource to a DataTable as it
        ''' was loaded via a DataTable in form load
        ''' 
        ''' Next, using Lambda group by TaxRate, create a IEnumerable of a GroupData
        ''' type which does the summing.
        ''' 
        ''' Next loop through the GroupData data and insert into the bottom DataGridView
        ''' where the last column does the sum for the final column.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView2.Rows.Clear()
    
            Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
    
            Dim Results = dt.AsEnumerable.GroupBy(
                Function(student) student.Field(Of Decimal)("TaxRate")) _
                .Select(Function(group) New GroupData With
                {
                    .TaxRate = group.Key,
                    .TaxRateSum = group.Sum(Function(row) row.Field(Of Decimal)("TaxRate")),
                    .ValueSum = group.Sum(Function(row) row.Field(Of Decimal)("Value")),
                    .TaxAmountSum = group.Sum(Function(row) row.Field(Of Decimal)("TaxAmount")),
                    .Data = group
                })
    
    
            For Each group In Results
                DataGridView2.Rows.Add(New Object() _
                    {
                        group.TaxRate,
                        group.ValueSum,
                        group.TaxAmountSum,
                        group.ValueSum + group.TaxAmountSum
                    })
            Next
        End Sub
    
    End Class
    Public Class GroupData
        Public Property TaxRate As Decimal
        Public Property TaxRateSum As Decimal
        Public Property ValueSum As Decimal
        Public Property TaxAmountSum As Decimal
        Public Property Data As IGrouping(Of Decimal, DataRow)
    End Class
    

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Sid Williams Sunday, December 24, 2017 7:15 PM
    Sunday, December 24, 2017 2:44 PM
    Moderator

All replies

  • There are several things to ask about before recommending a solution.

    How is the top (in your question) loaded e.g. using a TableAdapter, using a DataSet (with a single DataTable within), a DataTable, a list etc ?

    If from a database (goes back to the first question) what are the fields (are they the same as the one's shown), is there an identifier field (e.g. a primary key not visible).

    The more details you provide the better a recommendation will be.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, December 24, 2017 1:43 PM
    Moderator
  • No, that datagridview is not bounded to any database, we have inserted the records using rows.add method.
    Sunday, December 24, 2017 1:49 PM
  • Hello,

    Well I always recommend to never load a DataGridView via rows.add other than for a view only function but if there are calculations or groupings to be done load a dataGridView from a DataTable or List(Of T).

    In this case I'm loading the top DataGridView with a DataTable (A DataTable as you can see does not need to work from a database table). Then in Button1 click event I do a Lambda statement with a GroupBy to get sums per group.

    Note in this case the top DataGridView obtained it's column names from the DataTable although I could had overridden the column names also. The bottom DataGridView I created columns in the IDE. I didn't do any formatting, left that to you as that is easy to do.

    Public Class Form1
        ''' <summary>
        ''' Rather than loading via DataGridView.Rows.Add a DataTable is used.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable With {.TableName = "MyTable"}
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "TaxRate", .DataType = GetType(Decimal)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Value", .DataType = GetType(Decimal)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "TaxAmount", .DataType = GetType(Decimal)})
    
            dt.Rows.Add(New Object() {5D, 1000D, 50D})
            dt.Rows.Add(New Object() {5D, 2000D, 100D})
            dt.Rows.Add(New Object() {10D, 1000D, 100D})
            dt.Rows.Add(New Object() {10D, 2000D, 200D})
            dt.Rows.Add(New Object() {15D, 1000D, 150D})
            dt.Rows.Add(New Object() {15D, 2000D, 300D})
    
            DataGridView1.DataSource = dt
    
        End Sub
        ''' <summary>
        ''' Here we first cast the top DataGridView DataSource to a DataTable as it
        ''' was loaded via a DataTable in form load
        ''' 
        ''' Next, using Lambda group by TaxRate, create a IEnumerable of a GroupData
        ''' type which does the summing.
        ''' 
        ''' Next loop through the GroupData data and insert into the bottom DataGridView
        ''' where the last column does the sum for the final column.
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView2.Rows.Clear()
    
            Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
    
            Dim Results = dt.AsEnumerable.GroupBy(
                Function(student) student.Field(Of Decimal)("TaxRate")) _
                .Select(Function(group) New GroupData With
                {
                    .TaxRate = group.Key,
                    .TaxRateSum = group.Sum(Function(row) row.Field(Of Decimal)("TaxRate")),
                    .ValueSum = group.Sum(Function(row) row.Field(Of Decimal)("Value")),
                    .TaxAmountSum = group.Sum(Function(row) row.Field(Of Decimal)("TaxAmount")),
                    .Data = group
                })
    
    
            For Each group In Results
                DataGridView2.Rows.Add(New Object() _
                    {
                        group.TaxRate,
                        group.ValueSum,
                        group.TaxAmountSum,
                        group.ValueSum + group.TaxAmountSum
                    })
            Next
        End Sub
    
    End Class
    Public Class GroupData
        Public Property TaxRate As Decimal
        Public Property TaxRateSum As Decimal
        Public Property ValueSum As Decimal
        Public Property TaxAmountSum As Decimal
        Public Property Data As IGrouping(Of Decimal, DataRow)
    End Class
    

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Sid Williams Sunday, December 24, 2017 7:15 PM
    Sunday, December 24, 2017 2:44 PM
    Moderator
  • I am trying your solution, see my datagrid looks similar to this

    Once I execute my program, I am getting the error below -

    What may be going wrong ?

    Sunday, December 24, 2017 6:58 PM
  • Did you figure it out? Usually such an error indicates one of the fields on a row is not set to a value and is null/Nothing.

    Looking at the screenshots I'm not seeing a problem.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, December 24, 2017 8:31 PM
    Moderator
  • solved, thanks.

    Thursday, January 4, 2018 10:26 AM