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.

 

[Frankie]

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

 

[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.