"Invalid attempt to call Read when reader is closed" when using a SqlDataReader

Question

1) I have the following codes:

private static sqlDataReader gCandidateList = null;

public SqlDataReader myCandidateList
{
    set
    {
        gCandidateList = value;
    }
    get
    {
        return gCandidateList;
    }
}

2) In FormA I have:

sqlConn.ConnectionString = mySettings.myConnString;
sqlConn.Open();
SqlCommand cmdAvailableCandidate = new SqlCommand(tempString, sqlConn);
SqlDataReader drAvailableCandidate = cmdAvailableCandidate.ExecuteReader();
mySettings.myCandidateList = drAvailableCandidate;
sqlConn.Close();

3) In FormB I want to reuse the data saved in myCandidatList so I use:

SqlDataReader drCandidate = mySettings.myCandidateList;
drCandidate.Read();

4) I then got the error "Invalide attempt to call Read when reader is closed."

5) I tried mySettings.myCandidateList.Read() in (3) above and again received the same error message.

6) How can I re-open SqlDataReader drCandidate to read data?

7) Would appreciate very much for advise and help, please.

A static DataReader is never a good idea.

SLaks
– SLaks

2012-01-24 02:40:56 +00:00
Commented Jan 24, 2012 at 2:40 — SLaks
– SLaks, Commented Jan 24, 2012 at 2:40

KV Prajapati · Accepted Answer · 2012-01-24 02:38:50Z

6

You can't read reader once the connection is closed or disposed. If you want to use those rows (fetch result) later in your code you need to create a List or DataTable.

For instance,

System.Data.DataTable dt = new System.Data.DataTable();
dt.Load(drAvailableCandidate);

answered Jan 24, 2012 at 2:38

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh P · Accepted Answer · 2012-01-24 02:49:31Z

If you want to use the datareader at later stage, you have to specify the same as a parameter to the ExecuteReader Method. Your code in FormA should be changed as below.

sqlConn.ConnectionString = mySettings.myConnString;  
sqlConn.Open();  
SqlCommand cmdAvailableCandidate = new SqlCommand(tempString, sqlConn);  
SqlDataReader drAvailableCandidate = cmdAvailableCandidate.ExecuteReader(CommandBehavior.CloseConnection);  
mySettings.myCandidateList = drAvailableCandidate;  
sqlConn.Close();

Make sure to dispose the datareader once it is used, as the connection to the database will be held open till the datareader is closed. Better change your code in FormB as below.

using (mySettings.myCandidateList)
{
mySettings.myCandidateList.Read();  
}

phoog · Accepted Answer · 2012-01-24 02:39:00Z

0

You're closing the connection before you attempt to read from the reader. That won't work.

answered Jan 24, 2012 at 2:39

phoog

43.3k6 gold badges84 silver badges125 bronze badges

Comments

James Shuttler · Accepted Answer · 2012-01-24 02:39:37Z

0

When you call Close on the SqlConnection object (sqlConn.Close();) it closes the connection and your data reader. That is why you are getting the error when you try to read from your SqlDataReader from FormB.

What you need to do is change the definition of your myCandidateList property to instead return a representation of the data that you have extracted from the your drAvailableCandidate reader.

Essentially what you need to do is iterate through the rows in the drAvailableCandidate object, extract the values and cache them in your property for later retrieval.

answered Jan 24, 2012 at 2:39

James Shuttler

1,37410 silver badges15 bronze badges

Comments

Richard · Accepted Answer · 2015-11-07 02:31:32Z

Just to add to the answers already given, if you're using async/await then it's easy to get caught out with this by not awaiting an operation inside a using block of a SqlConnection. For example, doing the following can give the reported error

public Task GetData()
{
    using(new SqlConnection(connString))
    {
        return SomeAsyncOperation();
    }
}

The problem here is we're not awaiting the operation inside the using, therefore it's being disposed of before we actually execute the underling async operation. Fairly obvious, but has caught me out before.

The correct thing to do being to await inside the using.

public async Task GetData()
{
    using(new SqlConnection(connString))
    {
        await SomeAsyncOperation();
    }
}

DaveDean1 · Accepted Answer · 2025-02-25 10:33:48Z

0

For me, this was a missing await when querying context, i.e I was trying to return an uncompleted Task<something> rather than a something, which is obviously impossible

answered Feb 25 at 10:33

DaveDean1

4594 silver badges12 bronze badges

Collectives™ on Stack Overflow

"Invalid attempt to call Read when reader is closed" when using a SqlDataReader

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related