Update single cell in datagrid WPF

Question

I have a datagrid in my WPF application. In one column I have an int (column name = Amount).

So for example there will be a number "4" in the cell. I can edit "4" in the DataGrid to "3".

After editing, I will push the button "Update" so my database column Amount will be updated.

It is working, but it update all the cells in the column Amount to the Id number of the chosen row.

This is my code in the xaml.cs file:

 private void Update(object sender, RoutedEventArgs e)
 {
        DataRowView o = (DataRowView)g2.SelectedItem;
        int Amount = Convert.ToInt32(o.Row.ItemArray[0]);

        try
        {
            const string query = @"UPDATE [Stock] SET [STOCK].Amount = @Aantal;";
           
            using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=\"...."))
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.Add("@Amount", SqlDbType.Int).Value = Amount;
                con.Open();
                cmd.ExecuteNonQuery();
            }

            MessageBox.Show("Update complete");
            binddatagrid();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error occurred:\r\n" + ex.Message);
        }
}

What am I doing wrong?

Answer 1

Your database query is updating the Amount column in every row in the [Stock] table. You need to add a WHERE clause to your database query so that you only update the [Stock] row in the database that corresponds to the selected row in the DataGrid.

I don't know what your database schema looks like, but I'm assuming that the [Stock] table has an Id column. If so, the query might look something like this:

UPDATE [Stock] SET [Stock].Amount = @Anatal WHERE [Stock].Id = @Id

Notice that the query now has a second parameter, @Id. That means that you'll need to get the Id from the selected row in much the same way that you're currently getting the Amount.

int id = Convert.ToInt32(o.Row.ItemArray[1]);

I used o.Row.ItemArray[1], but I don't know what index the Id will actually be stored at. You'll have to use that index to get the correct Id.

Since your query has a second parameter, you also need to add it to the Parameters collection of the SqlCommand instance. Just like how you're doing with Amount.

cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;