Insert Sql function is not working

Question

I created a sale table which Insert function does not work properly. It shows the error message like this "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed." If I removed Sql Close Statement on Line 14, it shows this error msg "There is already an open DataReader associated with this Command which must be closed first." My code below work like this. I checked available stocks from my Product table. If quantity order is greater than quantity from Product Table, show error message. Otherwise, proceed to inserting order information into Sale Table. Any help is appreciated.

 private void btnOrder_Click(object sender, EventArgs e)
    {
        int iQuantityDB;
        int iCustomerID = Convert.ToInt32(txtCustomerID.Text);
        int iProductID = Convert.ToInt32(txtProductID.Text);
        decimal dPrice = Convert.ToDecimal(txtPrice.Text);
        int iQuantity = Convert.ToInt32(txtQuantity.Text);
        decimal dSubtotal = Convert.ToDecimal(txtSubTotal.Text);
        decimal dGST = Convert.ToDecimal(txtGST.Text);
        decimal dTotal = Convert.ToDecimal(txtTotal.Text);

        string strConnectionString = @"Data Source = KK\SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = JeanDB; MultipleActiveResultSets=True;";
        using (var sqlconn = new SqlConnection(strConnectionString))
        {
            sqlconn.Open();
            string querySelectQuantity = @"Select Quantity from dbo.JeanProduct WHERE ProductID = @iProductID";
            using (var cmdOrder = new SqlCommand(querySelectQuantity, sqlconn))
            {

                using (var sdRead = cmdOrder.ExecuteReader())
                {
                    sdRead.Read();
                    iQuantityDB = Convert.ToInt32(sdRead["Quantity"]);

                }
            }
            if (iQuantityDB > iQuantity)
            {
                string InsertQuery = @"INSERT INTO Sale(CustomerID, ProductID, Price, Quantity, Subtotal, GST, Total)VALUES(@iCustomerID, @iProductID, @dPrice, @iQuantity, @dSubtotal, @dGST, @Total)";
                using (var InsertCMD = new SqlCommand(InsertQuery, sqlconn))
                {
                    InsertCMD.Connection = sqlconn;

                    InsertCMD.Parameters.AddWithValue("@iCustomerID", iCustomerID);
                    InsertCMD.Parameters.AddWithValue("@iProdcutID", iProductID);
                    InsertCMD.Parameters.AddWithValue("@dPrice", dPrice);
                    InsertCMD.Parameters.AddWithValue("@iQuantity", iQuantity);
                    InsertCMD.Parameters.AddWithValue("@dSubtotal", dSubtotal);
                    InsertCMD.Parameters.AddWithValue("@dGST", dGST);
                    InsertCMD.Parameters.AddWithValue("@dTotal", dTotal);
                    InsertCMD.ExecuteNonQuery();


                    LoadDataonTable();
                }

            }

            else
            {
                MessageBox.Show("no more stock");

            }
            sqlconn.Close();

        }

    }

Answer 1

You've closed your SqlConnection after the reader execution / read cycle (and in the other error, you've kept the reader open while trying to execute another command).

Either close the reader and leave the connection open for the insert, or open a new connection for the insert.

Better still, use using to handle the disposal of the resources for you, and scope the DB resources to be released as soon as you are done with them, e.g.:

using (var sqlconn = new SqlConnection(strConnectionString))
{
    sqlconn.Open();

    string querySelectQuantity = "Select Quantity ...";
    using var (cmdOrder = new SqlCommand(querySelectQuantity, sqlconn))
    {
        int iQuantityDB;
        using (var sdRead = cmdOrder.ExecuteReader())
        {
            sdRead.Read();
            iQuantityDB = Convert.ToInt32(sdRead["Quantity"]);
        }   // Dispose reader
        // sqlconn.Close(); <-- Don't close
    } // cmdOrder disposed here

    if (iQuantityDB > iQuantity)
    {
        string InsertQuery = "INSERT INTO ...";
        using var (InsertCMD = new SqlCommand(InsertQuery, sqlconn))
        {
          // ...
        } // InsertCmd disposed here
    }
 } // Sql Connection disposed here

This will overcome many bugs, such as the one you've got where you are conditionally closing the command + connection in an if branch.

Answer 2

You should change your connection string to

string strConnectionString = @"Data Source = KK\SQLEXPRESS; 
                              Integrated Security = SSPI; 
                              Initial Catalog = JeanDB;
                              MultipleActiveResultSets=True";

And do not close the connection between the Reader.Read and the ExecuteNonQuery. You need at least Sql Server 2005 for this to work.

The connection used by a SqlDataReader cannot be used for other operations unless you set the connection string with the MultipleActiveResultSets key. Of course you could open two connection objects (with the same connection string) and use one for the SqlDataReader and one to Execute your command.

Not really linked to your problem, but I suggest to use a parameterized query also for the SELECT part of your code.

Moreover, you should use the Using Statement around the disposable object to ensure the proper closing and disposing also in case of exceptions. Finally, the syntax used in the INSERT INTO is not correct. I think that this code could explain some of the points explained above.

string strConnectionString = @"......;MultipleActiveResultSets=True;";
using(SqlConnection sqlconn = new SqlConnection(strConnectionString))
{
    sqlconn.Open();
    string querySelectQuantity = @"Select Quantity from dbo.JeanProduct 
                                   WHERE ProductID = @id";
    using(SqlCommand cmdOrder = new SqlCommand(querySelectQuantity, sqlconn))
    {
        cmdOrder.AddWithValue("@id", Convert.ToInt32(txtProductID.Text));
        using(SqlDataReader sdRead = cmdOrder.ExecuteReader())
        {
            if(sdRead.Read())
            {
                .....
                string InsertQuery = @"INSERT INTO Sale(SaleID, CustomerID, ProductID, 
                    Price, Quantity, Subtotal, GST, Total)VALUES(@iCustomerID, 
                    @iProductID, @dPrice, @iQuantity, 
                    @dSubtotal, @dGST, @Total)";
                    using(SqlCommand InsertCMD = new SqlCommand(InsertQuery, sqlconn))
                    {
                         InsertCMD.Parameters.AddWithValue("@iCustomerID", iCustomerID);  
                         ....
                         InsertCMD.ExecuteNonQuery();
                         LoadDataonTable(); 
                    }
             }
             else
             {
                  MessageBox.Show("no more stock");
             }
         }
    }
}