error 'there is already an open datareader associated with this command which must be closed first'

0 votes
asked Oct 1, 2010 by pradeep

runtime error 'there is already an open datareader associated with this command which must be closed first'

objCommand = new SqlCommand("SELECT field1, field2 FROM sourcetable", objConn);

objDataReader = objCommand.ExecuteReader();

while (objDataReader.Read())
{
objInsertCommand = new SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, '" + objDataReader[0] + "')", objConn);
objInsertCommand.ExecuteNonQuery();//Here is the error
}
objDataReader.Close();

I cannot define any stored procedure here. Any help would we appreciated.

12 Answers

0 votes
answered Jan 1, 2010 by david-aleu

Try something like this:

//Add a second connection based on the first one
SqlConnection objConn2= new SqlConnection(objConn.connectionString))

SqlCommand objInsertCommand= new SqlCommand();
objInsertCommand.CommandType = CommandType.Text;
objInsertCommand.Connection = objConn2;

while (objDataReader.Read())
{
    objInsertCommand.CommandText = "INSERT INTO tablename (field1, field2) VALUES (3, '" + objDataReader[0] + "')";
    objInsertCommand.ExecuteNonQuery();
}
0 votes
answered Oct 1, 2010 by brad

How about pulling the data into a DataSet via Fill and then iterate through that to perform your insert via NonQuery?

IDbDataAdapter da;
IDbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandType = CommandType.Text;
selectCommand.CommandText = "SELECT field1, field2 FROM sourcetable";
connection.Open();
DataSet selectResults= new DataSet();
da.Fill(selectResults); // get dataset
selectCommand.Dispose();
IDbCommand insertCommand;

foreach(DataRow row in selectResults.Tables[0].Rows)
{
    insertCommand = connection.CreateCommand();
    insertCommand.CommandType = CommandType.Text;
    insertCommand.CommandText = "INSERT INTO tablename (field1, field2) VALUES (3, '" + row["columnName"].ToString() + "'";   
}
insertCommand.Dispose();
connection.Close();
0 votes
answered Oct 1, 2010 by dan-dumitru

What version of SQL Server are you using? The problem might be with this:

(from http://msdn.microsoft.com/en-us/library/9kcbe65k.aspx)

When you use versions of SQL Server before SQL Server 2005, while the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader. While in this state, no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called.

So, if this is what's causing your problem, you should first read all the data, then close the SqlDataReader, and only after that execute your inserts.

Something like:

objCommand = new SqlCommand("SELECT field1, field2 FROM sourcetable", objConn);

objDataReader = objCommand.ExecuteReader();

List<object> values = new List<object>();
while (objDataReader.Read())
{
    values.Add(objDataReader[0]);
}

objDataReader.Close();

foreach (object value in values)
{
    objInsertCommand = new SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, '" + value + "')", objConn);
    objInsertCommand.ExecuteNonQuery();
}
0 votes
answered Oct 1, 2010 by joe-enos

You can't perform an action on that connection while it's still working on reading the contents of a data reader - the error is pretty descriptive.

Your alternatives are:

1) Retrieve all your data first, either with a DataSet or use the reader to populate some other collection, then run them all at once after the initial select is done.

2) Use a different connection for your insert statements.

0 votes
answered Oct 1, 2010 by steve-ellinger

Your best bet would be to read the information you need into a list and then iterating the list to perform your inserts like so:

        List<String> values = new List<String>();
        using(SqlCommand objCommand = new SqlCommand("SELECT field1, field2 FROM sourcetable", objConn)) {
            using(SqlDataReader objDataReader = objCommand.ExecuteReader()) {
                while(objDataReader.Read()) {
                    values.Add(objDataReader[0].ToString());
                }
            }
        }
        foreach(String value in values) {
            using(SqlCommand objInsertCommand = new SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, '" + value + "')", objConn)) {
                objInsertCommand.ExecuteNonQuery();
            }
        }
0 votes
answered Oct 1, 2010 by joe-enos
INSERT INTO tablename (field1, field2)
    SELECT 3, field1 FROM sourcetable

A single SQL statement instead of one per insert. Not sure if this will work for your real-life problem, but for the example you provided, this is a much better query than doing them one at a time.

On a side note, make sure your code uses parameterized queries instead of accepting strings as-is inside the SQL statement - your sample is open to SQL injection.

0 votes
answered Oct 21, 2011 by ali

don't need to do all that, just turn on MARS and your problem will get solved, in your connection string just add "MultipleActiveResultSets=True;"

0 votes
answered Oct 21, 2011 by user423430

Several suggestions have been given which work great, along with recommendations for improving the implementation. I hit the MARS limit due to existing code not cleaning up a reader so I wanted to put together a more respectable sample:

const string connectionString = @"server=.\sqlexpress;database=adventureworkslt;integrated security=true";
const bool useMARS = false;
using (var objConn = new System.Data.SqlClient.SqlConnection(connectionString + (useMARS ? ";MultipleActiveResultSets=True" : String.Empty)))
using (var objInsertConn = useMARS ? null : new System.Data.SqlClient.SqlConnection(connectionString))
{
 objConn.Open();
 if (objInsertConn != null)
 {
  objInsertConn.Open();
 }

 using (var testCmd = new System.Data.SqlClient.SqlCommand())
 {
  testCmd.Connection = objConn;
  testCmd.CommandText = @"if not exists(select 1 from information_schema.tables where table_name = 'sourcetable')
                          begin
                           create table sourcetable (field1 int, field2 varchar(5))
                           insert into sourcetable values (1, 'one')
                           create table tablename (field1 int, field2 varchar(5))
                          end";
  testCmd.ExecuteNonQuery();
 }

 using (var objCommand = new System.Data.SqlClient.SqlCommand("SELECT field1, field2 FROM sourcetable", objConn))
 using (var objDataReader = objCommand.ExecuteReader())
 using (var objInsertCommand = new System.Data.SqlClient.SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, @field2)", objInsertConn ?? objConn))
 {
  objInsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("field2", String.Empty));
  while (objDataReader.Read())
  {
   objInsertCommand.Parameters[0].Value = objDataReader[0];
   objInsertCommand.ExecuteNonQuery();
  }
 }
}
0 votes
answered Oct 21, 2014 by jaanus

Adding this to connection string should fix the problem.

MultipleActiveResultSets=true
0 votes
answered Jan 17, 2016 by kush-kant-dayal-pune

Best Solution: There is only problem with your "CommandText" value. Let it be SP or normal Sql Query.

  • Check 1: The parameter value which you are passing in your Sql Query is not changing and going same again and again in your ExecuteReader.

  • Check 2: Sql Query string is wrongly formed.

  • Check 3: Please create simplest code as follows.

    string ID = "C8CA7EE2";
    string myQuery = "select * from ContactBase where contactid=" + "'" + ID + "'";
    string connectionString = ConfigurationManager.ConnectionStrings["CRM_SQL_CONN_UAT"].ToString(); 
    SqlConnection con = new SqlConnection(connectionString);
    con.Open();
    SqlCommand cmd = new SqlCommand(myQuery, con);
    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
    con.Close();
    
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...