simple update query doesn't work, but when i put it into database itself it does work

Question

When i run my code in the debugger and I hover my mouse over the parameters they do have the right values in them. It just doesn't update my database but when I copy the query and put it into the database it works without a problem.

The parameter values are:

id = 7
omschrijving = douche muntjes
prijs = 0,5
catagorie = faciliteiten

I checked the connection tring by using an insert query and that does add records to my database. And There is an id with the value of 7 in the database.

When I run a insert query or a delete query through my C# code it does work it's just the update statement that doesn't work. If anyone sees the issue please help me.

public static void wijzigprijs(int id, string omschrijving, decimal prijs, string catagorie)
    {
        try
        {
            try
            {
                OleDbConnection verbinding = new OleDbConnection(
                @"Provider=Microsoft.ACE.OLEDB.12.0;
            Data Source=..\..\..\La_Rustique.accdb;
            Persist Security Info=False;");
                verbinding.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            OleDbCommand query = new OleDbCommand();
            query.CommandText = @"UPDATE prijslijst
                                SET omschrijving = @omschrijving, 
                                    prijs = @prijs, 
                                    catagorie = @catagorie
                                WHERE id = @id";

            query.Parameters.Add(new OleDbParameter("@id", OleDbType.Integer));
            query.Parameters["@id"].Value = id;
            query.Parameters.Add(new OleDbParameter("@omschrijving", OleDbType.VarChar));
            query.Parameters["@omschrijving"].Value = omschrijving;
            query.Parameters.Add(new OleDbParameter("@prijs", OleDbType.Decimal));
            query.Parameters["@prijs"].Value = prijs;
            query.Parameters.Add(new OleDbParameter("@catagorie", OleDbType.VarChar));
            query.Parameters["@catagorie"].Value = catagorie;

            query.Connection = verbinding;
            query.ExecuteNonQuery();
            MessageBox.Show("succesvol gewijzigd");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            verbinding.Close();
        }

    }

Answer 1

I have never seen OleDB queries written in the above syntax. To state it differently: OleDB simply does not use named parameters, it uses the position only.

Try to change your SQL statement like this:

query.CommandText = @"UPDATE prijslijst
                            SET omschrijving = ?, 
                                prijs = ?, 
                                catagorie = ?
                            WHERE id = ?";

and then add the parameters in sequence of above in the code below that.

Answer 2

EDIT UPDATE

Look at this topic. Here he explains how you should use variables with OleDbCommand

Variables with OleDbCommand

This is how you typically will do it when using SQLCommand parameters: I know this doesnt answer your questions quite, but when i use SQLCommand i use this code whenever i want to update or insert with variables:

   string query = @"UPDATE prijslijst
                            SET omschrijving = @omschrijving, 
                                prijs = @prijs, 
                                catagorie = @catagorie
                            WHERE id = @id";

                        SqlCommand cmd = new SqlCommand(query, connDatabase);

                        cmd.Parameters.Add("@id", SqlDbType.integer).Value = 7; 
                        cmd.ExecuteNonQuery();
                        connDatabase.Close();

So you should be able to do the samething. Hope this will help you.