c# - Correct event to calculate Cells values in a DataGridView

Question

In a DataGridView I use this code, in the CellEndEdit event to automatically calculate the amounts multiplied by the quantity:

private void myGrid_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    DataGridView oDGV = (DataGridView)sender; 
    DataGridViewCell oCell = oDGV.Rows[e.RowIndex].Cells[e.ColumnIndex];

    // Only the quantity can be changed
    if (oCell.Value != DBNull.Value && oCell.OwningColumn.Name == "num")
    {
        oDGV.EndEdit(); 
        int nNum = Convert.ToInt16(oCell.Value);
        decimal nPrice = Convert.ToDecimal(oDGV.Rows[e.RowIndex].Cells["price"].Value.ToString());
        decimal nTot = nPrice * nNum;
        oDGV.Rows[e.RowIndex].Cells["tot"].Value = nTot;
    }
}

Apparently a simple operation, but it happens to me every so often that the calculation is not carried out and I have to go back to the cell, type the value again and press enter (even if everything is correct on the screen).
I don't understand where the problem is, maybe I have to use another event?

Answer 1

You have 3 Columns that need to interact:

• price Column: it should specify a Unit Price
• num Column: it should be the number of Items, the Quantity
• tot Column: the Total value represented by [Unit Price] * [Quantity]

Since the third Column's (Total) Value is obtained multiplying the price (Unit Price) by the number (Quantity) of items, this calculation can transferred to a DataTable.Expression: this operation is fixed (it always refers to the same Columns, which always have the same Value Type).

DataTable.Columns["Total"].Expression = "[Unit Price] * [Quantity]";

In your code, of course, you use the names you have assigned to your Columns:

[Your DataTable].Columns["tot"].Expression = "[price] * [num]";

? Note that, since you are expressing a Price (currency), you probably want to use a Decimal type to represent that Value, not an integer type. The same applies to the Total Column.

The Expression can be assigned right after you have loaded the DataTable, or after you have assigned it to the DataGridView.DataSource property.
Assigning the property, the DataSourceChanged event is raised:

private void myGrid_DataSourceChanged(object sender, EventArgs e)
{
    if (myGrid.DataSource is DataTable dt && dt.Columns.IndexOf("tot") >= 0) {
        dt.Columns["tot"].Expression = "[num] * [price]";
    }
}

? The Cells don't need to contain a value, both [Unit Price] and [Quantity] can be null, no exception is thrown. You can of course use the DataGridView NewRow to add a new Row to the DataTable.

To change the value of the Total Column right after either the Unit Price or the Quantity values are changed, subscribe to the CellEndEdit or CellValueChanged events and Validate the edit. In this case, you don't need to press Enter or change Row to see the new calculated value appear, just move the cursor to another adjacent Cell (Excel style).

private void myGrid_CellEndEdit(object sender, DataGridViewCellEventArgs e) => Validate();