Edit in datagridview successful, but it does not update the certain column

Question

i already can edit data in the database. But i am having a problem, when i edit and update Quantity column in the datagridview, it supposed to update the Total column in datagridview based on Quantity * SubTotal , but it is update only the Quantity column, not Price.

Where do i did wrong?

Here is the screenshot:

As you can see in the rows "1", the Quantity is 100 and the SubTotal is 10000, so the Total will be 1000000. But in the rows "2", the Quantity before i edit in the datagridview, the Quantity is 100 and also the Total is 2000000, when i change the Quantity to the 500, the Total still 2000000, the Total supposed to be 10000000.

Where do i did wrong?

Here is the code (i post the code as necessary as needed):

private void Updated(object sender, EventArgs e)
        {
            DataTable _dt = (DataTable)dataGridView1.DataSource;

            if (_dt.DefaultView.Count > 0)
            {
                int rowNum = dataGridView1.CurrentRow.Index;
                string productCode = Convert.ToString(_dt.DefaultView[rowNum]["ProductCode"]);
                int quantity = Convert.ToInt32(_dt.DefaultView[rowNum]["Quantity"]);
                int price = Convert.ToInt32(_dt.DefaultView[rowNum]["SubTotal"]);
                int _price = Convert.ToInt32(_dt.DefaultView[rowNum]["Total"]);

                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    string _commandSelect = "SELECT [Quantity], [SubTotal], [Total] FROM [TransRecord] WHERE [ProductCode] = @ProductCode";
                    string commandUpdate = "UPDATE [Record] SET [Quantity] = @Quantity, [SubTotal] = @SubTotal, [Total] = @Total WHERE [ProductCode] = @ProductCode";
                    string _commandUpdate = "UPDATE [TransRecord] SET [Quantity] = @Quantity, [SubTotal] = @SubTotal, [Total] = @Total WHERE [ProductCode] = @ProductCode";

                    conn.Open();

                    using (OleDbCommand cmdUpdate = new OleDbCommand(_commandUpdate, conn))
                    using (OleDbCommand _cmdSelect = new OleDbCommand(_commandSelect, conn))
                    using (OleDbCommand _cmdUpdate = new OleDbCommand(commandUpdate, conn))
                    {
                        _cmdSelect.Parameters.Add("@ProductCode", System.Data.OleDb.OleDbType.VarChar);
                        _cmdSelect.Parameters["@ProductCode"].Value = productCode;

                        cmdUpdate.Parameters.Add("@Quantity", System.Data.OleDb.OleDbType.Integer);

                        _cmdUpdate.Parameters.Add("@Quantity", System.Data.OleDb.OleDbType.Integer);

                        cmdUpdate.Parameters.Add("@SubTotal", System.Data.OleDb.OleDbType.Integer);

                        _cmdUpdate.Parameters.Add("@SubTotal", System.Data.OleDb.OleDbType.Integer);

                        cmdUpdate.Parameters.Add("@Total", System.Data.OleDb.OleDbType.Integer);

                        _cmdUpdate.Parameters.Add("@Total", System.Data.OleDb.OleDbType.Integer);

                        using (OleDbDataReader dReader = _cmdSelect.ExecuteReader())
                        {
                            while (dReader.Read())
                            {
                                cmdUpdate.Parameters.Add("@ProductCode", System.Data.OleDb.OleDbType.VarChar);
                                _cmdUpdate.Parameters.Add("@ProductCode", System.Data.OleDb.OleDbType.VarChar);

                                cmdUpdate.Parameters["@ProductCode"].Value = productCode;
                                cmdUpdate.Parameters["@Quantity"].Value = quantity;
                                cmdUpdate.Parameters["@SubTotal"].Value = price;
                                cmdUpdate.Parameters["@Total"].Value = _price;

                                _cmdUpdate.Parameters["@ProductCode"].Value = productCode;
                                _cmdUpdate.Parameters["@Quantity"].Value = quantity;
                                _cmdUpdate.Parameters["@SubTotal"].Value = price;
                                _cmdUpdate.Parameters["@Total"].Value = _price;

                                int numberOfRows = _cmdUpdate.ExecuteNonQuery();
                                int _numberOfRows = cmdUpdate.ExecuteNonQuery();
                            }

                            dReader.Close();
                        }
                    }

                    conn.Close();
                }
        }

private void UpdatePrice(object sender, EventArgs e)
        {
            if (numericTextBox1.TextLength >= 6)
            {
                decimal quantity = Convert.ToInt32(this.numericUpDown1.Value);
                decimal price = Convert.ToDecimal(this.numericTextBox2.Text);
                int total = Convert.ToInt32(quantity * price);

                if (numericUpDown1.Value > 0)
                {
                    this.numericTextBox3.Text = total.ToString();
                }
            }
        }

The Total is working when it is not in the datagridview, it is in here (shown as screenshot):

But when it goes to the datagridview and when i change the Quantity, it does not update the Total based on SubTotal * Quantity

I appreciate your help and answer!

Thank you very much!

Answer 1

In order for the grid to update with the correct result, you need to do two things: the first is to update the Total in the dataset. The second is to rebind the grid to the updated dataset. The most common way to do that is to set the datasource to null and then back to your dataset. I would also suggest to look at your sql statements, in particular cmdSelect. It's never used.
Edit:
Take a look at EditMode and the CellEndEdit event. It should be good start to start editing the grid. The EditMode specifies when to edit the cell and the cellEndEdit event can be used to update the data in your grid.