所以基本上它是一个asp日历，它显示了员工申请休假的次数，每次有人申请休假，槽号就会下降。这是工作正常，但我正在试图从数据库设置可用的插槽。因此，我在setshrinkage表上有一个数据列，它获取一些日期的人员编制集，并尝试从批准的带有人员编制的表中减去特定日期的人员编制。编制表如下所示。

例如，在2018年8月27日的日历中，我想用9减去批准表中2018年8月27日的计数。但是当我尝试这样做时，我得到了一个错误的对象引用，而不是设置为对象的示例。我知道这是因为剩余日期的数据为空。那我怎么才能让它工作呢？提前谢谢。

class Sample
{
    public DateTime Date { get; set; }
    public int SlotAvailable { get; set; }
    public int Pending { get; set; }
    public int HeadCount { get; set; }
} 

List<Sample> samples = new List<Sample>();

protected void FillLeaveplannerDataset()
{
    cal2.VisibleDate = cal2.TodaysDate;
    DateTime firstDate = new DateTime(cal2.VisibleDate.Year, cal2.VisibleDate.Month, 1).AddDays(-6);
    DateTime lastDate = new DateTime(cal2.VisibleDate.Date.AddMonths(1).Year, cal2.VisibleDate.Date.AddMonths(1).Month, 1).AddDays(7);
    dsleaveplanner = GetCurrentMonthData(firstDate, lastDate);
}

protected DateTime GetFirstDayOfNextMonth()
{
    int monthNumber, yearNumber;
    if (cal2.VisibleDate.Month == 12)
    {
        monthNumber = 1;
        yearNumber = cal2.VisibleDate.Year + 1;
    }
    else
    {
        monthNumber = cal2.VisibleDate.Month + 1;
        yearNumber = cal2.VisibleDate.Year;
    }
    DateTime lastDate = new DateTime(yearNumber, monthNumber, 1);
    return lastDate;
}

protected DataSet GetCurrentMonthData(DateTime firstDate, DateTime lastDate)
{
    DataSet dsMonth = new DataSet();
    MySqlConnection con = new MySqlConnection("Server=localhost;Database=mydb;Uid=myid;Pwd=abc123;");
    string caldate = "Select * From approved Where date >= @firstDate And date <= @lastDate And site=@site And skill=@skill And shift=@shift Group By date";
    MySqlCommand cmd = new MySqlCommand(caldate, con);
    cmd.Parameters.AddWithValue("@firstDate", firstDate);
    cmd.Parameters.AddWithValue("@lastDate", lastDate);
    cmd.Parameters.AddWithValue("@site", lblsite.Text);
    cmd.Parameters.AddWithValue("@skill", lblskill.Text);
    cmd.Parameters.AddWithValue("@shift", lblshift.Text);
    MySqlDataAdapter mysqlDataAdapter = new MySqlDataAdapter(cmd);

    try
    {
         mysqlDataAdapter.Fill(dsMonth);
    }
    catch { }

    return dsMonth;
}

protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
{
    foreach (DataRow dr in dsleaveplanner.Tables[0].Rows)
    {
        nextDate = (DateTime)dr["date"];
        MySqlConnection conn = new MySqlConnection("Server=localhost;Database=mydb;Uid=myid;Pwd=abc123;");
        string hc = "SELECT headCount FROM setshrinkage WHERE date = @date";
        MySqlCommand cmd1 = new MySqlCommand(hc, conn);
        cmd1.Parameters.AddWithValue("@date", nextDate);
        conn.Open();
        string hcount = cmd1.ExecuteReader().ToString();
        Int32 hcount1 = Convert.ToInt32(hcount);
        conn.Close();
        string cntdate = "SELECT COUNT(date) FROM approved WHERE date = @date";
        string cntdate2 = "SELECT COUNT(date) FROM pending WHERE date = @date";
        MySqlCommand cmd2 = new MySqlCommand(cntdate, conn);
        MySqlCommand cmd3 = new MySqlCommand(cntdate2, conn);
        cmd2.Parameters.AddWithValue("@date", nextDate);
        cmd3.Parameters.AddWithValue("@date", nextDate);
        conn.Open();
        string count = cmd2.ExecuteScalar().ToString();
        string count2 = cmd3.ExecuteScalar().ToString();
        var slot2 = Convert.ToInt32(count);
        Int32 slot3 = hcount1 - slot2;
        string slot4 = slot3.ToString();
        conn.Close();

        samples.Add(new Sample { Date = nextDate, SlotAvailable = slot4, Pending = count2, HeadCount = hcount1 });
    }

    if (samples.Any(x => x.Date == e.Day.Date))
    {
        e.Cell.BackColor = System.Drawing.Color.Orange;
        Environment.NewLine.ToString();
        e.Cell.ForeColor = System.Drawing.Color.Red;
        e.Cell.Font.Size = 9;
        e.Cell.Controls.Add(new LiteralControl("<p></p>Slot available:"));
        e.Cell.Controls.Add(new LiteralControl(samples.Where(x => x.Date == e.Day.Date).FirstOrDefault().SlotAvailable.ToString()));
        e.Cell.Controls.Add(new LiteralControl("<p></p>Pending:"));
        e.Cell.Controls.Add(new LiteralControl(samples.Where(x => x.Date == e.Day.Date).FirstOrDefault().Pending.ToString()));
    }
    else
    {
        e.Cell.Font.Size = 9;
        e.Cell.Controls.Add(new LiteralControl("<p>Slot available: </p>"));
        e.Cell.Controls.Add(new LiteralControl(samples.Where(x => x.Date == e.Day.Date).FirstOrDefault().HeadCount.ToString()));
        e.Cell.Controls.Add(new LiteralControl("<p></p>Pending: 0"));
    }
}

我用下面的代码找到了修复错误的方法，

protected DataSet GetCurrentMonthData(DateTime firstDate, DateTime lastDate)
    {
        DataSet dsMonth = new DataSet();
        MySqlConnection con = new MySqlConnection("Server=localhost;Database=mydb;Uid=myid;Pwd=abc123;");
        string caldate = "Select * From setshrinkage Where date >= @firstDate And date <= @lastDate Group By date";
        MySqlCommand cmd = new MySqlCommand(caldate, con);

        cmd.Parameters.AddWithValue("@firstDate", firstDate);
        cmd.Parameters.AddWithValue("@lastDate", lastDate);
        MySqlDataAdapter mysqlDataAdapter = new MySqlDataAdapter(cmd);
        try
        {
            mysqlDataAdapter.Fill(dsMonth);
        }
        catch { }
        return dsMonth;
    }

protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
{

    DateTime nextDate;
    if (dsleaveplanner != null)
    {

            foreach (DataRow dr in dsleaveplanner.Tables[0].Rows)
            {
                nextDate = (DateTime)dr["date"];
                var hcount = (dr["headCount"].ToString());
                Int32 hcount1 = Convert.ToInt32(hcount);

                MySqlConnection conn = new MySqlConnection("Server=localhost;Database=mydb;Uid=myid;Pwd=abc123;");
                string cntdate = "SELECT COUNT(date) FROM approved WHERE date = @date";
                string cntdate2 = "SELECT COUNT(date) FROM pending WHERE date = @date";
                MySqlCommand cmd2 = new MySqlCommand(cntdate, conn);
                MySqlCommand cmd3 = new MySqlCommand(cntdate2, conn);
                cmd2.Parameters.AddWithValue("@date", nextDate);
                cmd3.Parameters.AddWithValue("@date", nextDate);
                conn.Open();
                string count = cmd2.ExecuteScalar().ToString();
                string count2 = cmd3.ExecuteScalar().ToString();
                var slot2 = Convert.ToInt32(count);
                Int32 slot3 = hcount1 - slot2;
                string slot1 = Convert.ToString(slot3);
                string slot4 = slot3.ToString();
                conn.Close();

                samples.Add(new Sample { Date = nextDate, SlotAvailable = slot1, Pending = count2 });
                if (samples.Any(x => x.Date == e.Day.Date))
                {
                    Environment.NewLine.ToString();
                    e.Cell.Font.Size = 11;
                    e.Cell.Controls.Add(new LiteralControl("<p></p>Slot available:"));
                    e.Cell.Controls.Add(new LiteralControl(samples.Where(x => x.Date == e.Day.Date).FirstOrDefault().SlotAvailable.ToString()));
                    e.Cell.Controls.Add(new LiteralControl("<p></p>Pending:"));
                    e.Cell.Controls.Add(new LiteralControl(samples.Where(x => x.Date == e.Day.Date).FirstOrDefault().Pending.ToString()));

                }
                else
                {
                    e.Cell.ForeColor = System.Drawing.Color.Red;
                    e.Cell.Font.Size = 9;
                    e.Cell.Controls.Add(new LiteralControl("<p></p>Slot available:"));
                    e.Cell.Controls.Add(new LiteralControl(slot1));
                    e.Cell.Controls.Add(new LiteralControl("<p></p>Pending:0"));
                }
            }
    }

但我得到了一个完全不同的问题，如下图所示

标签重复如上所示。有人能帮我修一下吗？