Data Reader 'System.InvalidOperationException' 1

[fluxdemon]

Hello!

The program throws the exception on the last line below:
An unhandled exception of type System.InvalidOperationException' occurred in myprog.exe

Additional information: There is already an open DataReader associated with this Connection which must be closed first.

Is there anything I am missing that would work?

----
string sql = "select * from GroupCalendar";
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

conn.Open();
SqlCommand myCommand = new SqlCommand(sql, conn);
SqlDataReader myReader = myCommand.ExecuteReader();

if (myReader.HasRows)
while (myReader.Read())
{
sql = "update calendar set groupid = " + myReader["igroupid"];
sql += " where appointment_id = " + myReader["iappointmentID"];
myCommand.CommandText = sql;

myCommand.ExecuteNonQuery(); <--------------

 

[chiph]

Only one datareader can be associated with a connection at any one time. You will need to make sure that your other datareader(s) are closed and have their .Dispose() method called before opening a new one.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[fluxdemon]

I did more research and found that the SqlCommand has a reader inside of it and

.ExecuteReader(CommandBehavior.CloseConnection);

turns it off? But when I added this into my code it gave the following error:

The type or namespace name 'CommandBehavior' could not be found (are you missing a using directive or an assembly reference?)

What is the required namespace?

 

[Durandal66]

From looking at your code, it seems you are attempting to modify your connection while you are still using it. There are a few ways around this.

1. If you will only have one row to work with, and know this for sure, just do the Read (in your case read in your two values, and build the string &quot;sql&quot. Remove the myCommand... modification from the Reader loop. When your Read has completed, close your SQL connection completly. Looking at your code this can probably be done with conn.Close(); Then modify myCommand however you want. If you need to work some more, remember to call conn.Open(). You can reuse all of your previous settings as you already declared them.

2. If you will be getting more then one row, store the strings in an array or some other data structure as your reader loops. Close your connection as before, then use the strings however you want.

Hope this helps.

-Andrew R.

 

[obislavu]

Try to use this &quot;pattern&quot;:

SqlConnection myAccessConn = null;
SqlCommand myAccessCommand = null;
SqlDataAdapter myDataAdapter = null;
DataTable dtQry = null;
SqlCommandBuilder CB = null;
try
{
dtQry = new DataTable();

myAccessConn = new SqlConnection (connectionString);
myAccessCommand = new SqlCommand();
myDataAdapter = new SqlDataAdapter();

myAccessConn.Open();
myAccessCommand.Connection = myAccessConn;
string sView = &quot;SELECT * FROM &quot; + &quot;GroupCalendar&quot;;
myAccessCommand.CommandText = sView;
myDataAdapter.SelectCommand = myAccessCommand;
myDataAdapter.Fill(dtQry, );
// Modify here the dtQry DataTable object
string sFilter = &quot;appointment_id = iappointmentID&quot;;
DataRows aRows[] = dtQry.Filer(sFilter);
foreach (DataRow dr in aRows)
{
dr[&quot;groupid&quot;]=dr[&quot;igroupid&quot;];
}
// Commit the changes
CB = new SqlCommandBuilder(myDataAdapter);
int count = myDataAdapterException.Update (dtQry);
}
catch (Exception e)
{
string sMsg = &quot;Error: &quot; + e.GetType() + e.Message;
trhow new(sMsg);
}
finally
{

if ( myAccessConn != null)
{
myAccessConn.Close();
myAccessConn.Dispose();
}
if (CB != null)
CB.Dispose();
dtQry= null;
myAccessCommand = null;
myDataAdapter = null;



}




}
-obislavu-