none
datatable / dgv sum of 2 columns RRS feed

  • Question

  • I have a datatable that is the source of datagridview.

    what is the best practice to do that:

    id | number1 | number2

    1     3               5

    2     1               1

    i want to add a new column like this:

    id  | number1  | number2  | Total

    1     3               5                  8

    2     1               1                  2

    should i do it on the datable or dgv? i tryed both and failed..

    edit: i used the .expression but didn't go well..

    • Edited by noampro8 Sunday, July 1, 2018 4:03 AM
    Sunday, July 1, 2018 3:58 AM

Answers

  • What didn't go well with .Expression ? The following provides a solution to your question unless there is something you left out.

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "ID", .DataType = GetType(Integer),
                              .AutoIncrement = True,
                              .AutoIncrementSeed = 1, .ReadOnly = True})
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "C1", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C2", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C3", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C4", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Total", .DataType = GetType(Integer),
                              .Expression = "C1 + C2 + C3 + C4"})
    
            dt.Rows.Add(New Object() {Nothing, 1, 2, 3, 4})
    
            DataGridView1.DataSource = dt
    
        End Sub
    End Class
    

    I will close out by saying there is more to just using .Expression, what happens when someone deletes a cell value in the above example? The answer is the calculation will be wrong so we need to compensate for this e.g.

    Public Class Form1
        Private ReadOnly _columnNames As New List(Of String) From {"C1", "C2", "C3", "C4"}
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "ID", .DataType = GetType(Integer),
                              .AutoIncrement = True,
                              .AutoIncrementSeed = 1, .ReadOnly = True})
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "C1", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C2", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C3", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C4", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Total", .DataType = GetType(Integer),
                              .Expression = "C1 + C2 + C3 + C4"})
    
            dt.Rows.Add(New Object() {Nothing, 1, 2, 3, 4})
    
            DataGridView1.DataSource = dt
    
            AddHandler dt.ColumnChanged, AddressOf _ColumnChanged
        End Sub
        Private Sub _ColumnChanged(sender As Object, e As DataColumnChangeEventArgs)
            If Not e.Row.RowState = DataRowState.Deleted Then
    
                If _columnNames.Contains(e.Column.ColumnName) Then
    
                    If Not e.Row.RowState = DataRowState.Detached Then
    
                        If IsDBNull(e.Row.Item(e.Column.ColumnName)) Then
                            e.Row.Item(e.Column.ColumnName) = 0
                        End If
    
                        e.Row.AcceptChanges()
                    End If
    
                End If
            End If
        End Sub
    End Class
    

    There are other considerations too for values changing and then many may want a grand-total so this means more code to satisfy user requirements.

    In the last code sample above I use ColumnChanged event for the DataTable, depending on requirements there may be a need to subscribe to RowChanged and RowDeleted events of the DataTable.


    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 noampro8 Sunday, July 1, 2018 10:40 AM
    Sunday, July 1, 2018 9:12 AM
    Moderator

All replies

  • What didn't go well with .Expression ? The following provides a solution to your question unless there is something you left out.

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "ID", .DataType = GetType(Integer),
                              .AutoIncrement = True,
                              .AutoIncrementSeed = 1, .ReadOnly = True})
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "C1", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C2", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C3", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C4", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Total", .DataType = GetType(Integer),
                              .Expression = "C1 + C2 + C3 + C4"})
    
            dt.Rows.Add(New Object() {Nothing, 1, 2, 3, 4})
    
            DataGridView1.DataSource = dt
    
        End Sub
    End Class
    

    I will close out by saying there is more to just using .Expression, what happens when someone deletes a cell value in the above example? The answer is the calculation will be wrong so we need to compensate for this e.g.

    Public Class Form1
        Private ReadOnly _columnNames As New List(Of String) From {"C1", "C2", "C3", "C4"}
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "ID", .DataType = GetType(Integer),
                              .AutoIncrement = True,
                              .AutoIncrementSeed = 1, .ReadOnly = True})
    
            dt.Columns.Add(New DataColumn With {.ColumnName = "C1", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C2", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C3", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "C4", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Total", .DataType = GetType(Integer),
                              .Expression = "C1 + C2 + C3 + C4"})
    
            dt.Rows.Add(New Object() {Nothing, 1, 2, 3, 4})
    
            DataGridView1.DataSource = dt
    
            AddHandler dt.ColumnChanged, AddressOf _ColumnChanged
        End Sub
        Private Sub _ColumnChanged(sender As Object, e As DataColumnChangeEventArgs)
            If Not e.Row.RowState = DataRowState.Deleted Then
    
                If _columnNames.Contains(e.Column.ColumnName) Then
    
                    If Not e.Row.RowState = DataRowState.Detached Then
    
                        If IsDBNull(e.Row.Item(e.Column.ColumnName)) Then
                            e.Row.Item(e.Column.ColumnName) = 0
                        End If
    
                        e.Row.AcceptChanges()
                    End If
    
                End If
            End If
        End Sub
    End Class
    

    There are other considerations too for values changing and then many may want a grand-total so this means more code to satisfy user requirements.

    In the last code sample above I use ColumnChanged event for the DataTable, depending on requirements there may be a need to subscribe to RowChanged and RowDeleted events of the DataTable.


    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 noampro8 Sunday, July 1, 2018 10:40 AM
    Sunday, July 1, 2018 9:12 AM
    Moderator
  • The DGV does nothing but display the data when using a data table source.

    One would do any math operations with the datatable. 

     
    Sunday, July 1, 2018 9:17 AM