This is a migrated thread and some comments may be shown as answers.

Dates stored in SQL Do Not Match Appointments

2 Answers 72 Views
Scheduler
This is a migrated thread and some comments may be shown as answers.
Chris
Top achievements
Rank 1
Chris asked on 14 Apr 2014, 11:12 PM

Hi,
I have been trying to solve this issue for three days now and it is driving me nuts.
I am sure it is just a setting that I have not set or something I have forgot to do so here I am looking for any help I can get.

Environment
Visual Studio 2010 ultimate
SQL 2008 Enterprise
asp.net
.net 4.0
Telerik.Web.UI, v2013.1.403.40

OK here is the scenario.
Add an appointment to RadScheduler and it works fine, you can carry out all the functions and RadScheduler handles the data fine. It shows appointments in the correct time slots and right dates.
If you look in the SQL Database at the data the Start and End dates and times do not match what RadScheduler shows.
If you enter an appointment before 9 am it shows with the date of the previous day. After 9 am has correct date but wrong time. None of the appoints have the correct times.
I have checked my Regional settings, set the correct TimeZoneID in RadScheduler and am now at my wits end.
The only reason I am worrying about it is that i need to access the data in the SQL database from another function and I cant gather the correct appointments by date or times as none of them match what's in the SQL Database.

I have attached some screen shots that better show the process and matching data. I have only a single appointment to help keep it simple.

I put a debug message in the RadScheduler Insert and Update appointment and this is the data I captured

This was for the 9 am appointment image (SQL1)
Update Start Time= 14/04/2014 11:30:00 PM
Update End Time 15/04/2014
Update Time Zone= E. Australia Standard Time
Update Duration= 00:30:00

This was for the 9:30 am appointment image (SQL2)
Update Start Time= 14/04/2014 11:00:00 PM
Update End Time 14/04/2014 11:30:00 PM
Update Time Zone= E. Australia Standard Time
Update Duration= 00:30:00

This is the 10 am appointment (SQL3)
Update Start Time= 14/04/2014 11:30:00 PM
Update End Time 15/04/2014
Update Time Zone= E. Australia Standard Time
Update Duration= 00:30:00

ASPX
____________________________________________________________________________________________________________
<telerik:RadScheduler ID="QuoteScheduler" runat="server" 
              OnClientDataBound="OnClientDataBound" 
              DataDescriptionField="Description" DataEndField="End" DataKeyField="ID" 
              DataRecurrenceField="RecurrenceRule" 
              DataRecurrenceParentKeyField="RecurrenceParentID" 
              DataReminderField="Reminder" DataStartField="Start" 
              DataSubjectField="Subject" Height="587px" Skin="Office2010Black" 
              TimeZoneID="E. Australia Standard Time" TimeZoneOffset="10:00:00" 
              DataSourceID="QuoteAppointSQL" RowHeight="22px" 
              StartInsertingInAdvancedForm="True" EnableTheming="True" 
              Font-Names="Calibri" Font-Size="Small" EditFormTimeFormat="hh:mm" HoursPanelTimeFormat="htt"
              FirstDayOfWeek="Monday" LastDayOfWeek="Sunday" DayStartTime="04:30:00" DayEndTime="19:00:00" 
              WorkDayStartTime="04:30:00"  WorkDayEndTime="19:00:00" EnableCustomAttributeEditing="True">
             
              <ExportSettings FileName="Quote Appointments.pdf">
                <Pdf PageHeight="297mm" PageWidth="210mm" PaperSize="A4" Producer="mrConcrete" />
              </ExportSettings>
              
              <AdvancedForm Modal="True" TimeFormat="hh:mm" EnableCustomAttributeEditing="True" />
              <ResourceTypes>
                <telerik:ResourceType DataSourceID="ResourceSQL" ForeignKeyField="UserID" KeyField="ID" Name="AdminUser" TextField= "FullName" />
              </ResourceTypes>
             
                <Localization AdvancedSubject="Appointment Subject" Save="Save Appointment" />
             
                <TimelineView ColumnHeaderDateFormat="ddd-MM" HeaderDateFormat="ddd-MM" />
                <DayView DayStartTime="04:00:00" WorkDayEndTime="19:00:00" WorkDayStartTime="04:00:00" />
                <MonthView HeaderDateFormat="MMMM, yyyy" />
             
   </telerik:RadScheduler>

________________________________________________________________________________________________________________
Code Behind in FormCreate

If e.Container.Mode <> SchedulerFormMode.Insert AndAlso e.Container.Mode <> SchedulerFormMode.Edit Then
      Dim Combo As RadComboBox = TryCast(e.Container.FindControl("ResAdminUser"), RadComboBox)
      FindName = GetData("Select FirstName from Users where ID=" & Me.UserNum.Text)
      FindName = FindName & " " & GetData("Select Surname from Users where ID=" & Me.UserNum.Text)
      If Combo.Text <= "" Then Combo.SelectedIndex = Combo.FindItemIndexByText(FindName)

      Dim StartDate As RadDatePicker = TryCast(e.Container.FindControl("StartDate"), RadDatePicker)
      StartDate.SelectedDate = Today

      Dim StartTime As RadTimePicker = TryCast(e.Container.FindControl("StartTime"), RadTimePicker)

      StartTime.TimeView.Interval = New TimeSpan(0, 30, 0)
      StartTime.TimeView.TimeFormat = "hh:mm tt"
      StartTime.TimeView.StartTime = TimeSpan.FromHours("4.00") 
      StartTime.TimeView.EndTime = TimeSpan.FromHours("18.00")
      StartTime.TimeView.Columns = 4

      StartTime.TimeView.HeaderText = "Start Time For " & FindName
      startTime.TimeView.DataList.DataSource = Nothing
      startTime.DataBind()

      Dim EndTime As RadTimePicker = TryCast(e.Container.FindControl("EndTime"), RadTimePicker)
      EndTime.TimeView.Interval = New TimeSpan(0, 30, 0)
      EndTime.TimeView.TimeFormat = "hh:mm tt"
      EndTime.TimeView.StartTime = TimeSpan.FromHours("04.00") 
      EndTime.TimeView.EndTime = TimeSpan.FromHours("19.00") 
      EndTime.TimeView.Columns = 4

      EndTime.TimeView.HeaderText = "End Time For " & FindName
      EndTime.TimeView.DataList.DataSource = Nothing
      EndTime.DataBind()
    End If
end if


I am not sure what else I can supply but I hope this enough for somebody much smarter then me to point me in the right direction of give me some hints and what I am doing wrong.

Thanks in advance
chris























2 Answers, 1 is accepted

Sort by
0
Plamen
Telerik team
answered on 17 Apr 2014, 08:04 AM
Hello Chris,

The issue you observe is caused by the fact that you use TimeZoneID and TimeZoneOffset properties. In this case the appointments are stored in the database in UTC time and are displayed in the timezone you have set. This behavior is intended in cases when you want to be able to set different timezones as in this on-line demo.

If you want to have the appointments displayed in the same time as they are declared in the datasource you have to remove both TimeZoneID and TimeZoneOffset properties.

Hope this will explain the issue.


Regards,
Plamen
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Chris
Top achievements
Rank 1
answered on 28 Apr 2014, 06:37 AM
Thanks Plamen,
I should have said thanks to you earlier but was so happy to get your answer I just ploughed on with work once you pointed out what I was doing wrong.
You were spot on and thanks, saved me from going completely mad ha ha

Cheers
Chris
Tags
Scheduler
Asked by
Chris
Top achievements
Rank 1
Answers by
Plamen
Telerik team
Chris
Top achievements
Rank 1
Share this question
or