//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;
}
}
}