private void btmOrder_OrderModify_Click(object sender, EventArgs e) { if (!OrderUtils.CheckOrderCottageBookDate(Convert.ToInt32(tbOrder_ModifyCottageID.Text), Convert.ToInt32(lbOrder_ModifyOrderID.Text), dtpOrder_ModifyStartDate.Text, dtpOrder_ModifyEndDate.Text)) //Check is cottage free on selected dates { return; } DialogResult res = MessageBox.Show("Haluatko varmasti tallentaa muokatut tiedot?", "Muokkaa varaus", MessageBoxButtons.YesNo, MessageBoxIcon.Information); if (res == DialogResult.Yes) { string queryServices = "START TRANSACTION; "; foreach (DataGridViewRow row in dgvOrderServices.Rows) // Services modify { ConnectionUtils.OpenConnection(); MySqlCommand checkRow = new MySqlCommand("SELECT * FROM varauksen_palvelut WHERE varaus_id = '" + lbOrder_ModifyOrderID.Text + "' AND palvelu_id = '" + row.Cells[0].Value.ToString() + "'", ConnectionUtils.connection); MySqlDataReader reader = checkRow.ExecuteReader(); if (reader.HasRows && Convert.ToInt32(row.Cells["kpl"].Value) != 0) // If service already in database { reader.Read(); if (Convert.ToInt32(reader["lkm"]) != Convert.ToInt32(row.Cells["kpl"].Value)) { queryServices += "UPDATE varauksen_palvelut SET lkm='" + row.Cells["kpl"].Value.ToString() + "' " + "WHERE varaus_id = '" + lbOrder_ModifyOrderID.Text + "' AND palvelu_id = '" + row.Cells[0].Value.ToString() + "'; "; } } else if (!reader.HasRows && Convert.ToInt32(row.Cells["kpl"].Value) != 0) // If new service { queryServices += "INSERT INTO vn.varauksen_palvelut(varaus_id, palvelu_id, lkm) " + "VALUES(" + lbOrder_ModifyOrderID.Text + ", " + row.Cells["ID"].Value.ToString() + ", " + row.Cells["kpl"].Value.ToString() + "); "; } else if (reader.HasRows && Convert.ToInt32(row.Cells["kpl"].Value) == 0) // if service set to 0 { queryServices += "DELETE FROM varauksen_palvelut WHERE varaus_id = '" + lbOrder_ModifyOrderID.Text + "' AND palvelu_id = '" + row.Cells[0].Value.ToString() + "'; "; } ConnectionUtils.CloseConnection(); } queryServices += "COMMIT;"; if (queryServices != "START TRANSACTION; COMMIT;") // check if services not changet { try { ConnectionUtils.OpenConnection(); MySqlCommand command1 = new MySqlCommand(queryServices, ConnectionUtils.connection); command1.ExecuteNonQuery(); // Add/EDIT/REMOVE services ConnectionUtils.CloseConnection(); } catch { MessageBox.Show(queryServices); } } //Update order's start- and(or) enddate string query = "START TRANSACTION; " + "UPDATE varaus " + "SET varattu_alkupvm='" + dtpOrder_ModifyStartDate.Text + " 16:00:00',varattu_loppupvm='" + dtpOrder_ModifyEndDate.Text + " 12:00:00'" + "WHERE varaus_id=" + lbOrder_ModifyOrderID.Text + "; " + "COMMIT;"; ConnectionUtils.OpenConnection(); MySqlCommand command2 = new MySqlCommand(query, ConnectionUtils.connection); command2.ExecuteNonQuery(); ConnectionUtils.CloseConnection(); Close(); } }