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

  • Question

  • I write below code for updating Payment status but in the live server i am getting "There is already an open DataReader associated with this Command which must be closed first." this error. I am not used any Data Reader throught my application but i am receiving this error can anyone please help me. 

    ASP.net 4.0 and SQL Server 2008

    public void Method1(AdmissionSchema pAdmissionSchema)
            {
                string str = "";
                try
                {

                    objConn = SQLHelper.OpenConnection();
                    objTran = objConn.BeginTransaction();
                    str = "UPDATE Table SET FeeAmount =" + pAdmissionSchema.PayAmount + ", PayType ='" + pAdmissionSchema.PayType + "' WHERE (AppId = '" + pAdmissionSchema.Appid + "')";
                    int returnval = SQLHelper.ExecuteNonQuery(objConn, objTran, CommandType.Text, str);
                    objTran.Commit();
                }
                catch (Exception ex)
                {
                    objTran.Rollback();
                }
                finally
                {
                    SQLHelper.CloseConnection(objConn);
                }
            }

    Monday, July 29, 2013 11:24 AM

Answers

  • Hello kiranlad,

    I do a test using your code but a little change.However,it runs ok.Following is my code:

    public static void Method1()
            {
                SqlHelper sqlHelper = new SqlHelper();
                SqlConnection connection = null;
                SqlTransaction transaction = null;
                try
                {
                    connection = sqlHelper.OpenConnection();
                    string commandText = "update course set coursename = '" + "English" + "'";
                    transaction = connection.BeginTransaction();
                    sqlHelper.ExecuteNonQuery(connection, transaction, CommandType.Text, commandText);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    sqlHelper.CloseConnection(connection);
                }
            }
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    namespace ADONET
    {
        class SqlHelper
        {
            public SqlConnection OpenConnection()
            {
                //1.Create A Connection             
                string connectionString = "server=(localdb)\\V11.0;Integrated Security=SSPI;database=TestDataBase";
                SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
                connectionStringBuilder.Add("min pool size", 0);
                connectionStringBuilder.Add("max pool size", 1);
                SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString);
                connection.Open();
                return connection;
            }
            public void ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType type, string str)
            {
                SqlCommand command = new SqlCommand(str, connection, transaction);
                command.CommandType = type;
                int effectiveRow = command.ExecuteNonQuery();
            }
            public void CloseConnection(SqlConnection connection)
            {
                connection.Close();
            }
        }
    }


    If you can provide some more information about the calss SQLHelper,I think your peoblem will be solved better.

    Best Regards.

    Tuesday, July 30, 2013 2:18 AM

All replies

  • Hello kiranlad,

    I do a test using your code but a little change.However,it runs ok.Following is my code:

    public static void Method1()
            {
                SqlHelper sqlHelper = new SqlHelper();
                SqlConnection connection = null;
                SqlTransaction transaction = null;
                try
                {
                    connection = sqlHelper.OpenConnection();
                    string commandText = "update course set coursename = '" + "English" + "'";
                    transaction = connection.BeginTransaction();
                    sqlHelper.ExecuteNonQuery(connection, transaction, CommandType.Text, commandText);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    sqlHelper.CloseConnection(connection);
                }
            }
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    namespace ADONET
    {
        class SqlHelper
        {
            public SqlConnection OpenConnection()
            {
                //1.Create A Connection             
                string connectionString = "server=(localdb)\\V11.0;Integrated Security=SSPI;database=TestDataBase";
                SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
                connectionStringBuilder.Add("min pool size", 0);
                connectionStringBuilder.Add("max pool size", 1);
                SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString);
                connection.Open();
                return connection;
            }
            public void ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, CommandType type, string str)
            {
                SqlCommand command = new SqlCommand(str, connection, transaction);
                command.CommandType = type;
                int effectiveRow = command.ExecuteNonQuery();
            }
            public void CloseConnection(SqlConnection connection)
            {
                connection.Close();
            }
        }
    }


    If you can provide some more information about the calss SQLHelper,I think your peoblem will be solved better.

    Best Regards.

    Tuesday, July 30, 2013 2:18 AM
  • Hi kiranlad,

    From your description, I notice the issue you are experiencing is that System asks you to close a SqlDataReader that you never used.

    To troubleshoot this issue, we really need the source code to reproduce the problem, so that we can investigate the issue in house. It is not necessary that you send out the complete source of the SQLHelper class. We only need a simple sample to reproduce the problem. You can remove any confidential information or business logic from it.

    I look forward to hearing from you.

    Best Regards.

    Tuesday, July 30, 2013 9:20 AM