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

Question

I have got following error in my following code:

Invalid attempt to call Read when reader is closed.

I test my code number of time but I have not found where is mistake in my code. Can anyone tell me where is my code incorrect or why this error occurs?

   /*start calculate grade of SA2 */
   SqlConnection con = new SqlConnection();
   con.ConnectionString = ConfigurationManager.ConnectionStrings["con1"].ConnectionString;
    if (Convert.ToString(ddlexam.SelectedItem) == "SA2")
    {


        int SA2;
        string grade = null;
        SqlCommand comm;
        SqlDataReader dr;
        con.Open();
        comm = new SqlCommand("select SA2 from terms_marks where admission_no=@admission_no and sub_id=@sub_id", con);
        comm.Parameters.AddWithValue("@sub_id", sub_id);
        comm.Parameters.AddWithValue("@admission_no", admission_no);
        dr = comm.ExecuteReader();
        while (dr.Read())
        {
            SA2 = Convert.ToInt32(dr["SA2"].ToString());
            markspercentage = (SA2 * 100) / maxmarks;
            if (markspercentage >= 91 && markspercentage <= 100)
            {
                grade = "A1";
            }
            else if ((markspercentage >= 81) && (markspercentage <= 90))
            {
                grade = "A2";
            }
            else if (markspercentage >= 71 && markspercentage <= 80)
            {
                grade = "B1";
            }
            else if (markspercentage >= 61 && markspercentage <= 70)
            {
                grade = "B2";
            }
            else if (markspercentage >= 51 && markspercentage <= 60)
            {
                grade = "C1";
            }
            else if (markspercentage >= 41 && markspercentage <= 50)
            {
                grade = "C2";
            }
            else if (markspercentage >= 33 && markspercentage <= 40)
            {
                grade = "D";
            }
            else if (markspercentage >= 21 && markspercentage <= 32)
            {
                grade = "E1";
            }
            else if (markspercentage >= 0 && markspercentage <= 20)
            {
                grade = "E2";
            }
        }
        dr.Close();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "CreategradeDetails";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@st_id", st_id);
        cmd.Parameters.AddWithValue("@roll_no", roll_no);
        cmd.Parameters.AddWithValue("@admission_no", admission_no);
        cmd.Parameters.AddWithValue("@sub_id", sub_id);

        cmd.Parameters.AddWithValue("@FA1", DBNull.Value);
        cmd.Parameters.AddWithValue("@FA2", DBNull.Value);
        cmd.Parameters.AddWithValue("@SA1", DBNull.Value);
        cmd.Parameters.AddWithValue("@Total_FirstTerm", DBNull.Value);
        cmd.Parameters.AddWithValue("@FA3", DBNull.Value);
        cmd.Parameters.AddWithValue("@FA4", DBNull.Value);
        cmd.Parameters.AddWithValue("@SA2", grade);
        cmd.Parameters.AddWithValue("@Total_SecondTerm", DBNull.Value);
        cmd.Parameters.AddWithValue("@Total_FA", DBNull.Value);
        cmd.Parameters.AddWithValue("@Total_SA", DBNull.Value);
        cmd.Parameters.AddWithValue("@GrandTotal", DBNull.Value);

        result = cmd.ExecuteNonQuery();
        con.Close();

    }
    /*end calculate SA2 grade */
    /* start calculate Total_SecondTerm grade;*/
    if (Convert.ToString(ddlexam.SelectedItem) == "SA2")
    {
        int TotalST;
        string grade = null;
        SqlCommand comm;
        SqlDataReader dr;
        con.Open();
        comm = new SqlCommand("select Total_SecondTerm from terms_marks where admission_no=@admission_no and sub_id=@sub_id", con);
        comm.Parameters.AddWithValue("@sub_id", sub_id);
        comm.Parameters.AddWithValue("@admission_no", admission_no);
        dr = comm.ExecuteReader();
        if (dr.Read())
        {
            TotalST = Convert.ToInt32(dr["Total_SecondTerm"].ToString());
            dr.Close();
            comm = new SqlCommand("select Total_SecondTerm from max_marks where admission_no=@admission_no and sub_id=@sub_id", con);
            comm.Parameters.AddWithValue("@sub_id", sub_id);
            comm.Parameters.AddWithValue("@admission_no", admission_no);
            if (dr.Read())
            {
                total_STmaxmarks = Convert.ToInt32(dr["Total_SecondTerm"].ToString());
                secondterm_percentage = (TotalST * 100) /total_STmaxmarks;


            }
            dr.Close();
            if (secondterm_percentage >= 91 && secondterm_percentage <= 100)
            {
                grade = "A1";
            }
            else if ((secondterm_percentage >= 81) && (secondterm_percentage <= 90))
            {
                grade = "A2";
            }
            else if (secondterm_percentage >= 71 && secondterm_percentage <= 80)
            {
                grade = "B1";
            }
            else if (secondterm_percentage >= 61 && secondterm_percentage <= 70)
            {
                grade = "B2";
            }
            else if (secondterm_percentage >= 51 && secondterm_percentage <= 60)
            {
                grade = "C1";
            }
            else if (secondterm_percentage >= 41 && secondterm_percentage <= 50)
            {
                grade = "C2";
            }
            else if (secondterm_percentage >= 33 && secondterm_percentage <= 40)
            {
                grade = "D";
            }
            else if (secondterm_percentage >= 21 && secondterm_percentage <= 32)
            {
                grade = "E1";
            }
            else if (secondterm_percentage >= 0 && secondterm_percentage <= 20)
            {
                grade = "E2";
            }

        }

        dr.Close();
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "CreategradeDetails";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@st_id", st_id);
        cmd.Parameters.AddWithValue("@roll_no", roll_no);
        cmd.Parameters.AddWithValue("@admission_no", admission_no);
        cmd.Parameters.AddWithValue("@sub_id", sub_id);

        cmd.Parameters.AddWithValue("@FA1", DBNull.Value);
        cmd.Parameters.AddWithValue("@FA2", DBNull.Value);
        cmd.Parameters.AddWithValue("@SA1", DBNull.Value);
        cmd.Parameters.AddWithValue("@Total_FirstTerm", DBNull.Value);
        cmd.Parameters.AddWithValue("@FA3", DBNull.Value);
        cmd.Parameters.AddWithValue("@FA4", DBNull.Value);
        cmd.Parameters.AddWithValue("@SA2", DBNull.Value);
        cmd.Parameters.AddWithValue("@Total_SecondTerm", grade);
        cmd.Parameters.AddWithValue("@Total_FA", DBNull.Value);
        cmd.Parameters.AddWithValue("@Total_SA", DBNull.Value);
        cmd.Parameters.AddWithValue("@GrandTotal", DBNull.Value);

        result = cmd.ExecuteNonQuery();
        con.Close();

    }
    /* end calculate Total_SecondTerm grade;*/

error in above code here

  comm.Parameters.AddWithValue("@sub_id",sub_id);   
  comm.Parameters.AddWithValue("@admission_no", admission_no);
      if(dr.Read())                           
       {
     total_STmaxmarks = Convert.ToInt32(dr["Total_SecondTerm"].ToString());

As a side note, you should refactor duplicate code (example the nested `if`'s) into a method that takes the percentage and returns a grade, maybe , `string GetGrade(int mark);` — Christian Phillips, Oct 29 '15 at 12:13

score 2 · Answer 1 · answered Oct 29 '15 at 12:02

2

The issue is here:

dr.Close();  //Here
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "CreategradeDetails";

You have closed the connection before the reader reads it. The reader will read only when the connection is open.

answered Oct 29 '15 at 12:02

Rahul Tripathi

168,305
31
280
331

thanks for are your reply issue is resolve – user2342574 Nov 03 '15 at 08:02

rory.ap · Answer 2 · 2015-10-29T12:36:34.330

You are closing the reader on this line:

dr.Close();

Then four lines later, you're doing this:

if (dr.Read())

The error message is pretty clear: you can't use the reader after you close it.

Note that this is the second instance of dr that you are using, not the first one -- it's the instance you create inside the if block where you have the comment:

/*end calculate SA2 grade */
/* start calculate Total_SecondTerm grade;*/

My suggestion would be that you clean up your code a little bit to reduce confusion that leads to problems and makes it harder to find them. Also, you should make use of the using statement with classes that implement the disposable pattern, like System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlCommand, and System.Data.SqlClient.SqlDataReader. For example, here's a clean way to write code for interacting with a data reader:

using (var conn = new System.Data.SqlClient.SqlConnection(usingConnectionString))
{
    using (var command = new System.Data.SqlClient.SqlCommand(usingQuery, conn))
    {
        using (var reader = command.ExecuteReader())
        {
            // do your work here...
        }
    }
}

Notice I don't ever call Close because that's done automatically once the code exists the using scope.

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

2 Answers2