Datasource to asp calendar

Question

I'm trying to bind mysql data to asp:calendar but its not working. I need to display the data in mysql table slotavailable column according to the date column. How can I get it into the calendar cells?

<asp:Calendar ID="cal2" runat="server" Width="50%" DayField="Date" OnDayRender="Calendar1_DayRender"
            BackColor="Orange" NextMonthText="Next" PrevMonthText="Prev" >
            <DayStyle CssClass="days" VerticalAlign="Top" Font-Name="Arial" Height="80px" BackColor="lightYellow"  />
            <TodayDayStyle BackColor="Orange"  />
            <OtherMonthDayStyle BackColor="LightGray" ForeColor="DarkGray"/>
</asp:Calendar>

Below would be the cs code to fetch data

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            cal2.VisibleDate = DateTime.Today;
            FillLeaveplannerDataset();
        }
    }
    protected void FillLeaveplannerDataset()
    {
        DateTime firstDate = new DateTime(cal2.VisibleDate.Year, cal2.VisibleDate.Month, 1);
        DateTime lastDate = GetFirstDayOfNextMonth();
        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;");
        MySqlCommand cmd = new MySqlCommand("SELECT * FROM dummy WHERE date >= @firstDate AND date < @lastDate", con);
        cmd.Parameters.Add(new MySqlParameter("@firstDate", firstDate));
        cmd.Parameters.Add(new MySqlParameter("@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 slot = dr["slotavailable"];
            if (nextDate == e.Day.Date)
            {
                e.Cell.BackColor = System.Drawing.Color.Pink;
            }
        }
    }
}
protected void Calendar1_VisibleMonthChanged(object sender,
MonthChangedEventArgs e)
{
    FillLeaveplannerDataset();
}

How can get the slot column data into the calendar cell?

I found the mistake, It only changes color of dates that are in table. can anyone help how I can add text and read the slotavailable column? — prkash, Aug 10 '18 at 09:07
does your `dsleaveplanner` have proper data in between `firstDate` and `lastDate`? — er-sho, Aug 10 '18 at 09:56
Yes it even changes the color of the date present in datatable — prkash, Aug 10 '18 at 09:58

er-sho · Accepted Answer · 2018-08-10T10:35:15.557

2

You please make sure that your nextDate and e.Day.Date are matches and then change in your code like

e.Cell.Controls can add any text to your cell

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 slot = dr["slotavailable"];
            if (nextDate == e.Day.Date)
            {
                //This is the line where we add slotavailable column data
                e.Cell.Controls.Add(new LiteralControl($"<p>{slot}</p>"));
                e.Cell.BackColor = System.Drawing.Color.Pink;
            }
        }
    }
}

The output will be

The advantage of e.Cell.Controls you may add html button or span or image or any else as your need

And e.Cell.Controls.Clear(); may help you to clear all controls related to particular cell

Try once may it help you

edited Aug 10 '18 at 10:35

answered Aug 10 '18 at 10:26

er-sho

8,871
2
10
23

@prkash, If answer help you then accept it and vote up I also vote up to your question :) – er-sho Aug 10 '18 at 10:43
I have a different problem with this now, When I click any date or the submit button on the form, the data and the labels disappear – prkash Aug 13 '18 at 08:32
1

let me check and test once :) – er-sho Aug 13 '18 at 08:45
1

From my side its working fine regardless of dataset, please check your dataset is fill after postback, might be your dataset has been empty while postbacking – er-sho Aug 13 '18 at 10:10
You mean I should have FillLeaveplannerDataset() added inside an onselectionchanged event? Could you please explain?My code is exactly as shown above with your answer included. If you don't mind could you please post your code? – prkash Aug 13 '18 at 10:21
It is a really big code with more than 1000 lines with lots of if else ladders. i don't know if I can post it here, Wait I'll minimize some of them and send image – prkash Aug 13 '18 at 10:27
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/177938/discussion-between-prkash-and-ershoaib). – prkash Aug 13 '18 at 10:33
Hey bro, could you please take a look at this post? https://stackoverflow.com/questions/55240616/pass-mysql-distinct-values-into-a-c-sharp-string – prkash Mar 19 '19 at 12:04

Datasource to asp calendar

1 Answers1