void AddNewBook() { //Multiselectbox Genres string genres = ""; foreach (int i in ListBGenre.GetSelectedIndices()) { genres = genres + ListBGenre.Items[i] + ";"; } //genres=Cow,Mbuzi,Kondoo, //To remove the last comma use this code below genres = genres.Remove(genres.Length - 1); //File Upload string filepath = "~/BookInventory/books1.png"; string filename = Path.GetFileName(FileUpload1.PostedFile.FileName); FileUpload1.SaveAs(Server.MapPath("BookInventory/" + filename)); filepath = "~/BookInventory/" + filename; try { SqlConnection con = new SqlConnection(Strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("Insert into Book (BookId,BookName,Genre,AuthoName,PublisherName,PublishDate," + "Language,Edition,BookCost,NoofPages,BookDesc,ActualStock,CurrentStock,BookImgLink) values(@BookId,@BookName," + "@Genre,@AuthorName,@PublisherName,@PublishDate,@Language,@Edition,@BookCost,@NoofPages,@BookDesc,@ActualStock," + "@CurrentStock,@BookImgLink)", con); cmd.Parameters.AddWithValue("@BookId", TxtBookId.Text.Trim()); cmd.Parameters.AddWithValue("@BookName", TxtBookName.Text.Trim()); cmd.Parameters.AddWithValue("@Genre", genres); cmd.Parameters.AddWithValue("@AuthorName", DPLAuthorName.SelectedItem.Value); cmd.Parameters.AddWithValue("@PublisherName", DPLPublisherName.SelectedItem.Value); cmd.Parameters.AddWithValue("@PublishDate", TxtPublishDate.Text.Trim()); cmd.Parameters.AddWithValue("@Language", DPLLanguage.SelectedItem.Value); cmd.Parameters.AddWithValue("@Edition", TxtEdition.Text.Trim()); cmd.Parameters.AddWithValue("@BookCost", TxtBookCost.Text.Trim()); cmd.Parameters.AddWithValue("@NoofPages", TxtPages.Text.Trim()); cmd.Parameters.AddWithValue("@BookDesc", TxtBookDescription.Text.Trim()); cmd.Parameters.AddWithValue("@ActualStock", TxtActualStock.Text.Trim()); cmd.Parameters.AddWithValue("@CurrentStock", TxtActualStock.Text.Trim()); cmd.Parameters.AddWithValue("@BookImgLink", filepath); cmd.ExecuteNonQuery(); con.Close(); Response.Write("<script>alert('Book added successfully.');</script>"); GridView1.DataBind(); } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } }
void GetBookById() { try { SqlConnection con = new SqlConnection(Strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("SELECT * from Book where BookId='" + TxtBookId.Text.Trim() + "';", con); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); if (dt.Rows.Count >= 1) { TxtBookName.Text = dt.Rows[0]["BookName"].ToString(); TxtPublishDate.Text = dt.Rows[0]["PublishDate"].ToString(); TxtEdition.Text = dt.Rows[0]["Edition"].ToString(); TxtBookCost.Text = dt.Rows[0]["BookCost"].ToString().Trim(); TxtPages.Text = dt.Rows[0]["NoofPages"].ToString().Trim(); TxtActualStock.Text = dt.Rows[0]["ActualStock"].ToString().Trim(); TxtCurrentStock.Text = dt.Rows[0]["CurrentStock"].ToString().Trim(); TxtBookDescription.Text = dt.Rows[0]["BookDesc"].ToString(); TxtIssuedbooks.Text = "" + (Convert.ToInt32(dt.Rows[0]["ActualStock"].ToString()) - Convert.ToInt32(dt.Rows[0]["CurrentStock"].ToString())); DPLLanguage.SelectedValue = dt.Rows[0]["Language"].ToString().Trim(); DPLPublisherName.SelectedValue = dt.Rows[0]["PublisherName"].ToString().Trim(); DPLAuthorName.SelectedValue = dt.Rows[0]["AuthorName"].ToString().Trim(); ListBGenre.ClearSelection(); string[] genre = dt.Rows[0]["genre"].ToString().Trim().Split(','); for (int i = 0; i < genre.Length; i++) { for (int j = 0; j < ListBGenre.Items.Count; j++) { if (ListBGenre.Items[j].ToString() == genre[i]) { ListBGenre.Items[j].Selected = true; } } } GlobalActualStock = Convert.ToInt32(dt.Rows[0]["ActualStock"].ToString().Trim()); GlobalCurrentStock = Convert.ToInt32(dt.Rows[0]["CurrentStock"].ToString().Trim()); GlobaIssuedBooks = GlobalActualStock - GlobalCurrentStock; GlobalFilepath = dt.Rows[0]["BookImgLink"].ToString(); } else { Response.Write("<script>alert('Invalid book Id');</script>"); } } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } }
void updateBookByID() { if (CheckIfBookExist()) { try { int ActualStock = Convert.ToInt32(TxtActualStock.Text.Trim()); int CurrentStock = Convert.ToInt32(TxtCurrentStock.Text.Trim()); if (GlobalActualStock == ActualStock) { } else { if (ActualStock < GlobalActualStock) { Response.Write("<script>alert('Actual Stock value cannot be less than the Issued books');</script>"); return; } else { CurrentStock = ActualStock - GlobaIssuedBooks; TxtCurrentStock.Text = "" + CurrentStock; } } string genres = ""; foreach (int i in ListBGenre.GetSelectedIndices()) { genres = genres + ListBGenre.Items[i] + ","; } genres = genres.Remove(genres.Length - 1); string filepath = "~/BookInventory/books1"; string filename = Path.GetFileName(FileUpload1.PostedFile.FileName); if (filename == "" || filename == null) { filepath = GlobalFilepath; } else { FileUpload1.SaveAs(Server.MapPath("BookInventory/" + filename)); filepath = "~/BookInventory/" + filename; } SqlConnection con = new SqlConnection(Strcon); if (con.State == ConnectionState.Closed) { con.Open(); } SqlCommand cmd = new SqlCommand("UPDATE Book set BookName=@BookName, Genre=@Genre, AuthorName=@AuthorName, " + "PublisherName=@PublisherName, PublishDate=@PublishDate, Language=@Language, Edition=@Edition, " + "BookCost=@BookCost, NoofPages=@NoofPages, BookDescription=@BookDescription, ActualStock=@ActualStock, " + "CurrentStock=@CurrentStock, BookImgLink=@BookImgLink where BookId='" + TxtBookId.Text.Trim() + "'", con); cmd.Parameters.AddWithValue("@BookName", TxtBookName.Text.Trim()); cmd.Parameters.AddWithValue("@Genre", genres); cmd.Parameters.AddWithValue("@AuthorName", DPLAuthorName.SelectedItem.Value); cmd.Parameters.AddWithValue("@PublisherName", DPLLanguage.SelectedItem.Value); cmd.Parameters.AddWithValue("@PublishDate", TxtPublishDate.Text.Trim()); cmd.Parameters.AddWithValue("@Language", DPLLanguage.SelectedItem.Value); cmd.Parameters.AddWithValue("@Edition", TxtEdition.Text.Trim()); cmd.Parameters.AddWithValue("@BookCost", TxtBookCost.Text.Trim()); cmd.Parameters.AddWithValue("@NoofPages", TxtPages.Text.Trim()); cmd.Parameters.AddWithValue("@BookDescription", TxtBookDescription.Text.Trim()); cmd.Parameters.AddWithValue("@ActualStock", TxtActualStock.ToString()); cmd.Parameters.AddWithValue("@CurrentStock", TxtCurrentStock.ToString()); cmd.Parameters.AddWithValue("@BookImgLink", filepath); cmd.ExecuteNonQuery(); con.Close(); GridView1.DataBind(); Response.Write("<script>alert('Book Updated Successfully');</script>"); } catch (Exception ex) { Response.Write("<script>alert('" + ex.Message + "');</script>"); } } else { Response.Write("<script>alert('Invalid Book ID');</script>"); } }