• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9295
  • Last Modified:

Getting error: There is already an open DataReader associated with this Command which must be closed first.

I've used the 'using' method for my Sql calls for months and have had no problems. With this chunk of code I continue to get the following error at a certain point in my code.

"There is already an open DataReader associated with this Command which must be closed first."

I will mark the line of code where the error fires (Look for the comment line with the  ###### ERROR FIRES HERE   ####### in it). Any help would be greatly appreciated as I'm on a really tight deadline.

Could there be an unhandled 'reader' from the previous page?



using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
using System.Net.Mail;
using UserSession;

public partial class process_order : System.Web.UI.Page
{
  protected SessionVariables sv = new SessionVariables();

  protected void Page_Load(object sender, EventArgs e)
  {
    DateTime DateTimeApproved = DateTime.Now;

    string zApprovedDateTime = Convert.ToString(DateTimeApproved);
    string zResponseCodeAuth = null;
    string zAuthResponse = null;
    string zApproveCode = null;
    string zTransactionID = null;

    string zPayDetailID = null;
    string zFeeID = null;
    string zMstFi_Id = null;
    string zMF_Id = null;
    string zMF_Code = null;

    //-----------------------------------
    // Retrive the data we need from
    // our AuthResponseTable which is
    // made up of values returned to
    // us from Authorize.NET
    //------------------------------------
    foreach(DataRow r in sv.AuthResponseTable.Rows)
    {
      if(r["FieldNumber"].ToString() == "1")
        zResponseCodeAuth = r["Response"].ToString();

      if(r["FieldNumber"].ToString() == "4")
        zAuthResponse = r["Response"].ToString();

      if(r["FieldNumber"].ToString() == "5")
        zApproveCode = r["Response"].ToString();

      if(r["FieldNumber"].ToString() == "7")
        zTransactionID = r["Response"].ToString();
    }  

    using (SqlConnection cn = new SqlConnection(sv.DistConn.ConnectionString))
    {
      cn.Open();
     
      // This cmd inserts our data into the sPayDetail table
      using(SqlCommand cmd = new SqlCommand("INSERT INTO sPayDetail (sSchFi_Id, sParent_Id, sMstFi_Id, " +
                                                                    "SalesAmount, ResponseCodeAuth, " +
                                                                    "ApproveCode, TranDateTime, " +
                                                                    "TransactionID, AuthResponse, " +
                                                                    "CreditCardNo, CreditCardType) " +
                                            "VALUES ('" + sv.SchoolID + "', " +
                                                    "'" + sv.ParentID + "', " +
                                                    "'" + sv.StudentID + "', " +
                                                    "'" + sv.GrandTotal + "', " +
                                                    "'" + zResponseCodeAuth + "', " +
                                                    "'" + zApproveCode + "', " +
                                                    "'" + zApprovedDateTime + "', " +
                                                    "'" + zTransactionID + "', " +
                                                    "'" + zAuthResponse + "', " +
                                                    "'" + sv.CCNum + "', " +
                                                    "'FIX THIS')", cn))
      {
        cmd.ExecuteReader();
      }
     
      // This cmd gets our sPayDetailSysID
      using(SqlCommand cmd = new SqlCommand("SELECT sPayDetailSysID " +
                                            "FROM sPayDetail " +
                                            "WHERE TransactionID='" + zTransactionID + "' " +
                                            "AND TranDateTime='" + zApprovedDateTime + "'",cn))
      {
        using(SqlDataReader reader = cmd.ExecuteReader())  // ##### THIS IS WHERE ERROR FIRES #####
        {
          while(reader.Read())
          {
            zPayDetailID = Convert.ToString(reader.GetInt32(0));
          }
        }
      }

      // This cmd gets our MF_Id and MF_Code for the student
      using (SqlCommand cmd = new SqlCommand("SELECT MF_Id, MF_Code " +
                                    "FROM sMstFi " +
                                    "WHERE sMstFi_Id='" + sv.StudentID + "'", cn))
      {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
          while (reader.Read())
          {
            zMF_Id = Convert.ToString(reader.GetInt32(0));
            zMF_Code = Convert.ToString(reader.GetInt32(1));
          }
        }
      }

      //-----------------------------------------------------------
      // Now we iterate through our SelectedFeesTable and
      // do some decision making.
      //
      //    IF when we query for MstFi_Id
      //    in the sFeeDef table with the FeeID we get a 0
      //    (indicating that this is a generic or SchooDefined)
      //    fee.
      //
      //      We tag a new column to the end of the FeeDef
      //      table and we add a new row to the
      //      PayItem table which is tied to the PayDetail table.
      //
      //    IF when we query for MstFi_Id
      //    in the sFeeDef table with the FeeID find a
      //    VALID MstFi_Id.
      //
      //      We mark the Fee as paid, add TranDate and
      //      TranTime. We then add a row to the
      //      PayItem table which is tied to the
      //      PayDetail table.
      //-----------------------------------------------------------
      foreach(DataRow r in sv.SelectedFeesTable.Rows)
      {

        //--------------------------------------------------
        // Name and type of columns in sv.SelectedFeesTable
        // "FeeID" Type Int32  
        // "FeeGroup" Type String
        // "Comment" Type String
        // "Price" Type Decimal
        //--------------------------------------------------

        //--------------------------------------------------
        // PayItem Changes
        //
        // PayItem
        // DetailID
        // SchoolID
        // MstFiID
        // MF_Id
        // BGL_Id in (FeeDefItem)
        // DateTime (PayDetail)
        // SalesAmount (FeeDef)
        // Comment (from FeeDef)
        // Teacher (from FeeDef)
        //---------------------------------------------------

        //---------------------------------------------------
        // FeeDef
        //
        // Paid
        // PaidDate
        // PaidTranId
        //---------------------------------------------------

        // This cmd gets our sFeeDef_Id and our sMstFi_Id
        // based on the FeeID in the sv.SelectedFeesTable. We'll use this
        // to determine if this is a generic fee or a fee actually tied
        // to a student.
        using(SqlCommand cmd = new SqlCommand("SELECT sFeeDef_Id, sMstFi_Id " +
                                              "FROM sFeeDef " +
                                              "WHERE sFeeDef_Id = " + Convert.ToString(r["FeeID"]), cn))
        {
          using (SqlDataReader reader = cmd.ExecuteReader())
          {
            while (reader.Read())
            {
              zFeeID = Convert.ToString(reader.GetInt32(0));
              zMstFi_Id = Convert.ToString(reader.GetInt32(1));
            }
          }
        }

        // If the FeeID is tied to a student, mark paid and other stuff
        if (zMstFi_Id != "0" && zMstFi_Id != null)
        {
          using (SqlCommand cmd = new SqlCommand("UPDATE sFeeDef " +
                                                "SET Paid='1', " +
                                                    "PaidDate='" + zApprovedDateTime.ToString() + "' " +
                                                    "PaidTranId='" + zTransactionID + "' " +
                                                "WHERE sFeeDef_Id='" + zFeeID + "'", cn))
          {
            cmd.ExecuteReader();
          }
        }
        // If the FeeID is NOT tied to a student do the stuff in the ELSE clause
        else
        {
          // This cmd will get the MF_Id, and MF_Code of the Student
          using(SqlCommand cmd = new SqlCommand("INSERT INTO sFeeDef (sSchFi_Id, sParent_Id, sMstFi_Id, " +
                                                                     "MF_Id, MF_Code, FeeGroup, Comment, " +
                                                                     "Price, Paid, PaidDate, PaidTranID) " +
                                                "VALUES ('" + sv.SchoolID + "', " +
                                                        "'" + sv.ParentID + "', " +
                                                        "'" + sv.StudentID + "', " +
                                                        "'" + zMF_Id + "', " +
                                                        "'" + zMF_Code + "', " +
                                                        "'" + r["FeeGroup"] + "', " +
                                                        "'" + r["Comment"] + "', " +
                                                        "'" + r["Price"] + "', " +
                                                        "'1'" +
                                                        "'" + zApprovedDateTime + "'" +
                                                        "'" + zTransactionID + "')", cn))                                                                                                      
          {
            cmd.ExecuteReader();
          }
        }
        //----------------------------------------------------------
        // Now we've taken care of the FeeDef part. We now write
        // the data into our PayItem table
        //----------------------------------------------------------
        using(SqlCommand cmd = new SqlCommand("INSERT INTO sPayItem (sPayDetailSysID, sSchFi_Id, " +
                                                                    "sMstFi_Id, MF_Id, SaleDateTime, SaleAmount " +
                                                                    "Comment) " +
                                              "VALUES('" + zPayDetailID + "', " +
                                                     "'" + sv.SchoolID + "', " +
                                                     "'" + sv.StudentID + "', " +
                                                     "'" + zMF_Id + "', " +
                                                     "'" + zApprovedDateTime + "', " +
                                                     "'" + r["Price"] + "', " +
                                                     "'" + r["Comment"] + "')", cn))
        {
          cmd.ExecuteReader();
        }
      }  
    }    
  }
}
0
tatton777
Asked:
tatton777
  • 3
  • 2
  • 2
2 Solutions
 
YurichCommented:
ahm, it's not exactly previous i think, it's here:

      {
        cmd.ExecuteReader();
      }
     
      // This cmd gets our sPayDetailSysID
      using(SqlCommand cmd = new SqlCommand("SELECT sPayDetailSysID " +
                                            "FROM sPayDetail " +
                                            "WHERE TransactionID='" + zTransactionID + "' " +
                                            "AND TranDateTime='" + zApprovedDateTime + "'",cn))
      {
        using(SqlDataReader reader = cmd.ExecuteReader())  // ##### THIS IS WHERE ERROR FIRES #####

I guess you were going to use ExecuteNonQuery and accidently finished IntelliSense with Execute Reader...

regards,
yurich
0
 
tatton777Author Commented:
No, I meant to use the cmd.ExecuteReader so that I could use the while(reader.Read()) to populate my variable.

I've been using the method shown below for maninpulating my SQL components for a long time without any problems. But I must have broken the rules somewhere in my code. I just can't find where.

using(SqlConnection)
{
  using(SqlCommand)
  {
    using(SqlDataReader)
    {
    }
  }
  using(SqlCommand)
  {
    using(SqlDataReader)
    {
    }
  }
}

All the readers, commands, and connections, should be closed or disposed of automatically for me when using this method.
0
 
tatton777Author Commented:
If there is a better, or a 'best practices' way of doing this, I am really interested to know.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tatton777Author Commented:
I think I get it!!

When I am not using a SELECT in my command I should use the cmd.ExecuteNonQuery ... Right?
0
 
YurichCommented:
actually yes, if you're not going to return any values from your query, you use ExecuteNonQuery, if you're populating your datareader (reader data), you'd use ExecuteReader. For singular values, you can use ExecuteScalar.

If you're using data reader somewhere in your program - doesn't matter why, and it's not in your using block, you can just close your reader before you do any further data base access operations, but in your case - cmd.ExecuteReader - you can't do anything sine you don't even read INTO the reader...

actually, the bit I showed you didn't use "using"... I just copy-pasted this snipped out of your code. It's inside using cmdCommand, but SqlCommand doesn't close sql reader - you should do it explicitely in the using or through .Close().

good luck,
yurich
0
 
c_myersCommented:
It looks like you've got the answer for this, but I would like to add a side-note of best practices here.

When formulating your query, you're doing string concatenations (bad, but not terrible) and you're using string literals as the parameters (VERY VERY BAD).

This opens you up to SQL Injection at worse, but at best, you're killing your query performance because SQL Server cannot optimize ad-hoc queries.

You should be using Prepared Statements instead.

using( SqlConnection con = ... )
{
    con.Open();

    using( SqlCommand cmd = ... )
    {
        cmd.CommandText = "SELECT * FROM Foo WHERE FooID = @fooID";
        cmd.Parameters.Add("@fooID", myFoo.FooID );

        using( SqlDataReader rdr = cmd.ExecuteReader() )
        {
            while( rdr.Read() )
            {
                // TODO: do your dirty work here
            }
        }
    }
}

Also, doing direct ADO.NET work is a major pain in the butt, I strongly recommend you get the Data Access Application Block v1.x (the 2.x version is part of the Enterprise Library and requires a huge download just for a little functionality and isn't worth it anymore).

With SqlHelper (part of the DAAB), you can make your life a lot easier with the same code above looking like this:

string sqlStmt = "SELECT * FROM Foo WHERE FooID = @fooID";

using( SqlDataReader rdr = SqlHelper.ExecuteReader( connectStr,
       sqlStmt, myFoo.FooID ) )
{
    while( rdr.Read() )
    {
        //TODO: Dirty work here
    }
}
0
 
c_myersCommented:
One other thing, I forgot to tell you WHY you should be using prepared statements:

1.) SECURITY!!! No sql injections that way. What's a SQL injection you ask?
http://en.wikipedia.org/wiki/SQL_injection

2.) PERFORMANCE!!! SQL Server (7.0 and later) will optimize prepared statements and you get all the performance benefits equivalent-or-better-than stored procedures without all the SProc hassle.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now