Multiple Inserts - "already an open DataReader associated with this Command"

the full error is:System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.i am trying to loop through a SqlDataReader and then on each pass use the data i got from it to SqlCommand.ExecuteNonQuery().

This works:SqlCommand objInsertCommand = new SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, 'Hello')", objConn);
objInsertCommand.ExecuteNonQuery();
objInsertCommand = new SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, 'Hello')", objConn);
objInsertCommand.ExecuteNonQuery();but when i stick it in a loop like this it doesnt: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();
}

objDataReader.Close();(i am not just copying data, this is a simplified version of my code) if there is a better way of getting the data and closing the datareader before the loop (eg recordset.GetRows() in asp3) or a way of batching all my inserts into one and running it after closing this datareader, i am open to that

EDIT: Currently i am just concatenating all my insert statements with a semicolon and appending to a StringBuilder then running that at the end, dont know if this is the best way of doing it though.

I'm far too new at ASP.NET to help you with the specific code, but perhaps using the DataAdapter class would work for you. Here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.asp) is some sample code that hopefully is what you need.

theres a msdn2 for .net2.0 btw now if u use it, it even works in browsers other than ie! :eek:

however, i think dataadapter is used to select records, manipulate them and then update the changes.. if im wrong please tell me :o

It is my understanding from the documentation in that link I posted that DataAdapter will do inserts, updates and deletes.

The Update method of the DataAdapter is called to resolve changes from a DataSet back to the data source. The Update method, like the Fill method, takes as arguments an instance of a DataSet, and an optional DataTable object or DataTable name. The DataSet instance is the DataSet that contains the changes that have been made, and the DataTable identifies the table from which to retrieve the changes.

When you call the Update method, the DataAdapter analyzes the changes that have been made and executes the appropriate command (INSERT, UPDATE, or DELETE).
Maybe this will help you and maybe it won't but it's worth looking at the documentation, I think.

I think loading your query into a dataset or datatable and then looping through each of the rows in the datatable would be a good way to handle it. Also, I think making a few stored procedures for you inserts would work out well. You could have your stored procedure names in an array, and then each time through your datatable you could grab your stored procedure from the array and pass in the values.

As for the error, your sql connection is already being used by your first query, and still is when you're looping through each record, so you'd most probably have to use another connection object in order to do it the way you currently are. On a side note, I don't know what version of SQL Server you're using but in SQL Server 2005 you can use MARS - Multiple Active Result Sets, which would probably eliminate this problem.

Good luck;

Create one more connection object, and use new con object for datareader.