//Update button listener
private void UpdateButton_Click(object sender, EventArgs e)
{
//gets the values from the setLabel(ID), name and surname
Update(Convert.ToInt32(IDLabel.Text), NameTextBox.Text, SurnameTextBox.Text, PhoneNumberTextBox.GetPhoneNumber());
}
//Inserts the driver in the database
private void InsertDriver()
{
//Gets the textbox values
string firstName = FirstNameTextBox.Text;
string lastName = LastNameTextBox.Text;
string phoneNumber = PhoneNumberTextBox.GetPhoneNumber();
//Checks if First Name textbox and Last Name textbox are empty
if (FirstNameTextBox.Text == "" || LastNameTextBox.Text == "")
{
MessageBox.Show("Please insert First Name and Last Name.");
return;
}
//Checks if the phoneNumber length is == 11
if (phoneNumber.Length != 11)
{
MessageBox.Show("Invalid Phone number. Please try again.");
PhoneNumberTextBox.Clear();
return;
}
//The first query inserts the Driver's name and surname
String query = "INSERT INTO `driver`(`first_Name`, `last_Name`, `Phone_Number`) VALUES('" + firstName + "','" + lastName + "','" + phoneNumber + "')";
var Qualification_ID = new List<int>();
try
{
MySqlDataReader reader = null;
databaseConnection.Open();
MySqlCommand getID = databaseConnection.CreateCommand();
getID.CommandText = query;
getID.ExecuteNonQuery();
//Get last inserted ID. Using this ID we insert the driver's qualifications
long ID = getID.LastInsertedId;
MessageBox.Show("Driver successfully added. Name: " + firstName + " " + lastName + ", " + phoneNumber + " ID: " + ID + ".");
databaseConnection.Close();
foreach (DataGridViewRow row in AddQualificationsGridView.Rows)
{
if (!row.IsNewRow)
{
for (int i = 0; i < AddQualificationsGridView.Columns.Count; i++)
{
String Qualification = Convert.ToString(row.Cells[i].Value);
//The second query selects the qualifications
query = "SELECT `Qualification_ID`FROM `qualification` WHERE `Qualification` LIKE '" + Qualification + "'";
databaseConnection.Open();
using (MySqlCommand cmd = new MySqlCommand(query, databaseConnection))
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
Qualification_ID.Add((int)reader["Qualification_ID"]);
}
databaseConnection.Close();
foreach (int e in Qualification_ID)
{
databaseConnection.Open();
MySqlCommand cmd2 = databaseConnection.CreateCommand();
//The third query inserts the qualifications and assigns them to that driver's ID
cmd2.CommandText = "INSERT INTO `assigned_qualification` (`id`, `Qualification_ID`) VALUES(@id, @qualification_id)";
cmd2.Parameters.AddWithValue("@id", ID);
cmd2.Parameters.AddWithValue("@qualification_id", Qualification_ID[0]);
cmd2.ExecuteNonQuery();
databaseConnection.Close();
}
Qualification_ID.Clear();
}
}
}
}
}
catch (Exception e)
{
MessageBox.Show("Add button error: " + e.Message);
}
}