sqldatareader error "Invalid attempt to call Read when reader is closed."

Question

1.00/5 (1 vote)

See more:

Hello there,
when I am reading data from sqldatareader I am getting error "Invalid attempt to call Read when reader is closed."
My code is

C#

string cid = "a";

            string s = "select Student_Id from tbstudent_batchmaster where Batch_Identity='" + batchid + "'";

            ClsConnection.Conn.Close();
            ClsConnection.Conn.Open();

            SqlCommand cmd = new SqlCommand(s, ClsConnection.Conn);

            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {

                cid = sdr.GetValue(0).ToString();
                
                Int32.TryParse(cid, out  actval);
               // fillstudentdata();

                ClsConnection.Conn.Close();
                ClsConnection.Conn.Open();

                dtstudent.ColumnHeadersVisible = true;
                dtstudent.RowHeadersVisible = false;
                dtstudent.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                string selectCommand1 = "select  FName+' '+LName as Name from ADD_Master where ADD_No='" + actval + "'";
                SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(selectCommand1, ClsConnection.Conn);
                SqlCommand cmd1 = new SqlCommand();

                cmd1.CommandText = selectCommand1;
                DataSet ds1 = new DataSet();
                sqlDataAdapter1.Fill(ds1);
                ClsConnection.Conn.Close();
                dtstudent.DataSource = ds1.Tables[0];
                DataGridViewCheckBoxColumn cb = new DataGridViewCheckBoxColumn();
                cb.HeaderText = "test";
                cb.Name = "test";
                cb.Visible = true;
                cb.Width = 40;
                dtstudent.Columns.Add(cb);
                dtstudent.Columns[0].Visible = false;
                dtstudent.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                dtstudent.Refresh();
                

            }
           

            ClsConnection.Conn.Close();
        }

can any one help ???? please.

Posted 25-Jan-12 1:37am

SantoshRohinSantosh

Updated 25-Jan-12 1:56am

lukeer

v2

Add a Solution

Comments

manognya kota 25-Jan-12 7:45am

hi,
There are lot of connections closing and opening in the read method.close the connection once the read is done and check.

SantoshRohinSantosh 25-Jan-12 7:54am

Thanks manognya
But if i close the connection then it is giving me following error.
There is already an open DataReader associated with this Command which must be closed first.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Catalin Serafimescu · Answer 1 · 2012-01-25T02:16:00

Solution 1

Don't play with connection at all. Reader will open and close the connection for you. Close the reader when finish - use 'using' statement to do it automatically

Posted 25-Jan-12 2:16am

Catalin Serafimescu

Comments

SantoshRohinSantosh 25-Jan-12 8:25am

Thanks Caralin but that is not working.Can u suggest any other option?

Catalin Serafimescu 25-Jan-12 8:26am

Paste the new code here.

See this example: http://msdn.microsoft.com/en-us/library/9kcbe65k.aspx. You need to open the connection, though - my bad.

SantoshRohinSantosh 25-Jan-12 8:38am

ClsConnection.Conn.Open();
getbatchid();

string cid = "a";

string s = "select Student_Id from tbstudent_batchmaster where Batch_Identity='" + batchid + "'";

SqlCommand cmd = new SqlCommand(s, ClsConnection.Conn);

SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{

cid = sdr.GetValue(0).ToString();

Int32.TryParse(cid, out actval);
//ArrayList attribut = new ArrayList();
//attribut.Add("actval");
//fillstudentdata();

//ClsConnection.Conn.Close();
//ClsConnection.Conn.Open();

dtstudent.ColumnHeadersVisible = true;
dtstudent.RowHeadersVisible = false;
dtstudent.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
string selectCommand1 = "select FName+' '+LName as Name from ADD_Master where ADD_No='" + actval + "'";
SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(selectCommand1, ClsConnection.Conn);
SqlCommand cmd1 = new SqlCommand();

cmd1.CommandText = selectCommand1;
DataSet ds1 = new DataSet();
sqlDataAdapter1.Fill(ds1);
//ClsConnection.Conn.Close();
dtstudent.DataSource = ds1.Tables[0];
DataGridViewCheckBoxColumn cb = new DataGridViewCheckBoxColumn();
cb.HeaderText = "test";
cb.Name = "test";
cb.Visible = true;
cb.Width = 40;
dtstudent.Columns.Add(cb);
dtstudent.Columns[0].Visible = false;
dtstudent.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dtstudent.Refresh();
dtstudent.DataSource = null;
dtstudent.Rows.Clear();
dtstudent.Columns.Clear();

}

ClsConnection.Conn.Close();

Catalin Serafimescu 25-Jan-12 8:43am

Oh, I understand now. You are trying to run another read on same connection !
Read about MARS: http://msdn.microsoft.com/en-us/library/cfa084cz(v=vs.80).aspx
And don't mess with dtStudent in loop.

Espen Harlinn 25-Jan-12 8:43am

5'ed! using statements is IMHO the best way to avoid resource leakage :)

here2learn.net · Answer 2 · 2012-01-25T02:19:00

Solution 2

hi, try removing all open and close and have just one open before your while loop and have only one close after your while loop, and see if that helps...

Posted 25-Jan-12 2:19am

here2learn.net

Comments

SantoshRohinSantosh 25-Jan-12 8:27am

HiIf i do that then it is giving me error
ExecuteReader requires an open and available Connection. The connection's current state is closed.

Espen Harlinn 25-Jan-12 8:43am

5'ed! you've spotted his major problem :)

SantoshRohinSantosh 25-Jan-12 9:37am

thanks it work

Espen Harlinn · Answer 3 · 2012-01-25T02:38:00

Solution 3

I think Catalin Serafimescu wants you to do things this way:

SqlConnection connection = new SqlConnection(connectionString);
using(connection) // will make certain that the connection is properly disposed
{
 connection.Open();
 SqlCommand command = connection.CreateCommand();
 using(command) // will make certain that the command is properly disposed
 {
  command.CommandText = sqlStatement;
  SqlDataReader dataReader = command.ExecuteReader();
  using(dataReader) // will make certain that the reader is properly disposed
  {
   while(dataReader.Read())
   {
     // process records. don't close and open the connection
   }
  }
 }
}

Best regards
Espen Harlinn

Posted 25-Jan-12 2:38am

Espen Harlinn

Updated 25-Jan-12 2:41am

v3

Comments

SantoshRohinSantosh 25-Jan-12 8:48am

What is sqlstatement in this?

Espen Harlinn 25-Jan-12 8:51am

the same as your 's' - I prefer sqlStatement as I feel this makes my intent clearer. Perhaps something like:
sqlStatement = "SELECT * from dbo.MyTable";

SantoshRohinSantosh 25-Jan-12 9:37am

thanks it work

Espen Harlinn 25-Jan-12 9:58am

Excellent :)

fjdiewornncalwe 25-Jan-12 9:57am

Agreed. +5

Espen Harlinn 25-Jan-12 9:58am

Thank you Marcus!