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

Question

Hello, 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.

Thanks,

Pradeep

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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.