UPDATE query doesn't update in C# while it works in Access

Question

I'm trying to update a column when the value in the column is higher as 0, otherwise it INSERTS a new row.

The UPDATE query is tested in Access first and it worked, tried it in C# and it doens't.

Don't know where to look for. Any help/tips?

Reservation selection = (Reservation)lstbReservations.SelectedItem;
connection.Open();

command.CommandText = @"SELECT Service_Overzicht.OverzichtID, Service.ServiceID, Service.Beschrijving,
                        IIf(Service_Overzicht.Aantal Is Null,0,Service_Overzicht.Aantal) AS Aantal, Service.Prijs, Service.Categorie
                        FROM Service LEFT JOIN (SELECT * FROM Service_Overzicht WHERE OverzichtID = [@waarde])
                        AS OSF ON Service.ServiceID = OSF.ServiceID;";

command.Connection = connection;

command.Parameters.Add(new OleDbParameter("@waarde", OleDbType.Integer));
command.Parameters["@waarde"].Value = selection.Reserveringsnummer;

reader = command.ExecuteReader();
while(reader.Read())
{
    if(reader.GetString(2) == "Douche Muntjes")
    {
        if(reader.GetInt32(3) == 0)
        {
            command1.CommandText = @"INSERT INTO Service_Overzicht (OverzichtID, ServiceID, Aantal) VALUES (@resID, @sID, @aantal)";
        }
        else
        {
            command1.CommandText = @"UPDATE Service_Overzicht
                                    SET Aantal = @aantal
                                    WHERE OverzichtID = @resID AND ServiceID = @sID";
        }

        command1.Connection = connection;

        command1.Parameters.Add(new OleDbParameter("@resID", OleDbType.Integer));
        command1.Parameters["@resID"].Value = selection.Reserveringsnummer;

        command1.Parameters.Add(new OleDbParameter("@sID", OleDbType.Integer));
        command1.Parameters["@sID"].Value = 1;

        command1.Parameters.Add(new OleDbParameter("@aantal", OleDbType.Integer));
        command1.Parameters["@aantal"].Value = nudShower.Value;

        reader1 = command1.ExecuteReader();
        if(reader1.HasRows)
        {
            MessageBox.Show("Something went wrong!");
        }
        else
        {
            MessageBox.Show("Services updated");
        }
    }

Are you getting error or something like ? Please clarify the problem. — lucky, Dec 04 '17 at 12:55
Well I worked with it with other UPDATE queries and those worked, so I think this one should work too. Also, I dont know an other method for that, kinda new to this :/ — Menno Wellner, Dec 04 '17 at 12:56
It is not only the problem of calling ExecuteReader on an update/insert query but also the fact that in OleDb parameters are POSITIONAL. You should insert your parameters following the order in which they appear in the query (so two different ways for INSERT and UPDATE) — Steve, Dec 04 '17 at 12:56
The following line returns the number of rows effected : reader = command.ExecuteReader();. So if you do an Update and zero is returned it means the row does not exist in database so you need to an insert. If you do an insert and returns zero then you need to do an update. When doing update or insert you need to use ExecuteNonQuery (not ExecuteReader). — jdweng, Dec 04 '17 at 12:59
@Steve, the order of the parameters were the problem, it is fixed now, thanks! Will also look into the ExcuteReader stuff, thanks all for quick respons! — Menno Wellner, Dec 04 '17 at 13:00

Steve · Answer 1 · 2017-12-04T13:13:32.173

In OleDb the parameters are not retrieved looking at their name but at their position. You need to put the parameters in the collection following the order in which they appear in the query.

Inverting the order of the fields in the INSERT query allows you to insert the parameters in the same order both for INSERT and UPDATE.

if(reader.GetInt32(3) == 0)
{
    command1.CommandText = @"INSERT INTO Service_Overzicht 
                            (Aantal, OverzichtID, ServiceID) 
                            VALUES ( @aantal, @resID, @sID)";
}
else
{
    command1.CommandText = @"UPDATE Service_Overzicht
                            SET Aantal = @aantal
                            WHERE OverzichtID = @resID AND ServiceID = @sID";
}
command1.Parameters.Add("@aantal", OleDbType.Integer).Value = nudShower.Value;        
command1.Parameters.Add("@resID", OleDbType.Integer).Value = selection.Reserveringsnummer;
command1.Parameters.Add("@sID", OleDbType.Integer).Value = 1;
command1.Connection = connection;

Finally an UPDATE/INSERT/DELETE query is executed using the ExecuteNonQuery call and looking at the return value that represent the number of rows changed.
Calling ExecuteReader also works, because it executes the query passed, but it tries to build an OleDbDataReader to read the supposed returned set of records.
You don't have records to read back with an update query but just a number.

  int rows = command1.ExecuteNonQuery();
  if(rows != 0) 
      ....success....

UPDATE query doesn't update in C# while it works in Access

1 Answers1