关于while(dr.read())的处理,求助

Question

 string txt = "select userno from v_users where DepartmentNo='" + this.ddlpart1.SelectedValue.ToString() + "' AND WorkState='1' AND  UserNo NOT IN(select UserNo from Dinner where Week='" + weeks + "' AND Year='" + this.DropDownList2.SelectedValue.ToString() + "' AND DepartmentNo='" + this.ddlpart1.SelectedValue.ToString() + "')";
        SqlCommand cmd0 = new SqlCommand(txt, con);
        SqlDataReader dr0=cmd0.ExecuteReader();
          while (dr0.Read())
        {
            
           string where = "where Week='" + weeks + "' AND address='" + this.ddlplace.SelectedValue.ToString() + "' and Year='" + year + "' AND DepartmentNo='" + this.ddlpart1.SelectedValue.ToString() + "' and UserNo IN('" + dr0["userno"].ToString() + "' )";
           
        //判断订餐周次是否订下周的餐
        if (dt.AddDays(7) < System.DateTime.Now)
        {
            Response.Write("<script>alert('請不要訂下周之前的餐！');</script>");
            return;
        }
        else
        {
            //////////////////////////////////////////////////////////////////////////
            string text = @"insert into Dinner(IDCardNo,UserNo,DepartmentNo,Region,DinnerDate,Year,Week,BookMorningCheck0,BookNoonCheck0,BookEveningCheck0,BookMorningCheck1,BookNoonCheck1,BookEveningCheck1,BookMorningCheck2,BookNoonCheck2,BookEveningCheck2,BookMorningCheck3,BookNoonCheck3,BookEveningCheck3,BookMorningCheck4,BookNoonCheck4,BookEveningCheck4,BookMorningCheck5,BookNoonCheck5,BookEveningCheck5,BookMorningCheck6,BookNoonCheck6,BookEveningCheck6,Address) Select CardNo,UserNo ,DepartmentNo, Region,'" + dt.ToShortDateString() + "'," + year.ToString() + "," + weeks.ToString() + ",0,0,0," +//周日
                     morning + "," + noon + "," + evening + "," +//周1
                     morning + "," + noon + "," + evening + "," +//周2
                     morning + "," + noon + "," + evening + "," +//周3
                     morning + "," + noon + "," + evening + "," +//周4
                     morning + "," + noon + "," + evening + "," +//周5
                     morning + "," + noon + "," + evening + ",'" +//周6
                     this.ddlplace.SelectedValue.ToString() + "' " + //就餐地點
                     "FROM V_Users WHERE DepartmentNo=@txtpartmentno AND WorkState='1' AND UserNo IN('"+dr0["userno"].ToString()+"')";            SqlCommand cmd = new SqlCommand(text, con);
            cmd.Parameters.Add("@txtpartmentno", SqlDbType.NVarChar, 50);
            cmd.Parameters["@txtpartmentno"].Value = this.ddlpart1.SelectedValue.ToString();
            cmd.ExecuteNonQuery();
……（后面的不写了）
出现错误，最后的cmd.ExecuteNonQuery();提示有未关闭的dr.read
原因应该是insert语句中条件使用的dr0["userno"].ToString()，如果我关闭，再读下一笔记录的时候会出现错误

Answer 1

使用与SqlDataReader同一个数据库连接的时候，SqlDataReader没关闭前不能使用该连接。
楼主的这种情况是在读取数据的同时还要操作数据库，有两办法
1、再打开一个连接进行保存操作
2、将上面的数据读取到DataSet中另外，你这里的代码
Response.Write(" <script>alert('請不要訂下周之前的餐！'); </script>");
            return; 
没关闭SqlDataReader之前就reutrn是不对的，
应该在return上面加上一句dr0.Close();使用DataReader一定要注意，用完就样，一定要关闭。