//add passenger private void btnAdd_Click(object sender, EventArgs e) { //passenger and seat objects passenger = new Passenger(); seat = new Seat(); //see what seat column was selected (A, B, C, or D) var checkedButton = Controls.OfType <RadioButton>() .FirstOrDefault(r => r.Checked); //validate input. Valid name, seat row and selection of seat column is required if (!passenger.IsValidPassenger(txtName.Text) || cmbSeatRow.SelectedIndex == -1 || checkedButton == null) { MessageBox.Show("Please enter valid name and seat", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } //check if plane is full. If it is, place passenger on waitig list if (seat.IsPlaneFull()) { var msg = MessageBox.Show("The plane is full. Add passenger on waiting list?", "Plane Full", MessageBoxButtons.YesNo, MessageBoxIcon.Information); if (msg == DialogResult.No) { return; } using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString)) { conn.Open(); command = new OleDbCommand("INSERT INTO Passengers (PassengerName, PassengerOnWaitingList) " + "VALUES (@passengerName, true)", conn); command.Parameters.Add(new OleDbParameter("PassengerName", txtName.Text)); command.ExecuteNonQuery(); command = new OleDbCommand("INSERT INTO PassengerSeats (PassengerID, SeatID) " + "SELECT MAX(PassengerID), 0 from Passengers", conn); command.ExecuteNonQuery(); MessageBox.Show("Passenger " + txtName.Text + " was added to the waiting list", "Waiting List", MessageBoxButtons.OK, MessageBoxIcon.Information); } return; } //check if seat is taken. If it is, exit so the user can select a different seat if (seat.IsSeatAlreadyTaken(cmbSeatRow.SelectedItem.ToString(), checkedButton.Text)) { MessageBox.Show("The seat is already taken. Please select different seat", "Seat Taken", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } //if everything is OK add passenger and seat to database along with the assigned seat //Insert new passenger to Passengers db. //Update seat to Taken in Seats db. //Insert Passenger and Seat ID in PassengerSeats db using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString)) { conn.Open(); command = new OleDbCommand("INSERT INTO Passengers (PassengerName) " + "VALUES (@passengerName)", conn); command.Parameters.Add(new OleDbParameter("PassengerName", txtName.Text)); command.ExecuteNonQuery(); command = new OleDbCommand("UPDATE Seats SET IsTaken = true WHERE " + "SeatRow = @SeatRow AND SeatColumn = @SeatColumn", conn); command.Parameters.Add(new OleDbParameter("SeatRow", cmbSeatRow.Text)); command.Parameters.Add(new OleDbParameter("SeatColumn", checkedButton.Text)); command.ExecuteNonQuery(); command = new OleDbCommand("INSERT INTO PassengerSeats (SeatID, PassengerID) " + "Select Seats.SeatID, (SELECT MAX(PassengerID) FROM Passengers) " + "FROM Seats WHERE Seats.SeatRow = @SeatRow AND Seats.SeatColumn = @SeatColumn", conn); command.Parameters.Add(new OleDbParameter("SeatRow", cmbSeatRow.Text)); command.Parameters.Add(new OleDbParameter("SeatColumn", checkedButton.Text)); command.ExecuteNonQuery(); MessageBox.Show("Passenger has been added", "Added Passenger", MessageBoxButtons.OK, MessageBoxIcon.Information); PopulateAirplane(); } }
private void btnAdd_Click(object sender, EventArgs e) { passenger = new Passenger(); seat = new Seat(); var checkedButton = gbRadios.Controls.OfType <RadioButton>().FirstOrDefault(r => r.Checked); if (!passenger.IsValidPassenger(txtName.Text) || cmbSeatRow.SelectedIndex == -1 || checkedButton == null) { MessageBox.Show("Please Enter Valid Name and Seat", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (seat.IsPlaneFull()) { var msg = MessageBox.Show("Plane is Full.\r\nAdd Passenger on waiting list?", "Plane is Full", MessageBoxButtons.YesNo, MessageBoxIcon.Information); if (msg == DialogResult.No) { return; } else { using (var conn = new OleDbConnection(DatabaseObjects.connectionString)) { conn.Open(); command = new OleDbCommand("INSERT INTO Passengers (PassengerName, PassengerOnWaitingList)" + " Values (@passengerName,true)", conn); command.Parameters.Add(new OleDbParameter("passengerName", txtName.Text)); command.ExecuteNonQuery(); command = new OleDbCommand("INSERT INTO PassengerSeats (PassengerID, SeatID)" + "" + "SELECT MAX(PassengerID),0 FROM Passengers", conn); command.ExecuteNonQuery(); MessageBox.Show($"Passenger {txtName.Text} was added to the waiting list", "Waiting List", MessageBoxButtons.OK, MessageBoxIcon.Information); } return; } } if (seat.IsSeatAlreadyTaken(cmbSeatRow.SelectedItem.ToString(), checkedButton.Text)) { MessageBox.Show($"The seat is already taken. Please select different seat.", "Seat Taken", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } using (var conn = new OleDbConnection(DatabaseObjects.connectionString)) { conn.Open(); command = new OleDbCommand("INSERT INTO Passengers (PassengerName)" + " Values (@passengerName)", conn); command.Parameters.Add(new OleDbParameter("passengerName", txtName.Text)); command.ExecuteNonQuery(); command = new OleDbCommand("UPDATE Seats SET IsTaken = true WHERE SeatRow = @seatRow AND SeatColumn = @seatColumn", conn); command.Parameters.Add(new OleDbParameter("seatRow", cmbSeatRow.Text)); command.Parameters.Add(new OleDbParameter("seatColumn", checkedButton.Text)); command.ExecuteNonQuery(); command = new OleDbCommand("INSERT INTO PassengerSeats (SeatID,PassengerID) " + "SELECT Seats.SeatID, (SELECT MAX(PassengerID) FROM Passengers) " + "FROM Seats WHERE Seats.SeatRow = @seatRow AND Seats.SeatColumn = @seatColumn" , conn); command.Parameters.Add(new OleDbParameter("seatRow", cmbSeatRow.Text)); command.Parameters.Add(new OleDbParameter("seatColumn", checkedButton.Text)); command.ExecuteNonQuery(); MessageBox.Show($"Passenger has been added.", "Seat Taken", MessageBoxButtons.OK, MessageBoxIcon.Information); PopulateAirplane(); } }