There is already an open Data Reader associated with this Command which must be closed first Exception

Question

I am getting an exception called There is already an open Data Reader Associated with this command which must be closed first, I tried to look up solution on Google I tried using MARS=true in connection string and also kept everything inside USING but it didn't solved the problem. i get an Exception in line cm.ExecuteNonQuery();

public void UpdateActionSchedule(string actionScheduleKey, string note, string PEOPLE_CODE_ID)
{

    using (SqlConnection con = new SqlConnection("server=123; database=abc; user id=qwe; password=qwe;"))
    {
        con.Open();

        if (note == "" || note == null)
        {
            string UPDATE_COMPLETE = String.Format("UPDATE ACTIONSCHEDULE SET EXECUTION_DATE = '" + DateTime.Now + "', COMPLETED = 'Y', REVISION_OPID='WFLOW' where UNIQUE_KEY = '" + actionScheduleKey + "' and people_org_code_id='" + PEOPLE_CODE_ID + "'");
            SqlCommand cd = new SqlCommand(UPDATE_COMPLETE, con);
            cd.ExecuteNonQuery();
            cd.Dispose();
        }
        else
        {
            string oriNote = "";
            string GET_NOTE = String.Format("SELECT NOTE FROM ACTIONSCHEDULE WHERE people_org_code_id='{0}' and UNIQUE_KEY='{1}'", PEOPLE_CODE_ID, actionScheduleKey);
            using (SqlCommand cmd = new SqlCommand(GET_NOTE, con))
            {
                // SqlDataReader dr = cmd.ExecuteReader();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            oriNote = dr["NOTE"].ToString();
                        }

                        note = oriNote + " " + note;
                    } 

                    //string UPDATE = String.Format("UPDATE ACTIONSCHEDULE SET Note = '" + note + "' where UNIQUE_KEY = '" + actionScheduleKey + "' and people_org_code_id='" + PEOPLE_CODE_ID + "'");
                    //SqlCommand cm = new SqlCommand(UPDATE, con);
                    //cm.ExecuteNonQuery();
                    //cm.Dispose();

                    string UPDATE_COMPLETE = String.Format("UPDATE ACTIONSCHEDULE SET EXECUTION_DATE = '" + DateTime.Now + "',Note = '" + note + "', COMPLETED = 'Y', REVISION_OPID='WFLOW' where UNIQUE_KEY = '" + actionScheduleKey + "' and people_org_code_id='" + PEOPLE_CODE_ID + "'");
                    SqlCommand cmw = new SqlCommand(UPDATE_COMPLETE, con);

                    cmw.ExecuteNonQuery();

                    cmw.Dispose();
                }
            }
        }
    }
}

Answer 1

In the second half of the code, you have a loop over cmd / dr, and inside that loop, you use cmw with ExecuteNonQuery. That means you're trying to execute two commands at once. Since you've already completed the loop: just move that code outside the using on the dr.

However, it looks like you could also do all of this in a single round trip with better SQL.