Check SQL for Book_Availability before issuing one (BookAvailability-1)

Question

If I put "if, foreach, and else statement under comment //", the program works and Reduces book count by 1 from SQL database. But I want to check IF there is at least 1 available book to give. This code keeps showing me the message in "else" statement if I leave it like this. Help is needed fast, it's my final project, that is needed to be done before 23.07. :(

            int book_qty = 0;
            SqlCommand cmd2 = connection.CreateCommand();
            cmd2.CommandType = CommandType.Text;
            cmd2.CommandText = "SELECT * FROM Book_list WHERE BookName = '" + TextBoxBookName + "'";
            cmd2.ExecuteNonQuery();
            DataTable dt2 = new DataTable();
            SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
            da2.Fill(dt2);


            foreach (DataRow dr2 in dt2.Rows)
            {
                book_qty = Convert.ToInt32(dr2["book_qty"].ToString());
            }

            if (book_qty > 0)
            {



                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Issue_book VALUES(" + TextBoxSearchMembers.Text + ",'" + TextBoxMemberName.Text + "','" + TextBoxMemberContact.Text + "','" + TextBoxMemberEmail.Text + "','" + TextBoxBookName.Text + "', '" + DateTimePicker1.Text + "')";
                cmd.ExecuteNonQuery();


                SqlCommand cmd1 = connection.CreateCommand();
                cmd1.CommandType = CommandType.Text;
                cmd1.CommandText = "UPDATE Book_list SET BookAvailability = BookAvailability-1 WHERE BookName ='" + TextBoxBookName.Text + "'";
                cmd1.ExecuteNonQuery();


                MessageBox.Show("successful issue");
                this.Close();


            else
            {
                    MessageBox.Show("Book not available");
            }

Answer 1

You are only checking book_qty from the last row in your result set instead of BookAvailability for all rows. You probably want to do something like:

            SqlCommand cmd2 = connection.CreateCommand();
            cmd2.CommandType = CommandType.Text;
            cmd2.CommandText = "SELECT BookAvailability FROM Book_list WHERE BookName = '" + TextBoxBookName + "'";
            var result = cmd2.ExecuteScalar();
            book_qty = Convert.ToInt32(result);

You need to make sure that there is only one book with the given bookname available.

In that case just correcting this one line in your code would help as well:

            book_qty = Convert.ToInt32(dr2["book_qty"].ToString());

to

            book_qty = Convert.ToInt32(dr2["BookAvailability"].ToString());

Otherwise you'd need to query SUM(BookAvailability), but the following code would decrease the amount of books for multiple books at once, that wouldn't be good.

Answer 2

Untested code. I don't have your database. Comments and explanation in line.

private void OPCode()
        {
            try
            {
                //keep your connections close to the vest (local)
                using (SqlConnection connection = new SqlConnection())
                //a using block ensures that your objects are closed and disposed 
                //even if there is an error
                {
                    using (SqlCommand cmd2 = new SqlCommand("SELECT BookAvailability  FROM Book_list WHERE BookName = @BookName", connection))
                    {
                        //Always use parameters to protect from sql injection
                        //Also it is easier than fooling with the single quotes etc.
                        //If you are referring to a TextBox you need to provide what property is
                        //being accessed. I am not in a WPF right now and not sure if .Text
                        //is correct; may be .Content
                        //You need to check your database for correct data type and field size
                        cmd2.Parameters.Add("@BookName", SqlDbType.VarChar, 100).Value = TextBoxBookName.Text;
                        //A select statement is not a non-query
                        //You don't appear to be using the data table or data adapter
                        //so dump them extra objects just slow things dowm
                        connection.Open();
                       //Comment out the next 2 lines and replaced with
                       //Edit Update
                        //var returnVal = cmd2.ExecuteScalar() ?? 0;
                        //if ((int)returnVal > 0)




               //*************************************************************
                            //Edit Update
                            //*************************************************************
                            //in case the query returns a null, normally an integer cannot
                            //hold the value of null so we use nullable types
                            // the (int?) casts the result of the query to Nullable of int
                            Nullable<int> returnVal = (int?)cmd2.ExecuteScalar();
                            //now we can use the .GetValueOrDefault to return the value
                            //if it is not null of the default value of the int (Which is 0)
                            int bookCount = returnVal.GetValueOrDefault();
                            //at this point bookCount should be a real int - no cast necessary
                            if (bookCount > 0)

 //**************************************************************
                           //End Edit Update
                           //**************************************************************
                                {
                                    using (SqlCommand cmd = new SqlCommand("INSERT INTO issue_book VALUES(@SearchMembers etc", connection))
                                    {
                                        //set up the parameters for this command just like the sample above
                                        cmd.Parameters.Add("@SearchMembers", SqlDbType.VarChar, 100).Value = TextBoxSearchMembers.Text;
                                        cmd.ExecuteNonQuery();
                                    }
                                    using (SqlCommand cmd1 = new SqlCommand("UPDATE Book_list SET BookAvailability = BookAvailability-1 WHERE BookName = @BoxBookName;", connection))
                                    {
                                        cmd1.Parameters.Add("@BoxBookName", SqlDbType.VarChar, 100);
                                        cmd1.ExecuteNonQuery();
                                    }
                                    MessageBox.Show("success");
                                    this.Close();
                                }
                                else
                                {
                                    MessageBox.Show("Book not available");
                                }
                            }
                        }
                    }
                    catch (Exception exc)
                    {
                        MessageBox.Show(exc.ToString());
                    }
                }