Datagridview computed columns force recalc

Question

Hi,
I have a datagridview that is filled from a database (DataAdapter & DataSet).
I've added a few computed columns. the values in these computed columns may depend on other calculated columns.

When I edit the cells in the grid and change rows, then the calculated columns update accordingly.
However I'd like to recalculate after a cell has been edited and the focus moved to another cell in the same row.
I've been trying to do it with cell endEdit however it doesn't recalculate all the computed columns.
Is there a way to mimic leaving the row to force a recalc on all columns?

Sorry I can't show the code because I'm on a different computer
Andy

Answer 1

Hello,

If by computed columns you are using DataColumn expressions than they should handle any changes in values, if not then you need to look at manually performing the calculations which would in one sense of the word be counter productive yet is possible as shown in the following code sample that does not use expressions but instead works with data table events.

Answer 2

Hi Karen,
I think I'm using DataColumn Expressions and the values in the calculated columns are being updated but only when I move to another row. Not when I move to a different cell in the same row.
I'd really like them to update as soon as a cell that affects a calculated column loses focus. (Like a spreadsheet works)
Here's the code to fill the dataset & add the columns.
Sub DAdapterLoad()
DstrSql = "PQDETAILS"
OpenConnection()

        If dsDetail.Tables.Count > 0 Then
            dsDetail.Reset()
        End If
        'MsgBox("line after tables.clear. Tables count = " & DsToolGrid.Tables.Count)
        Dim command As New OleDbCommand
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = DstrSql
        command.Connection = Con
        command.Parameters.Add(New OleDbParameter("@recordid", OleDbType.Integer))
        command.Parameters("@recordid").Value = CInt(TxtSelect.Text)
        Dadapter.SelectCommand = command
        Dadapter.Fill(dsDetail)
        Dim DatasetColcount As Integer = dsDetail.Tables(0).Columns.Count
        'MsgBox(DatasetColcount)
        Con.Close()
        ' ADD COLUMNS
        Dim SetCostColumn As DataColumn = New DataColumn
        With SetCostColumn
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "Set_Cost"
            .Expression = "RatePerHour/60*settingtime/" & TextBox6.Text
        End With

        Dim PercColumn As DataColumn = New DataColumn
        With PercColumn
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "At80"
            .Expression = "CycleTime/0.8"
        End With

        Dim ToolColumn As DataColumn = New DataColumn
        With ToolColumn
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "TC_Item"
            .Expression = "TotalToolCosts/" & TextBox6.Text
        End With

        Dim McCostColumn As DataColumn = New DataColumn
        With McCostColumn
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "MC_Cost"
            .Expression = "RatePerHour/60* At80"
        End With

        Dim TotalCostColumn As DataColumn = New DataColumn
        With TotalCostColumn
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "Total_Cost"
            .Expression = "set_cost  + MC_Cost + TC_Item"
            '.Expression = "Convert("set_cost  + MC_Cost", 'System.Decimal')"

        End With

        Dim HoursColumn As DataColumn = New DataColumn
        With HoursColumn
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "Hours"
            .Expression = " At80/60 * " & TextBox6.Text & "+ settingTime/60"
        End With

        Dim ValueColumn As DataColumn = New DataColumn
        With ValueColumn
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "Value"
            .Expression = "(set_cost  + MC_Cost + TC_Item)  * " & TextBox6.Text ' & " + "
        End With

        'Add columns
        With dsDetail.Tables(0).Columns
            .Add(SetCostColumn)
            .Add(PercColumn)
            .Add(ToolColumn)
            .Add(McCostColumn)
            .Add(TotalCostColumn)
            .Add(HoursColumn)
            .Add(ValueColumn)
        End With
        With dsDetail.Tables(0)
            .Columns("Set_Cost").SetOrdinal(4)
            .Columns("At80").SetOrdinal(6)
            .Columns("MC_COST").SetOrdinal(7)
            .Columns("TOTAL_COST").SetOrdinal(11)
        End With
        DataGridView1.DataSource = dsDetail.Tables(0)
        LoadStatus = True
    End Sub

I've tried using the datagridview cellEndEdit event and it works to a certain extent but only affects the cell that's been eddited leaving the rest of the calculated fields as they were.

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        With dsDetail.Tables(0)
            .Columns("Set_Cost").Expression = "RatePerHour/60*settingtime/" & TextBox6.Text
            .Columns("At80").Expression = "CycleTime/0.8"
            .Columns("TC_Item").Expression = "TotalToolCosts/" & TextBox6.Text
            .Columns("Total_Cost").Expression = "set_cost  + MC_Cost + TC_Item"
            .Columns("Hours").Expression = " At80/60 * " & TextBox6.Text & "+ settingTime/60"
        End With
    End Sub

Andy