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();
        }

    }

id = 7, omschrijving = douche muntjes, prijs = 0,5, catagorie = faciliteiten — Jelle Taal, Nov 01 '17 at 09:01
Define your command type query.CommandType = CommandType.Text and try to write your query in single line `query.CommandText = @"UPDATE prijslijst SET omschrijving = @omschrijving, prijs = @prijs, catagorie = @catagorie WHERE id = @id";` — cagri, Nov 01 '17 at 09:01
@JelleTaal Sounds like you don't have record with id=7 in DB. Check your connection string to be sure that you are looking in correct DB. Code seems to be ok, especially because there is no exception thrown. Search for the error in your current setup(wrong ID, wrong DB, etc..) — 3615, Nov 01 '17 at 09:25
Try replacing your execute statement as per: `int ret = query.ExecuteNonQuery();` `MessageBox.Show("succesvol gewijzigd: "+ret.ToString());` The point is that if the update is really happening, then you should get a return value `> 0`. It might get you closer to what is wrong. — Peter Abolins, Nov 01 '17 at 09:30
@3615 I tried a insert query to test if the connection string works and it does so the connection string is not the problem. and i looked into my DB and there definitely is a record with an id of 7 i double checked that in my database — Jelle Taal, Nov 01 '17 at 09:33
@PeterAbolins thanks I did this and it returns a 0 so somthing is not working correctly I don't know what — Jelle Taal, Nov 01 '17 at 09:36
OleDb provider may require parameters added in the same order they appear in the query, unlike MSSQL provider which assigns by name instead of position. — Crowcoder, Nov 01 '17 at 09:46
This might be a redundant question, but the query sent to the DB (with all parameters replaced) is the same one you are copying to the DB directly? — Peter Abolins, Nov 01 '17 at 09:59
@PeterAbolins if you mean that the parameters are the same as in the database than no their difrent except for id however the problem has already been solved I needed to add the parameters in the same order as they appear in the query so omschrijving, prijs, catagory, id in that order — Jelle Taal, Nov 01 '17 at 10:13

score 1 · Accepted Answer · answered Nov 01 '17 at 09:53

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.

nepdev · Answer 2 · 2017-11-01T10:04:10.360

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.

edited Nov 01 '17 at 10:04

answered Nov 01 '17 at 09:53

nepdev

937
1
11
19

1

Okay, have not worded this very well. OleDb does not understand named parameters - see [this post](https://stackoverflow.com/questions/2407685/oledbparameters-and-parameter-names). – nepdev Nov 01 '17 at 10:03

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

2 Answers2