Datagridview DataTable edit, update and show at a time VB.net-CodePudding

i made a data table like this :

    Dim DTCart As New DataTable

    DTCart.Columns.Add(New DataColumn With {.ColumnName = "Code", .DataType = GetType(String)})
    DTCart.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})
    DTCart.Columns.Add(New DataColumn With {.ColumnName = "Amount", .DataType = GetType(Double)})
    DTCart.Columns.Add(New DataColumn With {.ColumnName = "Price", .DataType = GetType(Long)})
    DTCart.Columns.Add(New DataColumn With {.ColumnName = "Total", .DataType = GetType(Long), .Expression = "Amount*Price"})

    DTCart.Columns("Code").ReadOnly = True
    DTCart.Columns("Name").ReadOnly = True
    DTCart.Columns("Amount").ReadOnly = False
    DTCart.Columns("Price").ReadOnly = True
    DTCart.Columns("Total").ReadOnly = True

Then i show the data table to Datagridview using DataGridView.DataSource = DTCart

i set the 'Amount' Column to be editable so then i can update it by editing the cell in Datagridview like this :

Private Sub DataGridViewCart_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewCart.CellEndEdit
    If DataGridViewCart.CurrentRow.Cells(2).Value.ToString = "" Then
        DataGridViewCart.CurrentRow.Cells(2).Value = 0
    End If

    Dim FindRow() As DataRow = DTCart.Select("Code='" & DataGridViewCart.CurrentRow.Cells(0).Value & "'")
    FindRow(0)("Amount") = DataGridViewCart.CurrentRow.Cells(2).Value

    Total()
End Sub

The 'Total()' method is just a method to sum 'Total' Column on DataTable so i can set a specific label's text to its value like this :

Sub Total()
    Dim sum As Double = 0
    For Each row As DataRow In DTCart.Rows
        sum  = row.Item("Total")
    Next

    LabelTotal.Text = sum
End Sub

Now, the problem is, when i finish editing the cell, the DataTable is Updated (i checked), but the LabelTotal.Text isn't.

The funny thing is this problem comes only the first time i edit the cell. When i tried to edit the same cell a second time it runs perfectly, everything is updated.

Help.. :'(

sorry for my bad english

CodePudding user response：

I question your comment that…

”When i tried to edit the same cell a second time it runs perfectly, everything is updated.” … ?

In my small tests... the sum of the “Total” column always reflected the “previous” total and NOT the actual total. In other words, the LabelTotal always missed the last change.

The reason for this is because when the code “sums” the tables “Total” column, it is when a value in the Amount column CHANGES (… note … the event will not fire when the “expression” column is updated). Therefore when the user changes an Amount value in the grid, the grid’s CellEndEdit event fires and sums the Total column and places that value into the label… HOWEVER since the Total column is an “Expression” column, its value has not yet been updated and will NOT be updated until AFTER the grid’s CellEndEdit has exited… therefore the last “Amount” change is not calculated in the sum.

I suggest you drop the Total method that loops through each row and sums the values and instead let the DataTable do this work for you using its Compute method to sum the Total column. This may simplify the code; however it will not solve the previous issue.

In order to keep the label updated with the correct sum of the Total column, I suggest you call the tables AcceptChanges() method BEFORE calling the tables compute method. Something like…

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewCart.CellEndEdit
  If DataGridViewCart.CurrentRow.Cells(2).Value.ToString = "" Then
    DataGridViewCart.CurrentRow.Cells(2).Value = 0
  End If
  DTCart.AcceptChanges()
  LabelTotal.Text = DTCart.Compute("SUM(Total)", "").ToString()
End Sub

Lastly, I agree with Mary’s comment that you should change the Amount, Price and Total columns to Decimal. Otherwise, you will lose fractional parts.

Edit… Getting the correct sum without calling the data tables AcceptChanges() method…

After further review, it is possible that you may not want to call the data tables AcceptChanges event. An example is commented in addition to the possible situation where you may be tracking the changes made. In that case, you could instead wire up the DataTable.RowChanged event. It will fire AFTER the expression column has been updated so the SUM should be correct. This may look something like…

AddHandler DTCart.RowChanged, AddressOf DTCart_RowChanged

Add the line above to the Forms Load event... and below the event...

Private Sub DTCart_RowChanged(sender As Object, e As DataRowChangeEventArgs)
  LabelTotal.Text = DTCart.Compute("SUM(Total)", "").ToString()
End Sub