Using two OleDbDataReaders at the same time

[Nathan Sokalski]

I have two OleDbDataReaders that I need to use at the same time. One of the
DataReadersis used inside a While loop. My code is as follows:

While eventreader.Read()

Response.Write(ControlChars.NewLine &
CStr(eventreader("eventdate")).PadRight(20) &
CStr(eventreader("eventname")).PadRight(32))

cmdregistered.CommandText = "SELECT SUM(attending) AS total FROM registered
WHERE eventid=" & CStr(eventreader("eventid"))

'eventreader.Close()

regreader = cmdregistered.ExecuteReader()

regreader.Read()

If regreader("total") Is System.DBNull.Value Then

Response.Write("0" & ControlChars.NewLine)

Else

Response.Write(CStr(regreader("total")) & ControlChars.NewLine)

End If

regreader.Close()

End While
When I use this code I recieve the following error:
There is already an open DataReader associated with this Connection which
must be closed first.
If I close the DataReader using the code which is commented out, I then
cannot reopen it. I did not have this problem when using an
OracleDataReader. The only solution I can think of would be to use a DataSet
rather than a DataReader for the first DataReader, but this would make
things less efficient. Does anyone know how to avoid this error? Thanks.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

[Frankie]

Have you tried using TWO different connections - one for each
OleDbDataReader?

"Nathan Sokalski" <nj********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I have two OleDbDataReaders that I need to use at the same time. One of the
DataReadersis used inside a While loop. My code is as follows:

While eventreader.Read()

Response.Write(ControlChars.NewLine &
CStr(eventreader("eventdate")).PadRight(20) &
CStr(eventreader("eventname")).PadRight(32))

cmdregistered.CommandText = "SELECT SUM(attending) AS total FROM
registered WHERE eventid=" & CStr(eventreader("eventid"))

'eventreader.Close()

regreader = cmdregistered.ExecuteReader()

regreader.Read()

If regreader("total") Is System.DBNull.Value Then

Response.Write("0" & ControlChars.NewLine)

Else

Response.Write(CStr(regreader("total")) & ControlChars.NewLine)

End If

regreader.Close()

End While
When I use this code I recieve the following error:
There is already an open DataReader associated with this Connection which
must be closed first.
If I close the DataReader using the code which is commented out, I then
cannot reopen it. I did not have this problem when using an
OracleDataReader. The only solution I can think of would be to use a
DataSet rather than a DataReader for the first DataReader, but this would
make things less efficient. Does anyone know how to avoid this error?
Thanks.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

[Miha Markic [MVP C#]]

Hi Nathan,

Frankie is right - you'll have to have a connection per open datareader.
BTW, .net 2/Sql server 2005 will have a feature called MARS which will allow
many reads per connection.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Nathan Sokalski" <nj********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

I have two OleDbDataReaders that I need to use at the same time. One of the
DataReadersis used inside a While loop. My code is as follows:

While eventreader.Read()

Response.Write(ControlChars.NewLine &
CStr(eventreader("eventdate")).PadRight(20) &
CStr(eventreader("eventname")).PadRight(32))

cmdregistered.CommandText = "SELECT SUM(attending) AS total FROM
registered WHERE eventid=" & CStr(eventreader("eventid"))

'eventreader.Close()

regreader = cmdregistered.ExecuteReader()

regreader.Read()

If regreader("total") Is System.DBNull.Value Then

Response.Write("0" & ControlChars.NewLine)

Else

Response.Write(CStr(regreader("total")) & ControlChars.NewLine)

End If

regreader.Close()

End While
When I use this code I recieve the following error:
There is already an open DataReader associated with this Connection which
must be closed first.
If I close the DataReader using the code which is commented out, I then
cannot reopen it. I did not have this problem when using an
OracleDataReader. The only solution I can think of would be to use a
DataSet rather than a DataReader for the first DataReader, but this would
make things less efficient. Does anyone know how to avoid this error?
Thanks.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/