locked
Invalid Operation exception was caught RRS feed

  • 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());
            }

    Monday, July 22, 2019 6:04 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2019 6:50 PM

All replies

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2019 6:50 PM
  • User-797751191 posted

    Hi

      On which line can u pls tell

    Thanks

    Tuesday, July 23, 2019 4:11 AM
  • 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

    Monday, July 29, 2019 10:19 AM