• Register
Welcome to Kodlogs, programming questions and answer website.
0 votes
10 views

Issue:

I am using following code and i get error message:

SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", connection);
SqlDataReader reader = cmd.ExecuteReader();
connection.Open();
if (reader != null)
{
      while (reader.Read())
      {
              //your code here
      }
}
reader.Close(); 
reader.Dispose(); 
connection.Close(); 

Error message:
System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.


   at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)


   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)


   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)


   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

by (330 points)  
edited by

1 Answer

0 votes

Reason :


This usually happens when the connection was tried to be used again just after reading using "SqlDataReader" the same connection was finished

So here exception is due to attempt to update a table using the used connection that was used for the reader.

Fix:

Wrap your DataContext in a using block and this issue is resolved.

Try to include .ToList() or .Include() in your code

using(SqlConnection connection = new SqlConnection("conn string"))
{

    connection.Open();

    using(SqlCommand cmd = new SqlCommand("SELECT * FROM Employee", conn))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader != null)
            {
                while (reader.Read())
                {
                    //Your code here
                }
            }
        } 

    } 


 

Further Readings:
http://msdn.microsoft.com/en-us/library/ms254509.aspx

http://msdn.microsoft.com/en-us/library/e80y5yhx.aspx

by (1.4k points)  
...