Invalid Operation exception was caught

Question

User-797751191 posted

Hi

  On cmdUpdate i get this error - There is already an open DataReader associated with this Command which must be closed first.

Thanks

try
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM test Where [Invoice No]=@InvoiceNo and [Customer Id] = @CustId", con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@InvoiceNo", txtInvoiceNo.Text.ToUpper().Trim());
                    cmd.Parameters.AddWithValue("@CustId", Request.QueryString["cu"].ToUpper().Trim());
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        if (sdr.HasRows)
                        {
                            sdr.Read();
                            Status = sdr["Status"].ToString();
                            if (Status == "")
                            {
                                SqlCommand cmdupdate = new SqlCommand("Update test SET Status = 'Pending' where [Invoice No]='" + txtInvoiceNo.Text.Trim() + "'", con);
                                cmdupdate.CommandType = CommandType.Text;
                                cmdupdate.ExecuteNonQuery();

                                string message = "Data Uploaded Successfully !";
                                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + message + "');", true);
                                cmdupdate.Dispose();
                            }
}
}
}
}
        catch (Exception ex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("<script language=JavaScript>");
            builder.Append("alert(\"" + ex.Message.Replace("\r\n", "") + "\")");
            builder.Append("</script>");
            Page.ClientScript.RegisterStartupScript(this.GetType(), "FailAlert", builder.ToString());
        }

Answer 1

User475983607 posted

You are using the same connection object for both queries. 

Even if you were to use separate connection objects, the code has a logical bug where only the first record found can cause an an Update.  You can refactor this code with single SQL script.

Answer 2

User-797751191 posted

Hi

  On which line can u pls tell

Thanks

Answer 3

User288213138 posted

Hi jsshivalik,

When you are iterating a DataReader, you must create a new connection object to execute other operations.

 you can try below code:

try
            {
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM test Where [Invoice No]=@InvoiceNo and [Customer Id] = @CustId", con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@InvoiceNo", txtInvoiceNo.Text.ToUpper().Trim());
                    cmd.Parameters.AddWithValue("@CustId", Request.QueryString["cu"].ToUpper().Trim());
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        if (sdr.HasRows)
                        {
                            sdr.Read();
                            Status = sdr["Status"].ToString();
                            if (Status == "")
                            {
                                SqlCommand cmdupdate = new SqlCommand("Update test SET Status = 'Pending' where [Invoice No]='" + txtInvoiceNo.Text.Trim() + "'", con);
                                cmdupdate.CommandType = CommandType.Text;
                                cmdupdate.ExecuteNonQuery();

                                string message = "Data Uploaded Successfully !";
                                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + message + "');", true);
                                cmdupdate.Dispose();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("<script language=JavaScript>");
                builder.Append("alert(\"" + ex.Message.Replace("\r\n", "") + "\")");
                builder.Append("</script>");
                Page.ClientScript.RegisterStartupScript(this.GetType(), "FailAlert", builder.ToString());
            }

Best regards,

sam