//edit the passenger record
private void btnEdit_Click(object sender, EventArgs e)
{
//Validate input from passenger
//check that passenger name has been entered
if (txtName.Text.Trim().Equals(""))
{
MessageBox.Show("Passenger name is required.", "Invalid Input",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Passenger cant have a seat and be on waiting list
if (chbOnList.Checked && (cmbRow.SelectedIndex > 0 || cmbColumn.SelectedIndex > 0))
{
MessageBox.Show("Passenger cannot be on a waiting list and have a seat assigned.", "Invalid Input"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Passenger must have a seat or on waiting list
if (!chbOnList.Checked && (cmbRow.SelectedIndex <= 0 || cmbColumn.SelectedIndex <= 0))
{
MessageBox.Show("Passenger must have a seat assigned or be on the waiting list.", "Invalid Input"
, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// Update passenger record
using (var con = new SqlConnection(DBObjects.conString))
{
con.Open();
command = new SqlCommand("SELECT SeatID, IsTaken FROM Seats WHERE " +
"SeatRow = @SeatRow AND SeatColumn = @SeatColumn", con);
command.Parameters.Add(new SqlParameter("SeatRow", cmbRow.SelectedItem));
command.Parameters.Add(new SqlParameter("SeatColumn", cmbColumn.SelectedItem));
reader = command.ExecuteReader();
var newSeatID = 0;
bool newIsTaken = false;
while (reader.Read())
{
newSeatID = Convert.ToInt32(reader["SeatID"]);
newIsTaken = Convert.ToBoolean(reader["IsTaken"]);
}
//check if only the name is being updated.
//If not, exit because the user needs to pick a different seat
int oldID = 0;
if (txtSeatID.Text.Equals(""))
{
oldID = 0;
}
else
{
oldID = Convert.ToInt32(txtSeatID.Text);
}
if (!txtSeatID.Equals(""))
{
if (newSeatID != oldID && newIsTaken)
{
MessageBox.Show("Seat is already taken", "Seat Taken", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
return;
}
}
else
{
newSeatID = 0;
}
//update passenger's name
command = new SqlCommand("UPDATE Passengers SET PassengerName = @PassengerName, " +
"PassengerOnWaitingList = @OnWaitingList WHERE PassengerID = @PassengerID", con);
Methods.AddParameters(command, "PassengerName", txtName);
Methods.AddParameters(command, "OnWaitingList", chbOnList);
Methods.AddParameters(command, "PassengerID", txtPassID);
command.ExecuteNonQuery();
//make original seat available
command = new SqlCommand("UPDATE Seats SET IsTaken = 0 WHERE " +
"seatID = @seatID", con);
Methods.AddParameters(command, "seatID", txtSeatID);
command.ExecuteNonQuery();
//make new seat taken
command = new SqlCommand("UPDATE Seats SET IsTaken = 1 WHERE " +
"seatID = @seatID", con);
Methods.AddParameters(command, "seatID", newSeatID);
command.ExecuteNonQuery();
//update old seatID with the new one
command = new SqlCommand("UPDATE PassengerSeats SET SeatID = @SeatID WHERE " +
"PassengerID = @PassengerID", con);
Methods.AddParameters(command, "SeatID", newSeatID);
Methods.AddParameters(command, "PassengerID", txtPassID);
command.ExecuteNonQuery();
MessageBox.Show("Record has been updated", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
}