dbReader closing prematurely

Discussion in 'ASP .Net' started by tshad, Jan 11, 2006.

  1. tshad

    tshad Guest

    I have a section of code that is giving me an error:
    Invalid attempt to Read when reader is closed.
    Description: An unhandled exception occurred during the execution of the
    current web request. Please review the stack trace for more information
    about the error and where it originated in the code.

    Exception Details: System.InvalidOperationException: Invalid attempt to Read
    when reader is closed.

    Source Error:

    Line 1701: dbReader =
    Line 1702:trace.warn("Before dbReader.Read")
    Line 1703: while dbReader.Read() <-
    error is here 2nd time around
    Line 1704:trace.warn("inside dbRead.Read")
    Line 1705: redim parameters(6)

    My code is:
    Dim myDbObject as new DbObject()
    Dim dbReader As SqlDataReader


    dbReader = myDBObject.RunProcedure("GetScreenQuestions",parameters)
    trace.warn("Before dbReader.Read")
    while dbReader.Read()
    trace.warn("inside dbRead.Read")
    redim parameters(6)
    parameters(0) = new
    parameters(1) = new SqlParameter("@Question",SqlDBType.VarChar,250)
    parameters(2) = new SqlParameter("@QuestionType",SqlDBType.Char,2)
    parameters(3) = new SqlParameter("@SortOrder",SqlDBType.SmallInt)
    parameters(4) = new SqlParameter("@Answers",SqlDBType.SmallInt)
    parameters(5) = new SqlParameter("@Weight",SqlDBType.SmallInt)
    parameters(6) = new SqlParameter("@ScreenTemplateMasterID",SqlDBType.Int)

    parameters(0).Value = dbReader("ScreenQuestionsTemplateID")
    parameters(1).Value = dbReader("Question")
    parameters(2).Value = dbReader("QuestionType")
    parameters(3).Value = dbReader("SortOrder")
    parameters(4).Value = dbReader("Answers")
    parameters(5).Value = dbReader("Weight")
    parameters(6).Value = ScreenTemplateMasterID

    Call myDbObject.RunProcedure("CopyScreenTemplateFromTemplate",parameters)
    end while

    RunProcedure is my own Database Object.

    The first RunProcedure passes back a DataReader (dbReader) and it works fine
    the first time through.

    But apparently the call to RunProcedure is killing or closing dbReader. Why
    would that be?

    I am not reusing the DataReader, just using doing a Call.


    tshad, Jan 11, 2006
    1. Advertisements

  2. tshad

    Guest Guest

    A SqlDataReader is a forward-only "firehose" style data object. This means
    you only get to read through it "One Time". After you are done with the
    Reader, you need to Close it and optionally close the underlying
    SqlConnection if you haven't specified CommandBehavior.CloseConnection.

    Co-founder, Eggheadcafe.com developer portal:
    Guest, Jan 11, 2006
    1. Advertisements

  3. tshad

    tshad Guest

    That I understand.

    But if I comment out the RunProcedure in the While loop, it goes through it
    about 10 times (as there are 10 records).

    If I don't, I get the error on the 2nd While statement (which is after the
    RunProcedure was run for the first time). It appears to have closed the
    dbReader before I have read through it.


    tshad, Jan 11, 2006
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.