none
calculate the total balance of the row or column RRS feed

  • Question

  • i have some problems here, can you please help me how to calculate the total balance of the row or column in my datagridview.

    ex

    record_number                    name                      balance

    3                                          john                         20

    3                                          john                         30

    3                                          john                         40

    4                                          john                         30

    what i want to do is to calculate the last record_number "3" with a balance of 40 and add to the record_number "4" to get the total balance of the data

    is this possible?


    • Edited by no[one] Saturday, November 3, 2018 11:46 AM
    Saturday, November 3, 2018 11:45 AM

Answers

  • Hello,

    If open to loading the DataGridView with a DataTable then continue, if not stop here.

    Load data

    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 = "record_number", .DataType = GetType(Integer)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "Grade", .DataType = GetType(Integer)})
    
        dt.Rows.Add(New Object() {3, "john", 20})
        dt.Rows.Add(New Object() {3, "john", 30})
        dt.Rows.Add(New Object() {3, "john", 40})
        dt.Rows.Add(New Object() {4, "john", 30})
    
        DataGridView1.DataSource = dt
    
    End Sub

    In a button click event

    '
    ' We are looking for a row where grade = 40
    '
    Dim result = DataGridView1.Rows.Cast(Of DataGridViewRow).FirstOrDefault(Function(row) CInt(row.Cells("Grade").Value) = 40)
    '
    ' Did we get one
    '
    If result IsNot Nothing Then
        '
        ' Is there a row beneath the found row?
        '
        If Not result.Index >= DataGridView1.Rows.Count Then
            '
            ' Get current found row grade value
            '
            Dim Value = CInt(DataGridView1.Rows(result.Index + 1).Cells("Grade").Value) + CInt(DataGridView1.Rows(result.Index).Cells("Grade").Value)
            '
            ' Set row below value
            '
            CType(DataGridView1.DataSource, DataTable).Rows(result.Index + 1).SetField(Of Integer)("Grade", Value)
        End If
    End If


    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

    • Proposed as answer by Alex Li-MSFT Monday, November 5, 2018 7:19 AM
    • Marked as answer by no[one] Friday, November 9, 2018 6:54 AM
    Saturday, November 3, 2018 12:22 PM
    Moderator
  • Hi,

    Do you want to this effect ?

    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 = "record_number", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Grade", .DataType = GetType(Integer)})
    
            dt.Rows.Add(New Object() {3, "john", 20})
            dt.Rows.Add(New Object() {3, "john", 30})
            dt.Rows.Add(New Object() {3, "john", 40})
            dt.Rows.Add(New Object() {4, "john", 20})
            dt.Rows.Add(New Object() {4, "john", 40})
            dt.Rows.Add(New Object() {4, "john", 60})
            dt.Rows.Add(New Object() {4, "john", 160})
            DataGridView1.DataSource = dt
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            '
            ' We are looking for a row where grade = 40
            '
            Dim result = DataGridView1.Rows.Cast(Of DataGridViewRow).FirstOrDefault(Function(row) CInt(row.Cells("Grade").Value) = 40)
            '
            ' Did we get one
            '
            If result IsNot Nothing Then
                '
                ' Is there a row beneath the found row?
                '
                If Not result.Index >= DataGridView1.Rows.Count Then
                    '
                    ' Get current found row grade value
                    '
                    Dim count1 As Integer = 0
                    Dim count2 As Integer = 0
                    For Each dr As DataGridViewRow In DataGridView1.Rows
                        count1 += IIf(dr.Cells("record_number").Value = 4, 1, 0)
                        'count2 += IIf(dr.Cells("record_number").Value = 3, 1, 0)
                    Next
                    Dim Value = CInt(DataGridView1.Rows(result.Index + count1).Cells("Grade").Value) + CInt(DataGridView1.Rows(result.Index).Cells("Grade").Value)
                    '
                    ' Set row below value
                    '
                    CType(DataGridView1.DataSource, DataTable).Rows(result.Index + count1).SetField(Of Integer)("Grade", Value)
                End If
            End If
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Alex Li-MSFT Friday, November 9, 2018 6:29 AM
    • Marked as answer by no[one] Friday, November 9, 2018 6:54 AM
    Friday, November 9, 2018 6:29 AM

All replies

  • Hello,

    If open to loading the DataGridView with a DataTable then continue, if not stop here.

    Load data

    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 = "record_number", .DataType = GetType(Integer)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})
        dt.Columns.Add(New DataColumn With {.ColumnName = "Grade", .DataType = GetType(Integer)})
    
        dt.Rows.Add(New Object() {3, "john", 20})
        dt.Rows.Add(New Object() {3, "john", 30})
        dt.Rows.Add(New Object() {3, "john", 40})
        dt.Rows.Add(New Object() {4, "john", 30})
    
        DataGridView1.DataSource = dt
    
    End Sub

    In a button click event

    '
    ' We are looking for a row where grade = 40
    '
    Dim result = DataGridView1.Rows.Cast(Of DataGridViewRow).FirstOrDefault(Function(row) CInt(row.Cells("Grade").Value) = 40)
    '
    ' Did we get one
    '
    If result IsNot Nothing Then
        '
        ' Is there a row beneath the found row?
        '
        If Not result.Index >= DataGridView1.Rows.Count Then
            '
            ' Get current found row grade value
            '
            Dim Value = CInt(DataGridView1.Rows(result.Index + 1).Cells("Grade").Value) + CInt(DataGridView1.Rows(result.Index).Cells("Grade").Value)
            '
            ' Set row below value
            '
            CType(DataGridView1.DataSource, DataTable).Rows(result.Index + 1).SetField(Of Integer)("Grade", Value)
        End If
    End If


    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

    • Proposed as answer by Alex Li-MSFT Monday, November 5, 2018 7:19 AM
    • Marked as answer by no[one] Friday, November 9, 2018 6:54 AM
    Saturday, November 3, 2018 12:22 PM
    Moderator
  • record_number                    name                      balance

    3                                          john                         20

    3                                          john                         30

    3                                          john                         40

    4                                          john                         20

    4                                          john                         40

    what i want to do is to calculate the last record_number "3" which has a balance of 40 and add to the last record record_number "4" which is 40 to get the total balance of the data.

    Dim result = DataGridView1.Rows.Cast(Of DataGridViewRow).FirstOrDefault(Function(row) CInt(row.Cells("Grade").Value) = 40)

    what if i want to look for the value programmatically and not put in the code

    sorry its really hard for me to explain in english and im just a beginner

    Wednesday, November 7, 2018 12:07 AM
  • Hi,

    Do you want to this effect ?

    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 = "record_number", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn With {.ColumnName = "Grade", .DataType = GetType(Integer)})
    
            dt.Rows.Add(New Object() {3, "john", 20})
            dt.Rows.Add(New Object() {3, "john", 30})
            dt.Rows.Add(New Object() {3, "john", 40})
            dt.Rows.Add(New Object() {4, "john", 20})
            dt.Rows.Add(New Object() {4, "john", 40})
            dt.Rows.Add(New Object() {4, "john", 60})
            dt.Rows.Add(New Object() {4, "john", 160})
            DataGridView1.DataSource = dt
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            '
            ' We are looking for a row where grade = 40
            '
            Dim result = DataGridView1.Rows.Cast(Of DataGridViewRow).FirstOrDefault(Function(row) CInt(row.Cells("Grade").Value) = 40)
            '
            ' Did we get one
            '
            If result IsNot Nothing Then
                '
                ' Is there a row beneath the found row?
                '
                If Not result.Index >= DataGridView1.Rows.Count Then
                    '
                    ' Get current found row grade value
                    '
                    Dim count1 As Integer = 0
                    Dim count2 As Integer = 0
                    For Each dr As DataGridViewRow In DataGridView1.Rows
                        count1 += IIf(dr.Cells("record_number").Value = 4, 1, 0)
                        'count2 += IIf(dr.Cells("record_number").Value = 3, 1, 0)
                    Next
                    Dim Value = CInt(DataGridView1.Rows(result.Index + count1).Cells("Grade").Value) + CInt(DataGridView1.Rows(result.Index).Cells("Grade").Value)
                    '
                    ' Set row below value
                    '
                    CType(DataGridView1.DataSource, DataTable).Rows(result.Index + count1).SetField(Of Integer)("Grade", Value)
                End If
            End If
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Alex Li-MSFT Friday, November 9, 2018 6:29 AM
    • Marked as answer by no[one] Friday, November 9, 2018 6:54 AM
    Friday, November 9, 2018 6:29 AM