C# – “Invalid attempt to call Read when reader is closed” when using a SqlDataReader

csqldatareader

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.

Best Answer

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);

Related Solutions

C# – sqldatareader error “Invalid attempt to call Read when reader is closed.”

The problem is that leaving the function (via the return statement) kicks you out of the using blocks, and so the SqlDataReader and SqlConnections you are using are disposed. To get around the problem, try changing the function signature like this:

public static IEnumerable<IDataRecord> ExecSqlDataReader( int queryType )

and then update the middle of the function like this:

using ( var reader = cmd.ExecuteReader() )
{
    while ( reader.Read() )
    {
        yield return reader;
    }
}

How you could use it:

var first10 = ExecSqlDataReader(0).Take(10);
foreach (var rec in first10)
{
    int ID = rec.GetInt32(0);
}

So this should work since the yield causes the Connection to keep alive:

Ruby – Multi-Line Comments in Ruby

#!/usr/bin/env ruby

=begin
Every body mentioned this way
to have multiline comments.

The =begin and =end must be at the beginning of the line or
it will be a syntax error.
=end

puts "Hello world!"

<<-DOC
Also, you could create a docstring.
which...
DOC

puts "Hello world!"

"..is kinda ugly and creates
a String instance, but I know one guy
with a Smalltalk background, who
does this."

puts "Hello world!"

##
# most
# people
# do
# this


__END__

But all forgot there is another option.
Only at the end of a file, of course.

This is how it looks (via screenshot) - otherwise it's hard to interpret how the above comments will look. Click to Zoom-in:

Best Answer

Related Solutions

C# – sqldatareader error “Invalid attempt to call Read when reader is closed.”

Ruby – Multi-Line Comments in Ruby

Related Topic