Running total columns in DataGridView

Question

Hi,

I have a DataGridView with 4 columns & I am trying make the last 2 columns running totals, calculated by checking if the Type column value is A or B, then adding the Quantity column progressively as new rows are added. Please see table below to demonstrate what I am trying to achieve. I have added the VB code I am using below, when I use this I am getting a running total for both types in both columns, i.e. both running total columns are adding all the Vol column values all the way down regardless of whether they are type A or B. Can anyone help sort my code to differentiate between the two types & have each running total column only add the Vol for the appropriate Type?

Type	Vol	Running Total A	Running Total B
A	3	3
A	2	5
B	3		3
A	4	9
B	5		8

Dim valT As Double = 0

Dim valA As Double = 0

 

        For Each row As DataGridViewRow In DataGridView1.Rows

            If row.Cells(“colTyp”).Value = (“A”) Then

                For i As Integer = 0 To DataGridView1.Rows.Count - 1

                    valA += DataGridView1.Rows(i).Cells("colVol").Value

                    DataGridView1.Rows(i).Cells("colRunA").Value = valA

                Next

            ElseIf row.Cells(“colTyp”).Value = (“B”) Then

                    For i As Integer = 0 To DataGridView1.Rows.Count - 1

                        valB += DataGridView1.Rows(i).Cells("colVol").Value

                        DataGridView1.Rows(i).Cells("colRunB").Value = valB

                    Next

            End If

        Next

Answer 1

Hi

Here is another version, this one also has 2 TextBoxes and a Button for user input as well as standard DGV new row input, and a Button to refresh with new random data. There is minimal exception handling so there are likely to be input exceptions to take care of. Negative and blank Vol values are catered for, though you did not specify.Entries can be from the 2 TextBoxes and Button, or, directly into the DGV new row.

The values of columns 0 and 1 can be edited and changes will propagate through.

In tests, when an invalid entry is detected, it will be handle sensibly,

' to test this code, start a new Project
' add a DataGridView named DGV and a Label1,
' Label2, TextBox1, TextBox2, Button1 and
' Button2 in the Designer
' Button2 is to refresh with new random data.
' Copy/Replace ALL the code in Form1 
' with ALL this code.
' NOTE: limited exceprion handling for inputs
' 
Option Strict On
Option Explicit On
Public Class Form1
	Dim dt As New DataTable("Freddy")
	Dim r As New Random
	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
		With dt
			.Columns.Add("Type", GetType(String))
			.Columns.Add("Vol", GetType(Integer))
			.Columns.Add("Running Total A", GetType(Integer))
			.Columns.Add("Running Total B", GetType(Integer))
		End With
		SetTestData()
		DGV.Columns("Running Total A").ReadOnly = True
		DGV.Columns("Running Total B").ReadOnly = True
	End Sub
	Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
		' make a new set of Test Data
		dt.Clear()
		SetTestData()
		Calc()
	End Sub
	Sub SetTestData()
		' add some initial test data
		Dim s1 As Integer = 0
		Dim s2 As Integer = 0
		For i As Integer = 0 To 5
			Dim r1 As Integer = r.Next(1, 8)
			dt.Rows.Add(IIf(r.Next(0, 2) = 0, "A"c, "B"c), r1)
		Next
		DGV.DataSource = dt
	End Sub
	
	Sub DGV_CellValidated(sender As Object, e As DataGridViewCellEventArgs) Handles DGV.CellValidated
		Calc()
	End Sub
	Sub Calc()
		Dim s1 As Integer = 0
		Dim s2 As Integer = 0
		For Each r As DataGridViewRow In DGV.Rows
			If Not r.Index = DGV.NewRowIndex Then
				Dim v As Integer = 0
				Integer.TryParse(r.Cells(1).Value.ToString, v)
				r.Cells(0).Value = r.Cells(0).Value.ToString.ToUpper
				Dim a1 As String = r.Cells(0).Value.ToString.ToUpper
				Dim r1 As Integer = v
				r.Cells(1).Value = IIf(v = 0, 0, v)
				'	If r.Index = 6 Then Stop
				If a1 = "A" Then
					s1 += CInt(r.Cells(1).Value)
					r.Cells("Running Total A").Value = s1
					r.Cells("Running Total B").Value = DBNull.Value
				ElseIf a1 = "B" Then
					s2 += CInt(r.Cells(1).Value)
					r.Cells("Running Total A").Value = DBNull.Value
					r.Cells("Running Total B").Value = s2
				End If
			End If
		Next
		Label1.Text = dt.Compute("SUM([Running Total A])", String.Empty).ToString
		Label2.Text = dt.Compute("SUM([Running Total B])", String.Empty).ToString
		TextBox1.Text = String.Empty
		TextBox2.Text = String.Empty
	End Sub
	Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
		If TextBox1.Text.Length > 0 Then
			Dim i = -1
			Integer.TryParse(TextBox2.Text, i)
			dt.Rows.Add(TextBox1.Text.ToUpper, i)
			Calc()
		End If
	End Sub
	Private Sub DGV_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles DGV.DataError
		If e.Context = 768 Then
			' parsing or commit error
			DGV.CancelEdit()
			Console.Beep(3000, 80)
			DGV(e.ColumnIndex, e.RowIndex).Value = 0
			DGV.EndEdit()
		End If
	End Sub
End Class