Using two OleDbDataReaders at the same time

Discussion in 'ASP .Net Datagrid Control' started by Nathan Sokalski, Sep 20, 2005.

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

    http://www.nathansokalski.com/
    Nathan Sokalski, Sep 20, 2005
    #1
    1. Advertising

  2. Nathan Sokalski

    Frankie Guest

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





    "Nathan Sokalski" <> wrote in message
    news:%...
    >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
    >
    > http://www.nathansokalski.com/
    >
    Frankie, Sep 20, 2005
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:%...
    >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
    >
    > http://www.nathansokalski.com/
    >
    Miha Markic [MVP C#], Sep 20, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shawn
    Replies:
    2
    Views:
    365
    =?Utf-8?B?RWx0b24gVw==?=
    Feb 19, 2006
  2. darrel
    Replies:
    2
    Views:
    464
    darrel
    Apr 5, 2006
  3. Replies:
    8
    Views:
    682
  4. flamesrock
    Replies:
    8
    Views:
    438
    Hendrik van Rooyen
    Nov 24, 2006
  5. id10t error
    Replies:
    3
    Views:
    715
    Alexey Smirnov
    Aug 31, 2009
Loading...

Share This Page