search envelope-o feed check
Home Unanswered Active Tags New Question
user comment-o

Having issue for daypilot calendar to display database values based on the date and time from database

Asked by Jordjmax
5 years ago.

Using daypilot calendar control. I am trying to display the values from database to the calendar. So currently in my codes I already specify the START_DATE and END_DATE column field. But I didn't add in codes for START_TIME and END_TIME as I don't know how to code it. And also in the daypilot properties on screenshot 2, I already put in START_DATE and END_DATE but how to put in START_TIME and END_TIME also.

Erm and my teacher says need to use the data type that is stated here for the database fields so I cannot change. So based on my codes, the output came out nothing like screenshot 3. and in my codes I already specify it as 3/4/2014, that means that whole week of 3/4/2014 will be displayed. screenshot 4 is the ideal timetable that I wanted.

MY SOURCE CODE

<DayPilot:DayPilotCalendar ID="DayPilotCalendar1" runat="server"
DataStartField="END_DATE"
DataEndField="START_DATE"
DataTextField="PURPOSE"
DataValueField="ID"
Days="5"
EventMoveHandling="CallBack" BackColor="#0066FF" BusinessBeginsHour="8"
BusinessEndsHour="21" CssOnly="False" EventBackColor="#66FF99"
HourBorderColor="Lime" HourHalfBorderColor="#0066FF"
HourNameBackColor="#6699FF" HourNameBorderColor="#0066FF" HoverColor="#0066FF"
NonBusinessBackColor="#0066FF"
style="top: 0px; left: 0px; margin-right: 6px;" DayEndsHour="6"
HeaderDateFormat="dddd" StartDate="2014-03-24"
>
</DayPilot:DayPilotCalendar>

MY CODE BEHIND CODE

public partial class number2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DayPilotCalendar1.StartDate = DayPilot.Utils.Week.FirstWorkingDayOfWeek(new DateTime(2014, 03, 24));
DayPilotCalendar1.DataSource = dbGetEvents(DayPilotCalendar1.StartDate, DayPilotCalendar1.Days);
DataBind();
}
}

private DataTable dbGetEvents(DateTime start, int days)
{
string constr = ConfigurationManager.ConnectionStrings["ProjectConnectionString"].ConnectionString;
SqlDataAdapter da = new SqlDataAdapter("SELECT ID, PURPOSE, [START_DATE], [END_DATE] FROM [Schedule] WHERE NOT (([END_DATE] <= @start) OR ([START_DATE] >= @end))", constr);
da.SelectCommand.Parameters.AddWithValue("start", start);
da.SelectCommand.Parameters.AddWithValue("end", start.AddDays(days));
DataTable dt = new DataTable();
da.Fill(dt);
return dt;

}
}

Answer posted by Dan Letecky [DayPilot]
5 years ago.

The START_DATE and END_DATE fields are correct but why do you store time separately in START_TIME and END_TIME?

The type of START_DATE is "datetime" and it will allow you to store both date and time parts.

You can store the time separately but you have to use an appropriate data type, i.e. "time" (storing time as float where 900 means 9:00 and 1600 means 16:00 is crazy). You would you have to join these two fields in the select so the full datetime is available in the fields specified using DataStartField (the same applies to DataEndField).

Comment posted by Jordjmax
5 years ago.

@Dan Letecky

I see understand. I already told my teacher about what you said, but he still said that the database is from a server so it can't be changed. He told me to do this instead. Select columndate cocat columntime as column3, then parse column3 as datetime datatype.

He meant to combine start_date and start_time as one column in the code, then convert it to datetime type also done in the code. Same goes for end date and end_time. Do you know how to do it?

Comment posted by Dan Letecky [DayPilot]
5 years ago.

If the start_time is of "time" type you can merge it with the "date" type directly in the SELECT command.

Otherwise, you will need to preprocess the DataTable before returning it from dbGetEvents() - go through all the Rows and update the field values as necessary.

This question is more than 3 months old and has been closed. Please create a new question if you have anything to add.