private void button2_Click_1(object sender, EventArgs e)
{
DBHelper db = new DBHelper();
String Query = "Update patron SET Last_Name = '" + this.editlnamebox.Text + "',First_Name = '" + this.editfnamebox.Text + "',Email = '" + this.editemailbox.Text + "',Address = '" + this.editaddressbox.Text + "',City = '" + editcitybox.Text + "',State = '" + this.editstatebox.Text + "',Telephone = '" + this.editphonebox.Text + "' WHERE patron_ID = '" + editpatronbox.Text + "';";
db.dbUpdate(Query);
this.editlnamebox.Text = String.Empty;
this.editfnamebox.Text = String.Empty;
this.editemailbox.Text = String.Empty;
this.editphonebox.Text = String.Empty;
this.editaddressbox.Text = String.Empty;
this.editcitybox.Text = String.Empty;
this.editstatebox.Text = String.Empty;
this.editpatronbox.Text = String.Empty;
MessageBox.Show("Update for patron " + editpatronbox.Text + " updated.");
}
private void button2_Click(object sender, EventArgs e)
{
String sISBN = removebookbox.Text;
if (sISBN.Length != 13)
{
MessageBox.Show("Incorrect Length for ISBN");
}
else
{
String dbString = "Delete from books where ISBN = " + sISBN + ";";
DBHelper db = new DBHelper();
db.dbUpdate(dbString);
MessageBox.Show("Book removed from database.");
removebookbox.Text = String.Empty;
}
}
private void SaveandExit_Click(object sender, EventArgs e)
{
String ISBN = addbookisbnbox.Text;
String title = addbooktitlebox.Text;
String authorLast = addbookauthorlast.Text;
String authorFirst = addbookauthorfirst.Text;
String pubDate = pubdatebox.Text;
String shelfID = addbookshelfidbox.Text;
if (ISBN.Length != 13)
{
MessageBox.Show("Incorrect length for ISBN (13)");
}
else
{
DBHelper db = new DBHelper();
String dbString = "INSERT INTO Books(ISBN,Title,Author_Lastname,Author_Firstname,Publication_Date,Shelf_ID,Checked_Out,Out_Date, Due_Date) "+
"VALUES (" + ISBN + ",'" + title + "','" + authorLast + "','" + authorFirst + "','" + pubDate + "','" + shelfID + "', 'N',null,null);";
db.dbUpdate(dbString);
MessageBox.Show("Book " + title + " added successfully");
this.Close();
}
}
private void button5_Click(object sender, EventArgs e)
{
String sISBN = checkinbox.Text;
if (sISBN.Length != 13)
{
MessageBox.Show("Incorrect length for ISBN (13)");
}
else
{
String dbString = "Update books SET Checked_Out = 'N', Out_Date = null, Due_Date = null, By_patron = null WHERE ISBN = " + sISBN + ";";
DBHelper db = new DBHelper();
db.dbUpdate(dbString);
MessageBox.Show("Check in successful.");
checkinbox.Text = String.Empty;
}
}
private void removepatronbutton_Click(object sender, EventArgs e)
{
DBHelper db = new DBHelper();
String sPatron_ID = RemovePatronBox.Text;
if (sPatron_ID.Length != 5)
{
MessageBox.Show("Incorrect Length for Patron ID");
}
else
{
String dbString = "UPDATE patron SET Inactive = TRUE where Patron_ID = " + sPatron_ID + ";";
db.dbUpdate(dbString);
MessageBox.Show("Patron "+sPatron_ID+" set to Inactive");
}
}
private void patronadd_Click(object sender, EventArgs e)
{
DBHelper db = new DBHelper();
MySqlDataReader myReader = null;
MySqlConnection myConnection = new MySqlConnection("Server = localhost; Database=libdb;Uid = root;password=root");
MySqlCommand myCommand = new MySqlCommand("select count(Patron_ID) from patron", myConnection);
String idCount = null;
myConnection.Open();
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
idCount = myReader[0].ToString();
}
int intCount = int.Parse(idCount);
intCount++;
String newID = null;
if (idCount.Length == 4)
newID = "1" + intCount;
else if (idCount.Length == 3)
newID = "10" + intCount;
else if (idCount.Length == 2)
newID = "100" + intCount;
else
newID = "1000" + intCount;
String addPatron = "INSERT INTO patron (Patron_ID, Last_Name, First_Name, Email, Telephone, Address, City, State, Inactive) values ('" + newID + "','" + this.lastnamebox.Text + "','" + this.firstnamebox.Text + "','" + this.emailbox.Text + "','" + this.phonebox.Text + "','" + addressbox.Text + "','" + this.citybox.Text + "','" + this.statebox.Text + "', FALSE);";
db.dbUpdate(addPatron);
MessageBox.Show("Entry created. New Patron ID:" + newID);
this.lastnamebox.Text = String.Empty;
this.firstnamebox.Text = String.Empty;
this.emailbox.Text = String.Empty;
this.phonebox.Text = String.Empty;
this.addressbox.Text = String.Empty;
this.citybox.Text = String.Empty;
this.statebox.Text = String.Empty;
myConnection.Close();
}
private void checkoutbutton_Click(object sender, EventArgs e)
{
String sISBN = checkoutboxbook.Text;
String sPatron = checkoutboxpatron.Text;
if (sISBN.Length != 13)
{
MessageBox.Show("Incorrect length for ISBN (13)");
}
else if (sPatron.Length != 5)
{
MessageBox.Show("Incorrect length for Patron ID (5)");
}
else
{
DateTime dtToday = DateTime.Today;
String sOut = dtToday.ToString("yyyy-MM-dd");
DateTime dtDue = dtToday.AddDays(14.00);
String sDue = dtDue.ToString("yyyy-MM-dd");
String dbString = "UPDATE books SET Checked_Out= 'Y',Out_Date= '" + sOut + "', Due_Date= '" + sDue + "', By_patron= " + sPatron + " WHERE ISBN= " + sISBN + ";";
DBHelper db = new DBHelper();
MessageBox.Show("Check out successfull.");
db.dbUpdate(dbString);
checkoutboxpatron.Text = String.Empty;
checkoutboxbook.Text = String.Empty;
}
}
