Hi there,
I reckon MARS is only supported by SQL Server 2005 / Oracle (and don't think
one would use OleDbProvider for SQL Server2005 - or based on his code he's
definitely ASP guy :D, and Oracle natively supports MARS so he probably would
not get this exception) Therefore Bob, you just have to close associated
reader before executing next query / stored procedure
OleDbCommand rsA = new OleDbCommand("S elect * from Authors",cnAcce ss);
OleDbDataReader drA = rsA.ExecuteRead er();
while(drA.Read( ))
{
sbL.Append("<tr ><td>"+drA["Name"].ToString()+"</td></tr>");
}
// close reader before executing next statement
drA.Close()
OleDbCommand rsT = new OleDbCommand("S elect * fromTitles",cnA ccess);
OleDbDataReader drT = rsT.ExecuteRead er();
while(drT.Read( ))
{
sbL.Append("<tr ><td>"+drT["Name"].ToString()+"</td></tr>");
}
Additional resolution would be to execute statements in one batch and move
to next result set:
OleDbCommand rsA = new OleDbCommand(
"Select * from Authors; GO; " +
"Select * fromTitles; GO;",cnAccess) ;
OleDbDataReader dr = rsA.ExecuteRead er();
while(dr.Read() )
{
sbL.AppendForma t("<tr><td>{0 }</td></tr>", drA["Name"].ToString());
}
dr.NextResult() ;
while(dr.Read() )
{
sbL.AppendForma t("<tr><td>{0 }</td></tr>", drT["Name"].ToString());
}
hope this helps
--
Milosz
"Manish Bafna" wrote:
Hi,
What you are trying to do is possible in ADO.NET 2.0 with a feature called
MARS(Multiple Active Result Sets).Search in any serach engine for MARS and
you will get plenty of articles on it.
Thanks and regards,
Manish Bafna.
MCP and MCTS.
"Bob" wrote:
I need to get several recordset for which I'm opening a datareader
like so...
OleDbCommand rsA = new OleDbCommand("S elect * from Authors",cnAcce ss);
OleDbDataReader drA = rsA.ExecuteRead er();
while(drA.Read( ))
{
sbL.Append("<tr ><td>"+drA["Name"].ToString()+"</td></tr>");
}
This works fine......but when I try for the second recordset like
this....
OleDbCommand rsT = new OleDbCommand("S elect * fromTitles",cnA ccess);
OleDbDataReader drT = rsT.ExecuteRead er();
while(drT.Read( ))
{
sbL.Append("<tr ><td>"+drT["Name"].ToString()+"</td></tr>");
}
I get an error...I "fixed" it by closing...then re-opening the
connection....b ut do I need to???
Why can't I open the connection and re-use the connection over and
over until I have everything..... ..as a matter of fact....I should be
able to re-use the dr also....
Anybody know?
Bob Sweeney
