Update statement does nothing

Question

When I enter a number in the ChbBeds_numericUpDown and click on the "Update" button, it says "Data Updated", but nothing changes in the database

private void ChbUp_button_Click(object sender, EventArgs e)
{
    try 
    {
        string statement = "UPDATE ChamberXPavilions SET Beds count = @beds_count WHERE Pav_name = @pav_name AND Chamber_number = @chamber_number";

        cmd = new OleDbCommand(statement, conn);

        cmd.Parameters.AddWithValue("@pav_name", Chbpav_comboBox.Text);
        cmd.Parameters.AddWithValue("@chamber_number", Chb_numericUpDown.Value);
        cmd.Parameters.AddWithValue("@beds_count", ChbBeds_numericUpDown.Value);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        MessageBox.Show("Data updated");
        showdata();
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Is the SQL statement wrong ?

Does the column "Beds count" really contain a space character? Then you need to enclose it in square brackets. — Klaus Gütter, Jan 08 '23 at 13:56
Does this answer your question? [Oledb update-command with parameters not working on access-db](https://stackoverflow.com/questions/52648299/oledb-update-command-with-parameters-not-working-on-access-db) — Klaus Gütter, Jan 08 '23 at 14:03
Update only works if the row(s) already exist in the database. If the rows do not exist than you have to use INSERT. cmd.ExecuteNonQuery() returns the number of rows changed. When using UPDATE and you get zero rows changed than use INSERT. Or do the opposite. First try Insert and if you get zero rows changed than rows already exist and then you have to try UPDATE. — jdweng, Jan 08 '23 at 14:11
@KlausGütter It's Beds_count in my code, its just just spellcheck correction — paramicia, Jan 08 '23 at 14:15

score 1 · Accepted Answer · answered Jan 08 '23 at 14:14

Contrary to SQL Server, the OleDB provider for MS Access does NOT work with named parameters - instead, it uses positional parameters.

In your case, you have a SQL statement

UPDATE ChamberXPavilions 
SET Beds count = @beds_count 
WHERE Pav_name = @pav_name AND Chamber_number = @chamber_number

so you need to also provide the parameters in the same order - first @beds_count, then @pav_name and finally @chamber_number.

So try this for providing the parameter values:

cmd = new OleDbCommand(statement, conn);

cmd.Parameters.AddWithValue("@beds_count", ChbBeds_numericUpDown.Value);    
cmd.Parameters.AddWithValue("@pav_name", Chbpav_comboBox.Text);
cmd.Parameters.AddWithValue("@chamber_number", Chb_numericUpDown.Value);

Now, your UPDATE statement should get the proper values and should now work

thanks it worked, you guys are life savers in this website keep it up — paramicia, Jan 08 '23 at 14:23

Update statement does nothing

1 Answers1