protected void btnDelete_Click(object sender, EventArgs e)
{
//Try catch block to handle errors
try
{
lblError.Text = "";
BookInventoryTableAdapters.BookTableAdapter tableAdapter = new BookInventoryTableAdapters.BookTableAdapter();
double Isbn = double.Parse(dropDownEditSearch.SelectedValue);
string Title = lblTitle.Text.ToString();
string AuthorLName = lblLastName.Text.ToString();
string AuthorFName = lblFirstName.Text.ToString();
string Rating = lblRating.ToString();
//Program sets up two queries to delete from the books table, and the categories table
string bookSql = "DELETE FROM [Table] "
+ "WHERE Isbn = @Isbn";
string categorySql = "DELETE FROM Categories "
+ "WHERE Isbn = @Isbn";
//Program executes both queries
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(bookSql, con))
{
cmd.Parameters.AddWithValue("Isbn", Isbn);
con.Open();
cmd.ExecuteNonQuery();
}
}
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(categorySql, con))
{
cmd.Parameters.AddWithValue("Isbn", Isbn);
con.Open();
cmd.ExecuteNonQuery();
}
}
//Program displays that the operation was successful
lblResult.Text = Title + " successfully removed!";
DataBind();
}
catch
{
//Program displays that a database error has occurred
lblResult.Text = "";
lblError.Text = "A database error has occurred";
}
}
protected void btnEditSubmit_Click(object sender, EventArgs e)
{
//Try catch block to handle errors
try
{
lbl_error.Text = "";
//Program gets and stores the values entered by the user
double isbn = double.Parse(dropDownEditSearch.SelectedValue);
string title = textEditTitle.Text;
string lastname = textEditLastName.Text;
string firstname = textEditFirstName.Text;
double rating = double.Parse(DropDownQuestNum.SelectedValue);
string format = rblst_format.SelectedValue;
double newIsbn = double.Parse(textEditIsbn.Text);
//Program updates the book table below
BookInventoryTableAdapters.BookTableAdapter tableAdapter = new BookInventoryTableAdapters.BookTableAdapter();
tableAdapter.UpdateBookTable(newIsbn, title, lastname, firstname, rating, format);
//Program gets the selected categories
bool fiction = cbEditCategory.Items.FindByText("Fiction").Selected;
bool children = cbEditCategory.Items.FindByText("Children's").Selected;
bool foreign = cbEditCategory.Items.FindByText("Foreign").Selected;
bool romance = cbEditCategory.Items.FindByText("Romance").Selected;
bool suspense = cbEditCategory.Items.FindByText("Suspense").Selected;
bool nonfiction = cbEditCategory.Items.FindByText("Non-Fiction").Selected;
bool comedy = cbEditCategory.Items.FindByText("Comedy").Selected;
bool history = cbEditCategory.Items.FindByText("History").Selected;
bool sciFi = cbEditCategory.Items.FindByText("Sci-Fi").Selected;
bool textbook = cbEditCategory.Items.FindByText("Textbook").Selected;
bool autobiography = cbEditCategory.Items.FindByText("AutoBiography").Selected;
bool drama = cbEditCategory.Items.FindByText("Drama").Selected;
bool horror = cbEditCategory.Items.FindByText("Horror").Selected;
bool selfHelp = cbEditCategory.Items.FindByText("Self-Help").Selected;
bool thriller = cbEditCategory.Items.FindByText("Thriller").Selected;
bool biography = cbEditCategory.Items.FindByText("Biography").Selected;
bool fantasy = cbEditCategory.Items.FindByText("Fantasy").Selected;
bool religious = cbEditCategory.Items.FindByText("Religious").Selected;
//Program updates the categories
tableAdapter.UpdateCategories(fiction, children, foreign, romance, suspense, nonfiction, comedy, history,
sciFi, textbook, autobiography, drama, horror, selfHelp, thriller, biography, fantasy, religious, isbn);
//Program displays that the operation was successful
lbl_result.Text = "Record successfully updated for " + title;
dropDownEditSearch.DataBind();
}
catch
{
//Program displays that an error has occurred
lbl_error.Text = "A database error has occurred.";
}
}
protected void dropDownEditSearch_SelectedIndexChanged(object sender, EventArgs e)
{
//Program clears out the current text
lbl_result.Text = "";
lbl_error.Text = "";
double isbn = double.Parse(dropDownEditSearch.SelectedValue);
double rating = 0;
string format = "";
BookInventoryTableAdapters.BookTableAdapter tableAdapter = new BookInventoryTableAdapters.BookTableAdapter();
BookInventory.BookInventoryTableDataTable bookInventory = tableAdapter.GetDataByIsbn(isbn);
//Foreach loop goes through the rows
foreach (BookInventory.BookInventoryTableRow br in bookInventory)
{
//Program updates each item with the current information from the database
textEditIsbn.Text = br.Isbn.ToString();
textEditFirstName.Text = br.AuthorFName.ToString();
textEditLastName.Text = br.AuthorLName.ToString();
textEditTitle.Text = br.Title.ToString();
cbEditCategory.Items.FindByText("Fiction").Selected = (bool)br.Fiction;
cbEditCategory.Items.FindByText("Children's").Selected = (bool)br._Children_s;
cbEditCategory.Items.FindByText("Foreign").Selected = (bool)br.Foreign;
cbEditCategory.Items.FindByText("Romance").Selected = (bool)br.Romance;
cbEditCategory.Items.FindByText("Suspense").Selected = (bool)br.Suspense;
cbEditCategory.Items.FindByText("Non-Fiction").Selected = (bool)br._Non_Fiction;
cbEditCategory.Items.FindByText("Comedy").Selected = (bool)br.Comedy;
cbEditCategory.Items.FindByText("History").Selected = (bool)br.History;
cbEditCategory.Items.FindByText("Sci-Fi").Selected = (bool)br._Sci_Fi;
cbEditCategory.Items.FindByText("Textbook").Selected = (bool)br.Textbook;
cbEditCategory.Items.FindByText("AutoBiography").Selected = (bool)br.Autobiography;
cbEditCategory.Items.FindByText("Drama").Selected = (bool)br.Drama;
cbEditCategory.Items.FindByText("Horror").Selected = (bool)br.Horror;
cbEditCategory.Items.FindByText("Self-Help").Selected = (bool)br._Self_Help;
cbEditCategory.Items.FindByText("Thriller").Selected = (bool)br.Thriller;
cbEditCategory.Items.FindByText("Biography").Selected = (bool)br.Biography;
cbEditCategory.Items.FindByText("Fantasy").Selected = (bool)br.Fantasy;
cbEditCategory.Items.FindByText("Religious").Selected = (bool)br.Religious;
rating = br.Rating;
format = br.Format;
}
int index = DropDownQuestNum.Items.IndexOf(DropDownQuestNum.Items.FindByValue(rating.ToString()));
DropDownQuestNum.SelectedIndex = index;
int rbindex = rblst_format.Items.IndexOf(rblst_format.Items.FindByValue(format));
rblst_format.SelectedIndex = rbindex;
}
protected void btnEditSubmit_Click(object sender, EventArgs e)
{
double isbn = double.Parse(dropDownEditSearch.SelectedValue);
string title = textEditTitle.Text;
string lastname = textEditLastName.Text;
string firstname = textEditFirstName.Text;
double rating = double.Parse(DropDownQuestNum.SelectedValue);
string format = rblst_format.SelectedValue;
BookInventoryTableAdapters.BookTableAdapter tableAdapter = new BookInventoryTableAdapters.BookTableAdapter();
tableAdapter.UpdateBookTable(isbn, title, lastname, firstname, rating, format);
bool fiction = cbEditCategory.Items.FindByText("Fiction").Selected;
bool children = cbEditCategory.Items.FindByText("Children's").Selected;
bool foreign = cbEditCategory.Items.FindByText("Foreign").Selected;
bool romance = cbEditCategory.Items.FindByText("Romance").Selected;
bool suspense = cbEditCategory.Items.FindByText("Suspense").Selected;
bool nonfiction = cbEditCategory.Items.FindByText("Non-Fiction").Selected;
bool comedy = cbEditCategory.Items.FindByText("Comedy").Selected;
bool history = cbEditCategory.Items.FindByText("History").Selected;
bool sciFi = cbEditCategory.Items.FindByText("Sci-Fi").Selected;
bool textbook = cbEditCategory.Items.FindByText("Textbook").Selected;
bool autobiography = cbEditCategory.Items.FindByText("AutoBiography").Selected;
bool drama = cbEditCategory.Items.FindByText("Drama").Selected;
bool horror = cbEditCategory.Items.FindByText("Horror").Selected;
bool selfHelp = cbEditCategory.Items.FindByText("Self-Help").Selected;
bool thriller = cbEditCategory.Items.FindByText("Thriller").Selected;
bool biography = cbEditCategory.Items.FindByText("Biography").Selected;
bool fantasy = cbEditCategory.Items.FindByText("Fantasy").Selected;
bool religious = cbEditCategory.Items.FindByText("Religious").Selected;
tableAdapter.UpdateCategories(fiction, children, foreign, romance, suspense, nonfiction, comedy, history,
sciFi, textbook, autobiography, drama, horror, selfHelp, thriller, biography, fantasy, religious, isbn);
lbl_result.Text = "Record successfully updated for " + title;
dropDownEditSearch.DataBind();
}
protected void dropDownEditSearch_SelectedIndexChanged(object sender, EventArgs e)
{
//Program gets the isbn, rating, and format
double isbn = double.Parse(dropDownEditSearch.SelectedValue);
double rating = 0;
string format = "";
//Creating objects for the table adapter and bookInventory
BookInventoryTableAdapters.BookTableAdapter tableAdapter = new BookInventoryTableAdapters.BookTableAdapter();
BookInventory.BookInventoryTableDataTable bookInventory = tableAdapter.GetDataByIsbn(isbn);
//Foreach loop goes through each row of data
foreach (BookInventory.BookInventoryTableRow br in bookInventory)
{
//Program sets the label text to the current record
lblFirstName.Text = br.AuthorFName.ToString();
lblLastName.Text = br.AuthorLName.ToString();
lblTitle.Text = br.Title.ToString();
lblRating.Text = br.Rating.ToString();
rating = br.Rating;
format = br.Format;
}
//Program gets the book categories
string categorySql = "SELECT Fiction, [Children's], [Foreign], Romance, Suspense, [Non-Fiction], Comedy, History, [Sci-Fi], Textbook, Autobiography, Drama, Horror, [Self-Help], Thriller, Biography, Fantasy, Religious" +
" FROM categories WHERE Isbn = @Isbn";
List <string> categoryNames = new List <string>();
//Program opens a new connection to the database
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(categorySql, con))
{
//Program executes the query
cmd.Parameters.AddWithValue("Isbn", isbn);
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
//While loop adds the category names to the list of categories
while (reader.Read())
{
for (int i = 0; i < 18; i++)
{
if (reader.GetBoolean(i) == true)
{
categoryNames.Add(reader.GetName(i).ToString());
}
}
}
}
}
}
string cat = "";
//For loop sets cat to the formatted list of categories
for (int i = 0; i < categoryNames.Count; i++)
{
if (categoryNames.Count - i == 1)
{
cat += categoryNames[i];
}
else
{
cat += categoryNames[i] + ", ";
}
}
//Program displays the categories
lblCategories.Text = cat;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
//Try catch block to handle errors
try
{
lbl_error.Text = "";
BookInventoryTableAdapters.BookTableAdapter tableAdapter = new BookInventoryTableAdapters.BookTableAdapter();
//Program gets the input from the user and stores it into the variables below
double Isbn;
double.TryParse(textIsbn.Text, out Isbn);
string Title = textTitle.Text.ToString();
string AuthorLName = textLastName.Text.ToString();
string AuthorFName = textFirstName.Text.ToString();
double Rating = double.Parse(DropDownQuestNum.SelectedValue);
string Format = rblst_format.SelectedValue;
bool fiction = cbCategory.Items.FindByText("Fiction").Selected;
bool children = cbCategory.Items.FindByText("Children's").Selected;
bool foreign = cbCategory.Items.FindByText("Foreign").Selected;
bool romance = cbCategory.Items.FindByText("Romance").Selected;
bool suspense = cbCategory.Items.FindByText("Suspense").Selected;
bool nonfiction = cbCategory.Items.FindByText("Non-Fiction").Selected;
bool comedy = cbCategory.Items.FindByText("Comedy").Selected;
bool history = cbCategory.Items.FindByText("History").Selected;
bool sciFi = cbCategory.Items.FindByText("Sci-Fi").Selected;
bool textbook = cbCategory.Items.FindByText("Textbook").Selected;
bool autobiography = cbCategory.Items.FindByText("AutoBiography").Selected;
bool drama = cbCategory.Items.FindByText("Drama").Selected;
bool horror = cbCategory.Items.FindByText("Horror").Selected;
bool selfHelp = cbCategory.Items.FindByText("Self-Help").Selected;
bool thriller = cbCategory.Items.FindByText("Thriller").Selected;
bool biography = cbCategory.Items.FindByText("Biography").Selected;
bool fantasy = cbCategory.Items.FindByText("Fantasy").Selected;
bool religious = cbCategory.Items.FindByText("Religious").Selected;
//Sql statement to add a new item to the database
string bookSql = "INSERT INTO [Table] "
+ "(Isbn, Title, AuthorLName, AuthorFName, Rating, Format) "
+ "VALUES(@Isbn, @Title, @AuthorLName, @AuthorFName, @Rating, @Format)";
//Program adds a new item to the books table
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(bookSql, con))
{
cmd.Parameters.AddWithValue("Isbn", Isbn);
cmd.Parameters.AddWithValue("Title", Title);
cmd.Parameters.AddWithValue("AuthorLName", AuthorLName);
cmd.Parameters.AddWithValue("AuthorFName", AuthorFName);
cmd.Parameters.AddWithValue("Rating", Rating);
cmd.Parameters.AddWithValue("Format", Format);
con.Open();
cmd.ExecuteNonQuery();
}
}
//Sql string to add a new item to the categories table
string categorySql = "INSERT INTO Categories "
+ "(Isbn, Fiction, [Children's], [Foreign], Romance, Suspense, [Non-Fiction], Comedy, History, [Sci-Fi], Textbook, Autobiography, Drama, Horror, [Self-Help], Thriller, Biography, Fantasy, Religious) "
+ "VALUES(@Isbn, @Fiction, @Childrens, @Foreign, @Romance, @Suspense, @NonFiction, @Comedy, @History, @SciFi, @Textbook, @Autobiography, @Drama, @Horror, @SelfHelp, @Thriller, @Biography, @Fantasy, @Religious)";
//Program adds it to the table
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(categorySql, con))
{
cmd.Parameters.AddWithValue("Isbn", Isbn);
cmd.Parameters.AddWithValue("Fiction", fiction);
cmd.Parameters.AddWithValue("Childrens", children);
cmd.Parameters.AddWithValue("Foreign", foreign);
cmd.Parameters.AddWithValue("Romance", romance);
cmd.Parameters.AddWithValue("Suspense", suspense);
cmd.Parameters.AddWithValue("NonFiction", nonfiction);
cmd.Parameters.AddWithValue("Comedy", comedy);
cmd.Parameters.AddWithValue("History", history);
cmd.Parameters.AddWithValue("SciFi", sciFi);
cmd.Parameters.AddWithValue("Textbook", textbook);
cmd.Parameters.AddWithValue("Autobiography", autobiography);
cmd.Parameters.AddWithValue("Drama", drama);
cmd.Parameters.AddWithValue("Horror", horror);
cmd.Parameters.AddWithValue("SelfHelp", selfHelp);
cmd.Parameters.AddWithValue("Thriller", thriller);
cmd.Parameters.AddWithValue("Biography", biography);
cmd.Parameters.AddWithValue("Fantasy", fantasy);
cmd.Parameters.AddWithValue("Religious", religious);
con.Open();
cmd.ExecuteNonQuery();
}
}
//Display that the operation was successful
lbl_result.Text = Title + " successfully inserted!";
}
catch
{
//Display that an error has occurred
lbl_result.Text = "";
lbl_error.Text = "A database error has occurred";
}
}
