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

Question

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.

Refer http://stackoverflow.com/questions/18475195/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c/20535263#20535263 — LCJ, Dec 12 '13 at 05:01

score 81 · Answer 1 · edited Jan 31 '18 at 06:01

81

No need to do all that, just turn on MARS and your problem will get solved. In your connection string just add MultipleActiveResultSets=True;

edited Jan 31 '18 at 06:01

Adrita Sharma

21,581
10
69
79

answered Apr 21 '11 at 08:37

ali

811
6
2

1

Verified this with SQL 2008. http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/78d3989a-8975-4930-998d-1eb907966f57 – user423430 Oct 21 '11 at 21:06
1

Works for me. SQL Server 2008. – finitenessofinfinity Sep 08 '12 at 14:58
Works for me. SQL Server 2008. – Swag Nov 07 '13 at 15:54
MARS cannot be used when using "context connection=true" in CLR-procedures: http://social.msdn.microsoft.com/Forums/en-US/2575accc-72ac-4fab-99c1-1683afa39d72/context-connection-and-multipleactiveresultsets-can-have-both-at-the-same-time?forum=sqlnetfx – Rafael Emshoff Nov 15 '13 at 15:04
Refer http://stackoverflow.com/questions/18475195/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c/20535263#20535263 – LCJ Dec 12 '13 at 05:01
I verified this on sql server express 2008, I had got this problem after migrate a sql server compact edition 3.5 installation. – dlopezgonzalez Oct 01 '14 at 15:45

score 7 · Answer 2 · answered Oct 01 '10 at 13:39

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.

Brad · Accepted Answer · 2010-10-01T13:53:26.170

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();

could you please provide me the syntax or example? – Pradeep Oct 01 '10 at 13:36 — Pradeep, Oct 01 '10 at 13:36

score 4 · Answer 4 · answered Oct 01 '10 at 13:48

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();
            }
        }

+1 for the only answer so far that's safely disposing of the Commands and the Reader — PHeiberg, Oct 03 '10 at 07:19

score 3 · Answer 5 · answered Oct 01 '10 at 23:10

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.

score 2 · Answer 6 · answered Sep 12 '17 at 13:04

Option 1: Must execute query and load data before running another query.

Option 2: Add MultipleActiveResultSets=true to the provider part of your connection string. See the example below:

<add name="DbContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=dbName;Persist Security Info=True;User ID=userName;Password=password;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

score 2 · Answer 7 · answered Oct 21 '11 at 21:21

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();
  }
 }
}

score 1 · Answer 8 · answered Apr 21 '14 at 19:03

1

Adding this to connection string should fix the problem.

MultipleActiveResultSets=true

answered Apr 21 '14 at 19:03

Jaanus

16,161
49
147
202

Dan Dumitru · Answer 9 · 2010-10-01T13:49:21.183

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();
}

score 0 · Answer 10 · edited Feb 17 '16 at 13:46

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();

Ahmet Gokdayi · Answer 11 · 2016-12-21T11:37:32.590

0

In order for it to be disposed easily i use the following coding-template :

`using (SqlConnection connection = new SqlConnection("your connection string"))
        {
            connection.Open();
            using (SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "Select * from SomeTable";
                using (SqlDataReader reader = cmd.ExecuteReader())
                {

                    if(reader.HasRows)
                    { 
                       while(reader.Read()){
                       // assuming that we've a 1-column(Id) table 
                       int id = int.Parse(reader[0].ToString()); 

                       }
                    }
                }
            } 
            connection.Close()
        }`

edited Dec 21 '16 at 11:37

answered Mar 28 '16 at 07:22

Ahmet Gokdayi

301
9
14

Did you open the connection before executing reader? Are you sure you're reading data correctly? – Ahmet Gokdayi Dec 16 '16 at 13:01
I've updated my answer, if it's not working still, please upload your code on Gist or something, so that i can be more helpful – Ahmet Gokdayi Dec 21 '16 at 11:38

David Aleu · Answer 12 · 2010-10-01T13:43:45.520

0

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();
}

edited Oct 01 '10 at 13:43

answered Oct 01 '10 at 13:36

David Aleu

3,922
3
27
48

This won't work - it's the same thing as what the OP is already doing. – Joe Enos Oct 01 '10 at 13:37

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

12 Answers12

Linked