none
There is already an open DataReader associated with this Command which must be closed first RRS feed

  • Question

  • Hello,

    I have seen some other forums on the same topic but still couldn't get anything better. I have the same problem with some difference:

    -> I am sure that am using the datareader closed and the connection as well with the help of cmd.ExecuteReader(CommandBehavior.CloseConnection)
    and in fact, this is the case that the whole project is working fine at my local machine setup and same few days back on the server. But today, get reported that SOMETIMES THIS PROBLEM GETS POPULATED, checked and found them correct.

    I can debug the application and troubleshoot all if can see on local machine but it directly showing on the DAL class on the hosting server and couldn't know how to trace the bug.

    Firstly please let me know how can the bug is dynamic. Is this the case that same connection is in use with multi-user environment as this is right that a number of users having their account for that same data entry page and using simultaneously. So, IS IT THE CASE THAT SAME CONNECTION IS IN USE. how this can be as these must be different processes not even the threads.

    Please help me and clear the doubt with this


    Thanks!!

    Sachin
    Thursday, April 30, 2009 11:51 AM

Answers

  • Sachin,

    In my opinion you should remove the share "Connection", from my experience this create a big mess. Intead, create a new connection on the fly in your runSProc function everytime you run.  That may seem inefficient but it is not.


    // Give this article a read, it's a good one
    http://weblogs.asp.net/bleroy/archive/2005/04/03/396916.aspx
    Friday, May 1, 2009 1:22 PM

All replies

  • Difficult to say what might be happening without seeing code. I would just make certain to recreate your DataReader and Command objects each time you need them. 


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, April 30, 2009 3:36 PM
  • You can get rid of this error by add this line to your connection string
    MultipleActiveResultSets=true;
    
    But I think you should check your logic, make sure you close the reader before use that same connection some where else.

    Good Luck!!
    Thursday, April 30, 2009 8:55 PM
  • I have tried this option but started getting new errors like Connection closed, and at the time of closing connection: it is not opened and like.
    Sachin Kalra
    Friday, May 1, 2009 4:34 AM
  •     private string ConnectionString;
        private SqlConnection con;
        public DAL()
        {
            //
            // TODO: Add constructor logic here
            //
            ConnectionString = ConfigurationManager.ConnectionStrings["sqlConnection"].ToString();
            con = new SqlConnection();
            con.ConnectionString = ConnectionString;
        }
        private void openConnection()
        {
            
    //        try
            {
                if(con.State!=ConnectionState.Open)
                    con.Open();
            }
      //      catch(exception e)
            {
        //        throw e;
            }
        }

        private void closeConnection()
        {
            if (con.State != ConnectionState.Closed)
                con.Close();
        }

        private SqlCommand getSqlCommand(CommandType ctype)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = ctype;
            cmd.CommandTimeout = 18000;
            openConnection();
            cmd.Connection = con;
            return cmd;
        }

        public static SqlParameter getParameter(string pname, string value)
        {
            return new SqlParameter(pname, value);
        }
        public SqlDataReader getDataReader(string sproc, params SqlParameter[] sqlParameter)
        {
            SqlCommand cmd = getSqlCommand(CommandType.StoredProcedure);
            //        SqlDataReader dr;
            cmd.CommandText = sproc;
            for (int i = 0; i < sqlParameter.Length; i++)
            {
                cmd.Parameters.Add(sqlParameter[i]);
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

    Sachin Kalra
    Friday, May 1, 2009 4:36 AM
  • I am closing the datareader after each use as make use of this.  This is surprising that working fine as if single user is working and on local machine. It looks that there something getting garbled with the connection object while using the multiple users. But as per my knowledge, these must be different processes and not going to share anything. This should work fine even if I use the static connection and the command but after this problem, not going to even think about all that. Here is some code of DAL, suggest the changes as u get something:



        public void runSProc(string sproc, params SqlParameter[] sqlParameter)
        {
            SqlCommand cmd = getSqlCommand(CommandType.StoredProcedure);
            cmd.CommandText = sproc;
            for (int i = 0; i < sqlParameter.Length; i++)
            {
                if (sqlParameter[i] != null)
                {
                    cmd.Parameters.Add(sqlParameter[i]);
                }
    //            else
    //          {
    //            break;
    //          }
            }
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            cmd = null;
            closeConnection();
        }


    Sachin Kalra
    Friday, May 1, 2009 4:41 AM
  • You could troubleshoot application that runs on server if you add Debug.Trace statements to your code and output tracing messages to, for example, text file. Then you could analyze produced log and see where it fails and potentially why.
    Val Mazur (MVP) http://www.xporttools.net
    Friday, May 1, 2009 10:07 AM
    Moderator
  • Where do you create your SqlDataReader? You should be closing it and setting it to null when you're done with it and then recreating it when you need to query the database again. I would think that code would be outside of your DAL.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, May 1, 2009 11:35 AM
  • Your might be right, but as you have see that I am using different Data Access Layer and the trace information get me on line
    Line 92:             cmd.Parameters.Add(sqlParameter[i]);
    Line 93: }
    Line 94: return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    Line 95: }
    Line 96:

    Source File: e:\domains\xxxxx.com\wwwroot\App_Code\DAL.cs    Line: 94

    this is not that I get it on my local machine. Now here how can I trap the point where it left unclosed. Could this be possible that as if a second user have submitting the form and his form is making some query to database with the help of datareader on the same time me too. As per my knowledge this should not be. All this made me confused as all this is quite different with the concept of OOP and the abstraction.

    And one thing let us assume that there is some command object or data reader left open, then why this is not showing any error on local machine. I am using SQL server 2005 and visual studio Express 2005.

    I am using the Business Logic Layer on different page and all error getting on DAL.CS if dll is not in use so how trace can be helpful for me.





    Sachin Kalra
    Friday, May 1, 2009 12:23 PM
  • The data readers are created as per requirement on admin and live pages and this is that am closing the data reader yes, not putting them to null. One thing would like to know CAN DATA READER ON DIFFERENT USER PRCESS INTERFEAR WITH EACH OTHER. I am not using any static connection or any static command or data reader object as shown on the code above.




    Sachin Kalra
    Friday, May 1, 2009 12:26 PM
  • Sachin,

    In my opinion you should remove the share "Connection", from my experience this create a big mess. Intead, create a new connection on the fly in your runSProc function everytime you run.  That may seem inefficient but it is not.


    // Give this article a read, it's a good one
    http://weblogs.asp.net/bleroy/archive/2005/04/03/396916.aspx
    Friday, May 1, 2009 1:22 PM
  • Well, will efficiency and methodology can never be implemented at the cost of any error. Will back as get the response from client but if it works will prove an unknown quite different approach of .net.

    Thanks for your suggestion, will be back in a few days after some testing.


    Thanks again.
    Sachin Kalra
    Saturday, May 2, 2009 5:27 AM