// The loadStaff helper method reads the data from the specified file and copies
// to the staff array.
private void loadStaff(string sql)
{
clsStaff member;
// Clear out the array before handling the file data.
mStaff.Clear();
// Read the data from the specified file.
if (File.Exists(mStaffFile) == false)
{
ShowMessage(mStaffFile + " does not exist. Please open another DB file.");
return;
}
openDatabaseConnection();
mDB.Open();
OleDbCommand cmd;
OleDbDataReader rdr;
try
{
cmd = new OleDbCommand(sql, mDB);
rdr = cmd.ExecuteReader();
while (rdr.Read() == true)
{
// Add the data from the line just read to the next array element, making sure to get the ID.
member = new clsStaff((int)rdr["StaffID"],
(string)rdr["LastName"],
(string)rdr["FirstName"],
(string)rdr["Title"],
(string)rdr["Email"],
(string)rdr["Phone"],
(string)rdr["Fax"],
(string)rdr["Office"]);
mStaff.Add(member);
}
rdr.Close();
}
catch (Exception ex)
{
ShowMessage("There was an unexpected problem: " + ex.Message);
}
finally
{
closeDatabaseConnection();
}
}
// This method removes the selected staff member from the roster
private void btnDelete_Click(object sender, EventArgs e)
{
string sql;
int selectedStaff = lstStaff.SelectedIndex - 2;
// Make sure a valid client was selected.
if (selectedStaff < 0 || selectedStaff >= mStaff.Count)
{
ShowMessage("Please select a valid Staff Member in the listbox.");
lstStaff.SelectedIndex = -1;
return;
}
// Get the ID of the selected client to fill in the input boxes
clsStaff temp = (clsStaff)mStaff[selectedStaff];
// Delete the selected client and remove the corresponding record in the ClientPlan table
try
{
openDatabaseConnection();
mDB.Open();
OleDbCommand cmd;
sql = "DELETE FROM Staff WHERE StaffID = " + clsSQL.ToSql(temp.StaffID);
cmd = new OleDbCommand(sql, mDB);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ShowMessage("There was an unexpected problem when deleting the Staff member: " + ex.Message);
}
finally
{
closeDatabaseConnection();
}
// Erase the input values, notify the user, and display the current client roster
eraseInputFields();
ShowMessage(temp.FirstName + "'s record has been deleted.");
loadStaff("SELECT * FROM Staff");
displayData();
}
// When the Update button is clicked, the user is able to modify the data
// for the selected staff member.
private void btnUpdate_Click(object sender, EventArgs e)
{
// Define all variables.
string title;
string email;
string phone;
string fax;
string office;
string sql;
int selectedStaff = lstStaff.SelectedIndex - 2;
// Make sure a valid client was selected.
if (selectedStaff < 0 || selectedStaff >= mStaff.Count)
{
ShowMessage("Please select a valid staff member in the listbox.");
lstStaff.SelectedIndex = -1;
return;
}
// Get the ID of the selected client to fill in the input boxes
clsStaff temp = (clsStaff)mStaff[selectedStaff];
// Validate the user's input.
if (txtFirstName.Text == "")
{
ShowMessage("Please enter the staff member's first name");
txtFirstName.Focus();
return;
}
if (txtLastName.Text == "")
{
ShowMessage("Please enter the staff member's last name");
txtLastName.Focus();
return;
}
if (validateInput(txtTitle, out title) == false)
{
return;
}
if (validateInput(txtEmail, out email) == false)
{
return;
}
if (validateInput(txtPhone, out phone) == false)
{
return;
}
if (validateInput(txtFax, out fax) == false)
{
return;
}
if (validateInput(txtOffice, out office) == false)
{
return;
}
// Data is valid so modify the staff and reload the data from the DB
try
{
// Change a record in the Staff table.
openDatabaseConnection();
mDB.Open();
OleDbCommand cmd;
sql = "UPDATE Staff SET FirstName = " + clsSQL.ToSql(txtFirstName.Text) +
", LastName = " + clsSQL.ToSql(txtLastName.Text) +
", Title = " + clsSQL.ToSql(title) +
", Email = " + clsSQL.ToSql(email) +
", Phone = " + clsSQL.ToSql(phone) +
", Fax = " + clsSQL.ToSql(fax) +
", Office = " + clsSQL.ToSql(office) +
" WHERE StaffID = " + clsSQL.ToSql(temp.StaffID);
cmd = new OleDbCommand(sql, mDB);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
ShowMessage("There was an unexpected problem: " + ex.Message);
}
finally
{
closeDatabaseConnection();
}
// Erase the input values, notify the user, and display the current client roster
eraseInputFields();
ShowMessage(temp.FirstName + "'s record has been updated.");
loadStaff("SELECT * FROM Staff");
displayData();
}
