Several Recordsets...Close the Connection Each Time?

[Bob]

I need to get several recordset for which I'm opening a datareader
like so...

OleDbCommand rsA = new OleDbCommand("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
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("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
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....but 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

 

[Guest]

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.

 

[Guest]

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 , 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("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
while(drA.Read())
{
sbL.Append("<tr><td>"+drA["Name"].ToString()+"</td></tr>");
}

// close reader before executing next statement
drA.Close()

OleDbCommand rsT = new OleDbCommand("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
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.ExecuteReader();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", drA["Name"].ToString());
}

dr.NextResult();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", drT["Name"].ToString());
}

hope this helps
--
Milosz

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.

I need to get several recordset for which I'm opening a datareader
like so...

OleDbCommand rsA = new OleDbCommand("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
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("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
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....but 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

 

[Guest]

Forgot to change "drA" / "drT" to "dr" in second snippet, should be:

OleDbCommand rsA = new OleDbCommand(
"Select * from Authors; GO; " +
"Select * fromTitles; GO;",cnAccess);
OleDbDataReader dr = rsA.ExecuteReader();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", dr["Name"].ToString());
}

dr.NextResult();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", dr["Name"].ToString());
}

Regards
--
Milosz

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 , 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("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
while(drA.Read())
{
sbL.Append("<tr><td>"+drA["Name"].ToString()+"</td></tr>");
}

// close reader before executing next statement
drA.Close()

OleDbCommand rsT = new OleDbCommand("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
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.ExecuteReader();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", drA["Name"].ToString());
}

dr.NextResult();

while(dr.Read())
{
sbL.AppendFormat("<tr><td>{0}</td></tr>", drT["Name"].ToString());
}

hope this helps
--
Milosz

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.

I need to get several recordset for which I'm opening a datareader
like so...

OleDbCommand rsA = new OleDbCommand("Select * from Authors",cnAccess);
OleDbDataReader drA = rsA.ExecuteReader();
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("Select * fromTitles",cnAccess);
OleDbDataReader drT = rsT.ExecuteReader();
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....but 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