//Show all passengers private void btnShowPassengers_Click(object sender, EventArgs e) { //Get all passenger information from all 3 tables (will use Inner Join) //place the result into DataTable and display the result in Lookups form //when focus is back, repopulate the list box with updated records using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString)) { conn.Open(); command = new OleDbCommand ("Select p.PassengerID as ID, p.PassengerName as Name, s.SeatRow, s.SeatColumn, " + "p.PassengerOnWaitingList as OnWaitingList " + "FROM (Passengers p " + "inner join PassengerSeats ps on p.PassengerID = ps.PassengerID) " + "inner join Seats s on s.SeatID = ps.SeatID " + "UNION " + "Select p.PassengerID, p.PassengerName, null,null,p.PassengerOnWaitingList " + "FROM Passengers p " + "WHERE p.PassengerOnWaitingList = true " + "ORDER BY s.SeatRow, s.SeatColumn", conn); DataTable dt = new DataTable(); dt.Load(command.ExecuteReader()); PassengerLookup form = new PassengerLookup(dt); form.ShowDialog(); PopulateAirplane(); } }
private void btnShowPassengers_Click(object sender, EventArgs e) { using (var conn = new OleDbConnection(DatabaseObjects.connectionString)) { conn.Open(); command = new OleDbCommand(@" SELECT P.PassengerID AS ID, P.PassengerName AS Name, S.SeatRow, S.SeatColumn, P.PassengerOnWaitingList AS OnWaitingList FROM (Passengers as P INNER JOIN PassengerSeats as PS ON PS.PassengerID = P.PassengerID) INNER JOIN Seats as S ON S.SeatID = PS.SeatID UNION SELECT P.PassengerID, P.PassengerName, NULL, NULL, P.PassengerOnWaitingList FROM Passengers as P WHERE P.PassengerOnWaitingList = TRUE ORDER BY S.SeatRow, S.SeatColumn", conn); DataTable dt = new DataTable(); dt.Load(command.ExecuteReader()); PassengerLookup form = new PassengerLookup(dt); form.ShowDialog(); PopulateAirplane(); } }
//search for passenger private void btnSearchPassenger_Click(object sender, EventArgs e) { //make sure a search string was entered in the text box //Get all the passengers that match the search string. Get all the information from all 3 tables. //Place the result in a DataTable and then display it in Lookups form using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString)) { conn.Open(); if (!txtName.Text.Trim().Equals("")) { command = new OleDbCommand ("Select p.PassengerID as ID, p.PassengerName as Name, s.SeatRow, s.SeatColumn, " + "p.PassengerOnWaitingList as OnWaitingList " + "FROM (Passengers p " + "inner join PassengerSeats ps on p.PassengerID = ps.PassengerID) " + "inner join Seats s on s.SeatID = ps.SeatID " + "WHERE p.PassengerName LIKE @PassengerName " + "UNION " + "Select p.PassengerID, p.PassengerName, null,null,p.PassengerOnWaitingList " + "FROM Passengers p " + "WHERE p.PassengerOnWaitingList = true AND p.PassengerName LIKE @PassengerName " + "ORDER BY s.SeatRow, s.SeatColumn", conn); command.Parameters.Add(new OleDbParameter("PassengerName", "%" + txtName.Text + "%")); DataTable dt = new DataTable(); dt.Load(command.ExecuteReader()); PassengerLookup form = new PassengerLookup(dt); form.ShowDialog(); PopulateAirplane(); } else { MessageBox.Show("Please enter a valid name", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } } }
private void btnSearchPassenger_Click(object sender, EventArgs e) { using (var conn = new OleDbConnection(DatabaseObjects.connectionString)) { conn.Open(); if (!txtName.Text.Trim().Equals("")) { command = new OleDbCommand(@" SELECT P.PassengerID AS ID, P.PassengerName AS Name, S.SeatRow, S.SeatColumn, P.PassengerOnWaitingList AS OnWaitingList FROM (Passengers as P INNER JOIN PassengerSeats as PS ON PS.PassengerID = P.PassengerID) INNER JOIN Seats as S ON S.SeatID = PS.SeatID WHERE P.PassengerName LIKE @passengerName UNION SELECT P.PassengerID, P.PassengerName, NULL, NULL, P.PassengerOnWaitingList FROM Passengers as P WHERE P.PassengerOnWaitingList = TRUE AND P.PassengerName LIKE @passengerName ORDER BY S.SeatRow, S.SeatColumn", conn); command.Parameters.Add(new OleDbParameter("passengerName", "%" + txtName.Text + "%")); DataTable dt = new DataTable(); dt.Load(command.ExecuteReader()); PassengerLookup form = new PassengerLookup(dt); form.ShowDialog(); PopulateAirplane(); } else { MessageBox.Show("Please Enter a Valid Name", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } }