public void searcher(string field, string column)
{
try
{
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select ISBN, Title, Author, Publisher, Binding, pub_date as Publication_Date, Pages, Available from book where " + column + " like '%" + escapeHandling(field) + "%' order by Author, Title;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvBook.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
dgvBook.ClearSelection();
}
private void clears()
{
txtNameSearch.Text = "Enter all or part of a name here...";
txtCCSearch.Text = "Scan a customer card here...";
try
{
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select * from customer order by last_name;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvCustomers.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
private void frmWhatMedia_Load(object sender, EventArgs e)
{
try
{
string connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select Title, isbn as Checkout_Code from (select title, isbn from book union select title, upc from movie union select title,upc from game union select album,upc from cd) order by title;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvMedia.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
DataGridViewColumn c = dgvMedia.Columns[0];
c.Width = 155;
dgvMedia.ClearSelection();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
private void txtNameSearch_TextChanged(object sender, EventArgs e)
{
if (txtNameSearch.Text.Length >= 0)
{
try
{
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select * from customer where first_name like '%" + txtNameSearch.Text + "%' or last_name like '%" + txtNameSearch.Text + "%' order by last_name;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvCustomers.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
}
private void searcher(string field, string column)
{
try {
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select * from Customer where " + column + " like '%" + escapeHandling(field) + "%' order by last_name;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvCustomers.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee) {
MessageBox.Show(ee.Message);
}
}
private void refresh()
{
try {
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select * from customer order by last_name;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvCustomers.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee) {
MessageBox.Show("There was a problem loading the customer list.\n" + ee.Message);
}
}
private void searcher(string field, string column)
{
try {
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select UPC, Artist, Album, Type, Publisher, Release_Date, Available from cd where " + column + " like '%" + field + "%' order by Artist, Album;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvMusic.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee) {
MessageBox.Show(ee.Message);
}
}
private void refresh()
{
try {
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select UPC, Artist, Album, Type, Publisher, Release_Date, Available from cd order by Artist, Album;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvMusic.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee) {
MessageBox.Show("There was a problem loading the CD list.\n" + ee.Message);
}
}
private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
{
string row = dgvMusic[0, dgvMusic.CurrentRow.Index].Value.ToString();
if (MessageBox.Show("Are you sure?", "Confirm Deletion", MessageBoxButtons.YesNo) == DialogResult.No) {
MessageBox.Show("Request ignored");
dgvMusic.ClearSelection();
return;
}
try {
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "delete from cd where UPC = '" + row + "';";
string query2 = "delete from track where UPC = '" + row + "';";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbDataAdapter da2 = new OleDbDataAdapter(query2, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
OleDbCommandBuilder cb2 = new OleDbCommandBuilder(da2);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvMusic.DataSource = bs;
da.Update(dt);
}
catch (Exception ex) {
MessageBox.Show("Unexpected error: " + ex);
}
finally {
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
refresh();
dgvMusic.ClearSelection();
}
private void refresh()
{
try {
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select UPC, TITLE, PLATFORM, STUDIO, RELEASE_DATE, AVAILABLE from game order by PLATFORM, TITLE;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvGames.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee) {
MessageBox.Show("There was a problem loading the game list.\n" + ee.Message);
}
}
private void searcher(string field, string column)
{
try {
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select UPC, Title, Release_Date, Director, Type, Studio, Running_Time, Available from movie where " + column + " like '%" + escapeHandling(field) + "%' order by Title, Release_Date;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvMovies.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee) {
MessageBox.Show(ee.Message);
}
}
private void frmOverdueItems_Load(object sender, EventArgs e)
{
try
{
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string bookQuery = "SELECT book.TITLE, book_CHECKOUT.DUE_DATE, customer.first_name &' '&customer.last_name as CUSTOMER_NAME, customer.email_addr AS EMAIL_ADDRESS FROM (((book inner join book_checkout on book.book_id = book_checkout.book_id) inner join checkout on book_checkout.checkout_id = checkout.checkout_id) inner join customer on checkout.customer_id = customer.customer_id) WHERE (((book_CHECKOUT.DUE_DATE)<#" + System.DateTime.Now + "#));";
string movieQuery = "SELECT movie.TITLE, movie_CHECKOUT.DUE_DATE, customer.first_name &' '&customer.last_name as CUSTOMER_NAME, customer.email_addr AS EMAIL_ADDRESS FROM (((movie inner join movie_checkout on movie.movie_id = movie_checkout.movie_id) inner join checkout on movie_checkout.checkout_id = checkout.checkout_id) inner join customer on checkout.customer_id = customer.customer_id) WHERE (((movie_CHECKOUT.DUE_DATE)<#" + System.DateTime.Now + "#));";
string musicQuery = "SELECT cd.album as TITLE, cd_CHECKOUT.DUE_DATE, customer.first_name &' '&customer.last_name as CUSTOMER_NAME, customer.email_addr AS EMAIL_ADDRESS FROM (((cd inner join cd_checkout on cd.cd_id = cd_checkout.cd_id) inner join checkout on cd_checkout.checkout_id = checkout.checkout_id) inner join customer on checkout.customer_id = customer.customer_id) WHERE (((cd_CHECKOUT.DUE_DATE)<#" + System.DateTime.Now + "#));";
string gameQuery = "SELECT game.TITLE, game_CHECKOUT.DUE_DATE, customer.first_name &' '&customer.last_name as CUSTOMER_NAME, customer.email_addr AS EMAIL_ADDRESS FROM (((game inner join game_checkout on game.game_id = game_checkout.game_id) inner join checkout on game_checkout.checkout_id = checkout.checkout_id) inner join customer on checkout.customer_id = customer.customer_id) WHERE (((game_CHECKOUT.DUE_DATE)<#" + System.DateTime.Now + "#));";
//BEGIN BOOK OVERDUE CHECK
OleDbDataAdapter bookDA = new OleDbDataAdapter(bookQuery, connectionString);
OleDbCommandBuilder bookCB = new OleDbCommandBuilder(bookDA);
DataTable bookDT = new DataTable();
bookDA.Fill(bookDT);
BindingSource bookBS = new BindingSource();
bookBS.DataSource = bookDT;
dgvOverdueBooks.DataSource = bookBS;
bookDA.Update(bookDT);
//END BOOK OVERDUE CHECK
//BEGIN MOVIE OVERDUE CHECK
OleDbDataAdapter movieDA = new OleDbDataAdapter(movieQuery, connectionString);
OleDbCommandBuilder movieCB = new OleDbCommandBuilder(movieDA);
DataTable movieDT = new DataTable();
movieDA.Fill(movieDT);
BindingSource movieBS = new BindingSource();
movieBS.DataSource = movieDT;
dgvOverdueMovies.DataSource = movieBS;
movieDA.Update(movieDT);
//END MVOIE OVERDUE CHECK
//BEGIN MUSIC OVERDUE CHECK
OleDbDataAdapter musicDA = new OleDbDataAdapter(musicQuery, connectionString);
OleDbCommandBuilder musicCB = new OleDbCommandBuilder(musicDA);
DataTable musicDT = new DataTable();
musicDA.Fill(musicDT);
BindingSource musicBS = new BindingSource();
musicBS.DataSource = musicDT;
dgvOverdueMusic.DataSource = musicBS;
musicDA.Update(musicDT);
//END MUSIC OVERDUE CHECK
//BEGIN GAME OVERDUE CHECK
OleDbDataAdapter gameDA = new OleDbDataAdapter(gameQuery, connectionString);
OleDbCommandBuilder gameCB = new OleDbCommandBuilder(gameDA);
DataTable gameDT = new DataTable();
gameDA.Fill(gameDT);
BindingSource gameBS = new BindingSource();
gameBS.DataSource = gameDT;
dgvOverdueGames.DataSource = gameBS;
gameDA.Update(gameDT);
//END GAME OVERDUE CHECK
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
private void refresh()
{
try
{
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select ISBN, Title, Author, Publisher, Binding, pub_date as Publication_Date, Pages, Available from book order by Author, Title;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvBook.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee)
{
MessageBox.Show("There was a problem loading the book list.\n" + ee.Message);
}
}