When installing several sql commands showing an error: there is already an open DataReader associated with this Command, which should be closed first

Question

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

There is already an open DataReader associated with this Command, which should 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 the connection string and add this option

  "MultipleActiveResultSets=True;" 

Starting with SQL Server 2005, there is the MARS option.
With MARS, a single open connection can serve more than one command at a time. So, for example, your connection string should look something like this:

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

See documents at MARS

In a "normal" configuration, when SqlDataReader is open, SqlConnection is busy with the read service and cannot accept other commands.
(See Notes at the link to SqlDataReader).

There is a reader in the code above that opens when you try to execute a command using the same connection.
There are workarounds such as populating a DataSet and looping it (but for large sets it will affect performance), so the SQL team at Microsoft introduced MARS

Answer 2

I suggest you not use nested sql, which slows down application performance, but uses massive SQL insertion. Thus, you can change your code to:

 SqlConnection con = obj.getcon(); con.Open(); SqlCommand cmd1 = new SqlCommand("insert into student_vs_testsession_details(student_id,testsession_id,testsession_status) select student_id, " + "'Test','' from student_details where student_id = '" + val + "'", con); int val1 = cmd1.ExecuteNonQuery(); SqlCommand cmd = new SqlCommand("select student_id from student_details where student_id != " + val , con); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { String ss=dr[0].ToString(); string message = "<script language=JavaScript> alert('StudentID does not exists!'); </script>"; if (!Page.IsStartupScriptRegistered("clientScript")) { Page.RegisterStartupScript("clientScript", message); } } dr.Close(); 

Answer 3

You cannot execute multiple instruction instructions for the same connection while the datareader is on ... use SqlDataAdapter instead. Using the dataadapter and DataSet will make your work easier.