CODEWITHSUNDEEP
c#ms-access
Menno Wellner
Menno Wellner

Reputation: 1

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

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

Upvotes: 0

Views: 71

Answers (1)

Steve
Steve

Reputation: 216363

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....

Upvotes: 2

Related Questions