There is already an open DataReader associated with this Command which must be closed first.

[Ian Boyd]

It's complaining that i have an open DataReader associated with my command,
but i just created the command - there is no DataReader associated with it
at all, open or otherwise:

DbCommand command = conn.CreateCommand();
try
{
command.CommandText = cmdText;
return command.ExecuteReader();
}
finally
{
command.Dispose();
}

Why does it think that i already have an open data reader for this command?
i just constructed it! i do, on the other hand, have another open DataReader
associated with the connection.

Here's ADO code that does the same thing but doesn't crash:

var
conn: _Connection;
command1: _Command; rs1: _Recordset;
command2: _Command; rs2: _Recordset;
RecordsAffected: OleVariant;
begin
conn := CoConnection.Create();
conn.ConnectionString := "Provider=SQLOLEDB;Network Library=DBMSSOCN;Data
Source=mango;User ID=xx;Password=xy";
conn.Open('', '', '', 0);

//Open one recordset
command1 := CoCommand.Create();
command1.Set_ActiveConnection(conn);
command1.Set_CommandText("SELECT * FROM Products");
rs1 := command1.Execute(RecordsAffected, EmptyParam, 0);

//Open a second recordset, on the same connection
command2 := CoCommand.Create();
command2.Set_ActiveConnection(conn);
command2.Set_CommandText("SELECT * FROM Projects");
rs2 := command2.Execute(RecordsAffected, EmptyParam, 0);
end;


And here's an ADO.NET translation of the above that reproduces the bug:
DbConnection conn = new SqlConnection();
conn.ConnectionString = "Network Library=DBMSSOCN;Data Source=mango;User
ID=xx;Password=xy";
conn.Open();

//Open one DataReader
DbCommand command1 = conn.CreateCommand();
command1.CommandText = "SELECT * FROM Products";
DbDataReader reader1 = command1.ExecuteReader();
command1.Dispose();

//Open second DataReader, on the same connection
DbCommand command2 = conn.CreateCommand();
command2.CommandText = "SELECT * FROM Projects";
DbDataReader reader2 = command2.ExecuteReader();
command2.Dispose();


So if someone could please tell me how to do in ADO.NET what ADO can already
do, i would appreciate it.

 

[Kerry Moorman]

Ian,

You can't have 2 data readers open on the same connection.

One option would be to open the second data reader on another connection.

Kerry Moorman

 

[Ian Boyd]

You can't have 2 data readers open on the same connection.

Just so i'm clear: there is no way to achieve in ADO.NET what ADO can do?

 

[Kerry Moorman]

Ian,

There is a feature called MARS, Multiple Active Result Sets, that allows for
multiple open data readers on the same connection.

But personally, I would avoid that feature and just open multiple connections.

Of course, you can also continue to use classic ADO with .Net.

Kerry Moorman