// Return to the Edit Database form
private void buttonBack_Click(object sender, EventArgs e)
{
this.Visible = false;
EditDatabase form = new EditDatabase();
form.Show();
this.Dispose();
}
private void buttonSave_Click(object sender, EventArgs e)
{
// Prompt for changes
if (MessageBox.Show("Are you sure you want to change this student's information?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
SQLiteConnection connect = new SQLiteConnection(Login.connection);
// Update the database
try
{
String[] queries = new String[3];
queries[0] = "UPDATE Students SET FirstName = @FirstName, LastName = @LastName, PID = @PID WHERE PID = '" + textBoxPID.Text + "';";
queries[1] = "UPDATE HISTORY SET PID = @PID WHERE PID = '" + textBoxPID.Text + "';";
queries[2] = "UPDATE CheckOut SET PID = @PID WHERE PID = '" + textBoxPID.Text + "';";
foreach (String query in queries)
{
SQLiteCommand cmd = new SQLiteCommand(query, connect);
connect.Open();
cmd.Parameters.AddWithValue("@FirstName", textBoxNewFirstName.Text);
cmd.Parameters.AddWithValue("@LastName", textBoxNewLastName.Text);
cmd.Parameters.AddWithValue("@PID", textBoxNewPID.Text);
cmd.ExecuteNonQuery();
connect.Close();
}
}
catch (SQLiteException exp)
{
MessageBox.Show(exp.Message.ToString());
}
finally
{
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
// Refresh back to Edit Database once record is updated
this.Visible = false;
EditDatabase form = new EditDatabase();
form.Show();
this.Dispose();
}
else
{
// Do Nothing and discard changes
}
}
private void buttonLogin_Click(object sender, EventArgs e)
{
// Initial error checking for blank username or password
if (textBoxUsername.Text.ToString() == "")
{
MessageBox.Show("Please enter your username");
}
else if (textBoxPassword.Text.ToString() == "")
{
MessageBox.Show("Please enter your password");
}
else
{
// Check credentials against database for login
SQLiteConnection connect = new SQLiteConnection(Login.connection);
bool login = false;
DataSet ds = new DataSet();
String query = "SELECT Username, Password FROM Users;";
try
{
connect.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(query, connect);
da.Fill(ds, "Users");
connect.Close();
int i = 0;
foreach (DataRow dr in ds.Tables[0].Rows)
{
if ((string)ds.Tables["Users"].Rows[i]["Username"] == textBoxUsername.Text.ToString() && (string)ds.Tables["Users"].Rows[i]["Password"] == textBoxPassword.Text.ToString())
{
login = true;
break;
}
i++;
}
if (login == true && textBoxUsername.Text == "admin")
{
// Save the login date and time to database
String loginDate = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss tt");
SQLiteConnection connectLog = new SQLiteConnection(Login.connection);
try
{
String queryLog = "UPDATE Users SET LastLogin = @LastLogin WHERE Username = '******';";
SQLiteCommand cmd = new SQLiteCommand(queryLog, connectLog);
connectLog.Open();
cmd.Parameters.AddWithValue("@LastLogin", loginDate);
cmd.ExecuteNonQuery();
connectLog.Close();
}
catch (SQLiteException exception)
{
MessageBox.Show(exception.Message.ToString());
}
finally
{
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
else if (connectLog.State == ConnectionState.Open)
{
connectLog.Close();
}
}
//User has administrator rights, Edit Database is available
this.Visible = false;
EditDatabase form = new EditDatabase();
form.Visible = true;
}
else if (login == true && textBoxUsername.Text != "admin")
{
// Save the login date and time to database
String loginDate = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss tt");
SQLiteConnection connectLog = new SQLiteConnection(Login.connection);
try
{
String queryLog = "UPDATE Users SET LastLogin = @LastLogin WHERE Username = '******';";
SQLiteCommand cmd = new SQLiteCommand(queryLog, connectLog);
connectLog.Open();
cmd.Parameters.AddWithValue("@LastLogin", loginDate);
cmd.ExecuteNonQuery();
connectLog.Close();
}
catch (SQLiteException exception)
{
MessageBox.Show(exception.Message.ToString());
}
finally
{
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
else if (connectLog.State == ConnectionState.Open)
{
connectLog.Close();
}
}
// User is not admin, edit database is not available
this.Visible = false;
Home form = new Home();
form.Visible = true;
}
else
{
labelAlert.Text = "Invalid username or password";
}
}
catch (SQLiteException exception)
{
MessageBox.Show(exception.ToString());
}
finally
{
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
}
}
private void buttonSave_Click(object sender, EventArgs e)
{
// Necessary code for PID error checking
int PIDparse;
int pid = Int32.Parse(textBoxPID.Text);
// Check for any blank fields
if (string.IsNullOrWhiteSpace(textBoxNewDevice.Text))
{
MessageBox.Show("Please type in the device name.");
}
if (string.IsNullOrWhiteSpace(textBoxNewSerial.Text))
{
MessageBox.Show("Please use the scanner or manually input the device serial number.");
}
if (string.IsNullOrWhiteSpace(textBoxNewCheckOut.Text))
{
MessageBox.Show("Please indicate the correct check out date.");
}
if (string.IsNullOrWhiteSpace(textBoxNewDueDate.Text))
{
MessageBox.Show("Please indicate the correct due date.");
}
if (string.IsNullOrWhiteSpace(textBoxNewCheckIn.Text))
{
MessageBox.Show("Please indicate the correct check in date.");
}
if (string.IsNullOrWhiteSpace(textBoxNewDueDate.Text))
{
MessageBox.Show("Please indicate the correct due date.");
}
// Verify that the PID is approprite in length and type (6 numbers)
else if (string.IsNullOrWhiteSpace(textBoxNewPID.Text))
{
MessageBox.Show("Please enter the student's PID.");
}
else if (textBoxNewPID.Text.Length != 6 || !int.TryParse(textBoxNewPID.Text, out PIDparse))
{
MessageBox.Show("The PID entered was not valid. Please enter a valid PID (6 numbers long)");
}
else
{
// Prompt for changes
if (MessageBox.Show("Are you sure you want to change the information in the database?", "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
SQLiteConnection connect = new SQLiteConnection(Login.connection);
// Make changes according to the info in the New Information text boxes
try
{
String[] queries = new String[2];
queries[0] = "UPDATE History SET CheckOutDate = @CheckOutDate, DueDate = @DueDate, Assets = @Assets, Comments = @Comments, CheckInDate = @CheckInDate, ReturnComments = @ReturnComments " +
"WHERE PID = '" + textBoxPID.Text + "' AND CheckOutDate = '" + textBoxCheckOut.Text + "';";
queries[1] = "UPDATE CheckOut SET CheckOutDate = @CheckOutDate, DueDate = @DueDate, Assets = @Assets, Comments = @Comments " +
"WHERE CheckOutDate = '" + textBoxCheckOut.Text + "';";
foreach (String query in queries)
{
SQLiteCommand cmd = new SQLiteCommand(query, connect);
connect.Open();
cmd.Parameters.AddWithValue("@CheckOutDate", textBoxNewCheckOut.Text);
cmd.Parameters.AddWithValue("@DueDate", textBoxNewDueDate.Text);
cmd.Parameters.AddWithValue("@CheckInDate", textBoxNewCheckIn.Text);
cmd.Parameters.AddWithValue("@Comments", textBoxNewComments.Text);
cmd.Parameters.AddWithValue("@Assets", textBoxNewAsset.Text);
cmd.Parameters.AddWithValue("@ReturnComments", textBoxNewReturnComments.Text);
cmd.ExecuteNonQuery();
connect.Close();
}
}
catch (SQLiteException exp)
{
MessageBox.Show(exp.Message.ToString());
}
finally
{
if (connect.State == ConnectionState.Open)
{
connect.Close();
}
}
// Refresh back to Home after New Check Out is completed
this.Visible = false;
EditDatabase form = new EditDatabase();
form.Show();
this.Dispose();
}
else
{
// Discard changes
}
}
}