i am facing problem in setting number of book present in the database is 0 then show "No Book Is Available " message otherwise if value is 5 then decrement the counter one by one in window application.
Please Reply me soon.. Its urgent
What I have tried:
private void btnSave_Click(object sender, EventArgs e)
{
textBox1.Text = getuniqueID();
SqlConnection con = new SqlConnection(Main.connectionname());
SqlCommand cmd = new SqlCommand();
string quiry1 = "select NoOfBook from BookEntry where AccessionNo='" + comboBox2.Text + "' and NoOfBook <=0";
MessageBox.Show("Book is not available for issue", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
string quiry = "BEGIN TRY " +
"BEGIN TRANSACTION " +
"INSERT INTO [BookIssue]([T_Id],[issue_date],[due_date],[S_Id],[S_Name],[course],[department],[Accession_no],[book_title],[author],[ISBN],[edition])" +
"VALUES(@T_Id,@issue_date,@due_date,@S_Id,@S_Name,@course,@department,@Accession_no,@book_title,@author,@ISBN,@edition)";
string update = "UPDATE [BookEntry] SET [NoOfBook] = [NoOfBook] - 1 WHERE AccessionNo = '" + comboBox2.Text + "' And NoOfBook >0";
cmd.Parameters.AddWithValue("@T_Id", textBox1.Text.Trim());
cmd.Parameters.AddWithValue("@issue_date", dateTimePicker1.Text.Trim());
cmd.Parameters.AddWithValue("@due_date", dateTimePicker1.Text.Trim());
cmd.Parameters.AddWithValue("@S_Id", comboBox1.Text.Trim());
cmd.Parameters.AddWithValue("@S_Name", textBox3.Text.Trim());
cmd.Parameters.AddWithValue("@course", textBox4.Text.Trim());
cmd.Parameters.AddWithValue("@department", textBox5.Text.Trim());
cmd.Parameters.AddWithValue("@Accession_no", comboBox2.Text.Trim());
cmd.Parameters.AddWithValue("@book_title", textBox7.Text.Trim());
cmd.Parameters.AddWithValue("@author", textBox8.Text.Trim());
cmd.Parameters.AddWithValue("@ISBN", textBox9.Text.Trim());
cmd.Parameters.AddWithValue("@edition", textBox10.Text.Trim());
quiry += update;
quiry += " COMMIT TRANSACTION " +
"END TRY " +
"BEGIN CATCH " +
"ROLLBACK TRANSACTION " +
" END CATCH";
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = quiry;
try
{
con.Open();//establishes phy link between App and DB
cmd.ExecuteNonQuery();
MessageBox.Show("Information Saved.", "Library Application", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}