when putting multiple sql commands showing error :There is already an open DataReader associated with this Command which must be closed first

Question

Friends, when I write these code in asp.net I get an error like this:

There is already an open DataReader associated with this Command which must be closed first

help me please

SqlConnection con = obj.getcon();
con.Open();

       SqlCommand cmd = new SqlCommand("select student_id from student_details", con);
       SqlDataReader dr = cmd.ExecuteReader();
       while (dr.Read())
       {
           String ss=dr[0].ToString();
           if (val == ss)
           {



               SqlCommand cmd1 = new SqlCommand("insert student_vs_testsession_details(student_id,testsession_id,testsession_status) values('" + val + "','" + Test + "','')", con);
               int val1 = cmd1.ExecuteNonQuery();


           }
           else
           {


               string message = "<script language=JavaScript> alert('StudentID does not exists!'); </script>";
               if (!Page.IsStartupScriptRegistered("clientScript"))
               {
                   Page.RegisterStartupScript("clientScript", message);
               }
           }

       } 
     dr.Close();
      // con.Close();

    }

Answer 1

You need to change your connection string and add this option

 "MultipleActiveResultSets=True;"

Starting from SQL Server 2005 there is MARS option.
With MARS a single opened connection could serve more than one command at a time. So, for example, your connection string should be like this

"Server=myServerAddress;" + 
"Database=myDataBase;" + 
"Trusted_Connection=True;" +
"MultipleActiveResultSets=true;"

See the docs on MARS

In a 'normal' configuration, when a SqlDataReader is open, the SqlConnection is busy serving the reader and cannot accept other commands.
(See remarks on the link to SqlDataReader).

Your code above has a reader open when you try to issue a command using the same connection.
There are workarounds like filling a DataSet and then looping over it (but for large sets this will impact performances), so the SQL Team at Microsoft introduced MARS