Data not updating to the database c# datagridview

Question

i wanted to update the database using datagridview, i already can edit it, but it didn't update to the database.

How do i fix that?

Here is my code:

There are buttons in my forms, one of it is "Edit" button, which is refer to "EditingDatabase" function, the other one is "OK" button, which is updating the data to the database (the code can be found in this below code, i mention it at the code).

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

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

                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    string commandSelect = "SELECT [Quantity], [Price] FROM [Table] WHERE [ProductCode] = @ProductCode";
                    string commandUpdate = "UPDATE [Table] SET [Quantity] = @Quantity, [Price] = @Price WHERE [ProductCode] = @ProductCode";

                    conn.Open();

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

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

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

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

                        using (OleDbDataReader dReader = _cmdSelect.ExecuteReader())
                        {
                            while (dReader.Read())
                            {
                                _cmdUpdate.Parameters["@ProductCode"].Value = productCode;
                                _cmdUpdate.Parameters["@Quantity"].Value = quantity;
                                _cmdUpdate.Parameters["@Price"].Value = price;

                                int numberOfRows = _cmdUpdate.ExecuteNonQuery();
                            }

                            dReader.Close();
                        }
                    }

                    conn.Close();
                }

                if (_choice.comboBox1.Text == "English") // THIS IS WHERE THE "OK" BUTTON FUNCTION RUNS
                {
                    System.Media.SoundPlayer _sounds = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Exclamation.wav");
                    _sounds.Play();
                    MessageBox.Show("Updated Successfully!", "Updated");

                    ShowButtons(sender, e);

                    DisableColumnEdited(sender, e);
                }
            }

            else
            {
                if (_choice.comboBox1.Text == "English")
                {
                    System.Media.SoundPlayer _sounds = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Exclamation.wav");
                    _sounds.Play();
                    MessageBox.Show("There is no Data in the Selected Row!", "Error");
                    return;
                }
            }
        }

Edited

This below is the screenshot of my datagridview in the program, the datagridview are connected to the database table named Table:

Thank you.

I appreciate your answer!

Answer 1

Wrong syntax in the UPDATE statement. It should be

   string query = "UPDATE [Table] SET [Quantity] = @Quantity, [Price] = @Price " + 
                  "WHERE [ProductCode] = @ProductCode";

Also, you are using OleDb, and OleDb requires that you add the parameters in the same order in which they appear in the command statement, so you need to write

_cmd.Parameters.AddWithValue("@Quantity", quantity);
_cmd.Parameters.AddWithValue("@Price", price);
_cmd.Parameters.AddWithValue("@ProductCode", productCode);

OleDb doesn't support named parameters, in the UPDATE statement you could use a simple question mark as parameter placeholder and name your parameters in the AddWithValue with whatever name you like. However the syntax used (the @Quantity) is also supported.
The important point is: Add the parameters in the order in which they appears.

Finally, to update the database the only command required is

  int numberOfRows = _cmd.ExecuteNonQuery();

The code around the ExecuteNonQuery seems wrong if you expect that that code updates the grid. You are recreating the _adapter variable without any SELECT command....