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
[email]njsokalski@hotmail.com[/email]
[url]http://www.nathansokalski.com/[/url]

[Frankie]

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





"Nathan Sokalski" <njsokalski@hotmail.com> wrote in message
news:%23g3hcSavFHA.3984@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
> [email]njsokalski@hotmail.com[/email]
> [url]http://www.nathansokalski.com/[/url]
>

[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 [url]www.rthand.com[/url]
Blog: [url]http://cs.rthand.com/blogs/blog_with_righthand/[/url]

"Nathan Sokalski" <njsokalski@hotmail.com> wrote in message
news:%23g3hcSavFHA.3984@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
> [email]njsokalski@hotmail.com[/email]
> [url]http://www.nathansokalski.com/[/url]
>