How to Enter a Running Total into a DataTable

Question

I have a DataGrid with these columns:

Header 1Header 2Cell 1Cell 2Cell 3Cell 4 and so on with additional parts. The ItemsSource is a DataTable with the same columns. Lastly, there is a known total cost. For this example, let's say $100. As the user enters costs for each part, I want the remaining cost to be entered in the next row. For example, suppose the user enters $34 in the first row. Then the grid should look like this:

where the 66 has been entered by the system. If the user overwrites the 66 with, say 42, the grid should look like this:

PartCostPart A34Part B42Part24

with the 24 entered by the application. And so on. I can't get this to work and have not been able to find examples on the web that provide ideas. Any help will be much appreciated.

Answer 1

Hi,
if you use Windows App you can use this demo in blank form:

Public Class Form1
  Private dgv As New DataGridView With {.Dock = DockStyle.Fill}
  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.Width = 1000
    Me.Controls.Add(dgv)
    dgv.DataSource = LoadDataSet()
    AddHandler dgv.CellEndEdit, AddressOf dgv_CellEndEdit
  End Sub

  Private Sub dgv_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs)
    If e.ColumnIndex < 2 Then Return
    Dim row As DataRow = dt.Rows(e.RowIndex)
    Dim sum As Decimal = CType(row(2), Decimal)
    For i = 3 To dgv.ColumnCount - 1
      Dim dec As Decimal = 0
      If Not Convert.IsDBNull(row(i)) Then dec = CType(row(i), Decimal)
      If dec = 0 Then
        row(i) = sum
        sum = 0
        Exit For
      End If
      sum -= dec
    Next
  End Sub

  Dim ds As New DataSet
  Dim dt As New DataTable

  Private Function LoadDataSet() As BindingSource
    With dt
      With .Columns
        With .Add("Header1", GetType(Integer))
          .AutoIncrement = True
          .AutoIncrementSeed = -1
          .AutoIncrementStep = -1
        End With
        .Add("Header2", GetType(String))
        .Add("Total", GetType(Decimal))
        .Add("Cell1", GetType(Decimal))
        .Add("Cell2", GetType(Decimal))
        .Add("Cell3", GetType(Decimal))
        .Add("Cell4", GetType(Decimal))
        .Add("Cell5", GetType(Decimal))
        .Add("Cell6", GetType(Decimal))
      End With
      For i = 1 To 10
        .Rows.Add(i, $"Row {i}")
      Next
    End With
    ds.Tables.Add(dt)
    Return New BindingSource() With {.DataSource = ds.Tables(0)}
  End Function
End Class

Result:

Answer 2

Hi,@RogerSchlueter-7899 . Welcome Microsoft Q&A. You could achieve this by adding a calculated property Num3 to your Data class, which updates its value based on the formula Total - Num1 - Num2. Additionally, make sure to set Num3 as readonly to prevent direct modification.

 <Window.DataContext>
        <local:MainViewModel/>
    </Window.DataContext>
    <Grid>
        <DataGrid ItemsSource="{Binding DataList}"  AutoGenerateColumns="False" >
            <DataGrid.Columns>
                <DataGridTextColumn Header="Num1" Binding="{Binding Num1,Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"/>
                <DataGridTextColumn Header="Num2" Binding="{Binding Num2,Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"/>
                <DataGridTextColumn Header="Num3" Binding="{Binding Num3,UpdateSourceTrigger=PropertyChanged}"/>
                <DataGridTextColumn Header="Total" Binding="{Binding Total,Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"/>
            </DataGrid.Columns>
        </DataGrid>
    </Grid>

Codebedhind:

Imports System.Collections.ObjectModel
Imports System.ComponentModel
Imports System.Runtime.CompilerServices

Class MainWindow

End Class
Public Class MainViewModel
    Public Property DataList As ObservableCollection(Of Data)

    Public Sub New()
        DataList = New ObservableCollection(Of Data) From {
            New Data With {
                .Num1 = 10,
                .Num2 = 20,
                .Total = 100
            },
            New Data With {
                .Num1 = 5,
                .Num2 = 15,
                .Total = 50
            }
        }
    End Sub
End Class

Public Class Data
    Implements INotifyPropertyChanged

    Private _num1 As Integer

    Public Property Num1 As Integer
        Get
            Return Me._num1
        End Get
        Set(ByVal value As Integer)
            Me._num1 = value
            NotifyPropertyChanged("Num1")
            UpdateNum3()
        End Set
    End Property



    Private _num2 As Integer

    Public Property Num2 As Integer
        Get
            Return Me._num2
        End Get
        Set(ByVal value As Integer)
            Me._num2 = value
            NotifyPropertyChanged("Num2")
            UpdateNum3()
        End Set
    End Property

    Private _num3 As Integer

    Public Property Num3 As Integer
        Get
            Return Me._num3
        End Get
        Private Set(ByVal value As Integer)
            Me._num3 = value
            NotifyPropertyChanged("Num3")
        End Set
    End Property

    Private _total As Integer

    Public Property Total As Integer
        Get
            Return Me._total
        End Get
        Set(ByVal value As Integer)
            Me._total = value
            NotifyPropertyChanged("Total")
            UpdateNum3()
        End Set
    End Property

    Private Sub UpdateNum3()
        Num3 = Total - Num1 - Num2
    End Sub

    Public Event PropertyChanged As PropertyChangedEventHandler _
             Implements INotifyPropertyChanged.PropertyChanged

    Private Sub NotifyPropertyChanged(ByVal propertyName As String)
        RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
    End Sub
End Class

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.