Schedule dont display my Appointments in a Viewer

5 posts, 1 answers
  1. Vuyiswa Maseko
    Vuyiswa Maseko avatar
    187 posts
    Member since:
    Jan 2010

    Posted 03 May 2010 Link to this post

    Good Day

    i am using  the Schedule to display appointments, i have the Following Data Example

    ID           Description     StartDate                              EndDate
    84337    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84338    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84339    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84340    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84341    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84342    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84343    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84344    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84345    CMG211C1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84346    CMH103Y1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84347    CMH103Y1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84348    CMH103Y1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    84349    CMH103Y1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000

    The Description is used as the Subject name and the start date is that and i id is obvious. When i run my Schedul , it does not bring me any appointments on the Viewer

    Thanks

  2. Answer
    Peter
    Admin
    Peter avatar
    6637 posts

    Posted 03 May 2010 Link to this post

    Hello Vuyiswa,

    Here is a sample table for RadScheduler appointments:
    http://www.telerik.com/help/aspnet-ajax/database-structure.html

    Also, I suggest you review the help topic on Data Binding.


    Regards,
    Peter
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Vuyiswa Maseko
    Vuyiswa Maseko avatar
    187 posts
    Member since:
    Jan 2010

    Posted 03 May 2010 Link to this post

    Good Day Admin

    I understand what you show me. i have downloaded a Video tutorial that guided me to a very simple Data binding procedure. Now this is like this. In order to bind the schedule you will need Four Database Fields.

    ID, Subject, StartDate, EndDate


    Now i have done a small example with Data and all this and it s working. Now i wanted to add this in my main application. The Difference between these scenarios , is that one is using a select statement and another is using a Stored procedure.

    The One with a Stored Procedure is the one i have problem , and i tested the Stored procedure in Isolation and it works and when i bind the Schedule i see the Stored procedure added data into the table and but there is no Data displayed in the Schedule.

    First my Stored Procedure looks like this

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER PROCEDURE [dbo].[sp_Timetable_View] @selectionType varchar(30),        -- either Venues, Staff, Subjects, Curricula etc...
    @selectedItems ntext, @selectedTerms ntext

    AS
     
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Final_Timetable]'))
    BEGIN
    drop table Final_Timetable
    END

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBL_LOG_VIEWER]'))
    BEGIN
    TRUNCATE TABLE TBL_LOG_VIEWER
    INSERT INTO TBL_LOG_VIEWER
    SELECT 'STEP 1' AS [STEP],'THE FINAL TABLE DROPPED "INSERT"                      ' AS [DESCRIPTION],GETDATE() AS [DATE]
    END
    ELSE
    BEGIN
    SELECT 'STEP 1' AS [STEP],'THE FINAL TABLE DROPPED "SELECT INTO"                 ' AS [DESCRIPTION],GETDATE() AS [DATE]
    INTO TBL_LOG_VIEWER
    END

    update MTM_ACTV_STAFF
    set number = 1
    where CycleTemplate is not null


    update MTM_ACTV_STAFF
    set number = 0
    where CycleTemplate is null

    --DROP TABLE #temp
    SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
    into #temp FROM TBL_STAFF S
    INNER JOIN MTM_ACTV_STAFF MTN ON
    S.ID = MTN.STAFF
    LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
    ON C.IDL = MTN.ID

    SELECT DESCR,ACTV,
    [CYCLETEMPLATE] = STUFF((SELECT ',' + CAST(CYCLETEMPLATE AS VARCHAR(MAX))
    FROM #temp
    WHERE DESCR = t.DESCR
    AND ACTV = t.ACTV Order by CYCLETEMPLATE
    FOR XML PATH('')),1,1,'')
     into #TempSummary
    FROM #temp t
    GROUP BY DESCR,ACTV

    UPDATE #TempSummary
    SET CYCLETEMPLATE  = T.Descr
    FROM #TempSummary TE
    INNER JOIN TBL_ACTV A
    ON TE.ACTV = A.ID
    INNER JOIN TBL_TERM T
    ON A.Term = T.ID
    INNER JOIN MTM_ACTV_STAFF MTM
    ON MTM.ACTV = A.ID
    WHERE MTM.NUMBER = 1

    Declare @xmldoc int
    EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedItems        --Create an internal representation of the XML document

    -- turn the xml into a table with characteristics of the given table
    SELECT *
    INTO #selectedItems
    FROM OPENXML ( @xmldoc , '/Root/Tags' , 2)
    WITH     (
            [TagID] int
        )

    EXEC sp_xml_removedocument @xmldoc

    create table #selectedActvs (
        id int
    )

    -- From the list of selected items of whichever type, find the list of activities these relate to; i.e. which activities are envolved in the selection
    if (@SelectionType = 'Venues') begin
    -- show the timetable for the selected activities
        Insert into #selectedActvs
            (id)
            select distinct Actv [Id]
            from sol_actv_venu
            where venu in ( select TagID from #selectedItems )
    end
    if (@SelectionType = 'Staff') begin
        Insert into #selectedActvs
            (id)
            select distinct Actv [Id]
            from sol_actv_staff
            where staff in ( select TagID from #selectedItems )        
    end
    if (@SelectionType = 'Subjects') begin
        Insert into #selectedActvs
            (id)
            select distinct Actv [Id]
            from sol_actv_time
            inner join tbl_actv a on a.id = sol_actv_time.Actv
            inner join tbl_cntc ct on ct.id = a.cntcID
            where modlID in ( select TagID from #selectedItems )        
    end
    if (@SelectionType = 'SubjectContainer') begin
        Insert into #selectedActvs
            (id)
            select distinct Actv [Id]
            from sol_actv_time
            inner join tbl_actv a on a.id = sol_actv_time.Actv
            inner join tbl_cntc ct on ct.id = a.cntcID
            inner join tbl_modl m on m.id=ct.modlid
            inner join mtm_modl_container mc on mc.modl=m.id
            where mc.container in ( select TagID from #selectedItems )        
    end
    if (@SelectionType = 'Curricula') begin
        Insert into #selectedActvs
            (id)
            select distinct sa.Actv [Id]
            from sol_actv_time sa
            inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
            inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
            inner join tbl_curr_strm c on c.id = cs.currstrmid
            where c.curr in ( select TagID from #selectedItems )        
    end            
    if (@SelectionType = 'Curriculum Streams') begin
        Insert into #selectedActvs
            (id)
            select distinct sa.Actv [Id]
            from sol_actv_time sa
            inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
            inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
            inner join tbl_curr_strm c on c.id = cs.currstrmid
            where c.id in ( select TagID from #selectedItems )        
    end            

    EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedTerms        --Create an internal representation of the XML document

    -- turn the xml into a table with characteristics of the given table
    SELECT *
    INTO #selectedTerms
    FROM OPENXML ( @xmldoc , '/Root/Tags' , 2)
    WITH     (
            [TagID] int
        )

    EXEC sp_xml_removedocument @xmldoc

    INSERT INTO TBL_LOG_VIEWER
    SELECT 'STEP 2'AS [STEP],'THE FINAL IS CREATED' AS [DESCRIPTION],GETDATE() AS [DATE]


    select distinct IDENTITY(int, 1,1) AS ID,tt.Dy, tt.Sess, m.descr as  [Code], m.LongName as[Description], ctyp.Abrev, ctyp.Descr as [Type], ct.Number,
    a.GrpName, a.GrpNumber, a.Duration,
    CASE WHEN MAV.stud IS NULL THEN a.Students
    ELSE MAV.STUD END as [Students],
    v.descr as [Venue], v.Capacity,
    s.descr as [Staff], m.id as [ModlID], t.Descr as [Term]
    , ISNULL(temp.CYCLETEMPLATE,t.Descr) as [StaffTerm]
    ,cast('2010-05-03 00:00:00.000' as datetime) as [STARTDATE] -- Cast(dates.descr as datetime)+ CONVERT(VARCHAR(8),GETDATE(),108) as [Date]
     ,cast('2010-05-03 00:00:00.000'as datetime) as [ENDDATE]
    ,a.Length as [Length]
    into Final_Timetable  
    from sol_actv_time tt
    inner join tbl_clmn dates on dates.id=tt.dy
    inner join tbl_actv a on a.id = tt.actv
    inner join #selectedActvs SelectedActvs on SelectedActvs.ID = a.id    -- filter the list of activies to be shown by the activities which fall in the selection
    inner join tbl_cntc ct on ct.id = a.cntcid
    inner join tbl_cntc_typ ctyp on ctyp.id = ct.cntctyp
    inner join tbl_modl m on m.id = ct.modlid
    inner join tbl_term t on t.id = a.term        --This will become t.id = actv.term when terms move to activities
    inner join #selectedTerms selTerms on selTerms.TagID = a.term
    left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id
    left outer join SOL_ACTV_VENU mav on mav.venu=ttVenu.Venu and mav.actv=a.id
    left outer join tbl_venue v on v.id = ttVenu.Venu
    left outer join sol_actv_staff ttStaff on ttStaff.Actv = a.id
    left outer join mtm_actv_staff mas on mas.actv=ttStaff.actv and mas.staff=ttStaff.Staff
    left outer join tbl_term staffTerm on staffTerm.id=mas.cycletemplate
    left outer join tbl_staff s on s.id = ttStaff.Staff
    left outer join [#TempSummary] temp on temp.descr = s.descr and temp.actv = a.id
    order by tt.dy, tt.sess


    DECLARE @Count int
    set @Count = (select  count(*)
    from sol_actv_time tt
    inner join tbl_clmn dates on dates.id=tt.dy
    inner join tbl_actv a on a.id = tt.actv
    inner join #selectedActvs SelectedActvs on SelectedActvs.ID = a.id    -- filter the list of activies to be shown by the activities which fall in the selection
    inner join tbl_cntc ct on ct.id = a.cntcid
    inner join tbl_cntc_typ ctyp on ctyp.id = ct.cntctyp
    inner join tbl_modl m on m.id = ct.modlid
    inner join tbl_term t on t.id = a.term        --This will become t.id = actv.term when terms move to activities
    inner join #selectedTerms selTerms on selTerms.TagID = a.term
    left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id
    left outer join SOL_ACTV_VENU mav on mav.venu=ttVenu.Venu and mav.actv=a.id
    left outer join tbl_venue v on v.id = ttVenu.Venu
    left outer join sol_actv_staff ttStaff on ttStaff.Actv = a.id
    left outer join mtm_actv_staff mas on mas.actv=ttStaff.actv and mas.staff=ttStaff.Staff
    left outer join tbl_term staffTerm on staffTerm.id=mas.cycletemplate
    left outer join tbl_staff s on s.id = ttStaff.Staff
    left outer join [#TempSummary] temp on temp.descr = s.descr and temp.actv = a.id
    )



    INSERT INTO TBL_LOG_VIEWER
    SELECT 'STEP 3'[STEP],'THE COUNT  ' + CAST(@Count AS VARCHAR) AS [DESCRIPTION],GETDATE() AS [DATE]  
     
    INSERT INTO TBL_LOG_VIEWER
    SELECT 'STEP 4'[STEP],'STEP RUNNING THE QUERY' AS [DESCRIPTION],GETDATE() AS [DATE]  

    INSERT INTO TBL_LOG_VIEWER
    SELECT 'STEP 5'[STEP],'STEP ENDING THE QUERY' AS [DESCRIPTION],GETDATE() AS [DATE] 




    SELECT  ID, Dy,  Sess,[Code],[Description],Description AS [SUBJECTS],Abrev,[Type],Number,
    GrpName,GrpNumber,Duration,[Students],
    [Venue],Capacity,
    [Staff],[ModlID],[Term]
    ,[StaffTerm]
    ,CAST(STARTDATE AS DATETIME) as [STARTDATE], CAST(ENDDATE AS DATETIME) AS [ENDDATE],[Length]
    FROM
    Final_Timetable




    now the Sp runs the last statement and the sample data is

    ID   Description         StartDate                              EndDate
    ========================================================
    1    ABT207L1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000
    3    ABT207L1    2010-05-03 00:00:00.000    2010-05-03 00:00:00.000


    this is not showing in today's Month or Day view of the Schedule

    and i have my example data that is working that has the following Data

    ID   Description         StartDate                              EndDate
    ========================================================
    7    NEW Subject    2010-05-06 15:18:06.000    2010-05-06 15:18:06.000
    23  ACN101M1       2011-10-06 00:00:00.000     2011-10-06 00:00:00.000


    if you can look at the Data is the same in a way. But the Example that i ran is working Perfectly , but the Stored procedure example is not working and  i investigated the data and its fine.

    The Definition of my Schedule in the markup is

    <telerik:RadScheduler ID="RadScheduler1" runat="server" DataSourceID="SqlDataSource2"
                DataEndField="ENDDATE" DataKeyField="ID" Skin="Sunset" Height="726px" DataSubjectField="SUBJECTS" DataStartField="STARTDATE">
            </telerik:RadScheduler>
           
    and the Definition of the Data-source Control is

     <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:UN_ViewerTestConnectionString2 %>"
                SelectCommand="sp_Timetable_View" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:Parameter Name="selectionType" Type="String" />
                    <asp:SessionParameter Name="selectedItems" SessionField="selectedItems" Type="String" />
                    <asp:SessionParameter Name="selectedTerms" SessionField="selectedTerms" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>


    I have been refereed to links that i have looked at before. I have no where to get a Solution than telerik Forum.

    Thanks

  5. Vuyiswa Maseko
    Vuyiswa Maseko avatar
    187 posts
    Member since:
    Jan 2010

    Posted 03 May 2010 Link to this post

    Good Day Admin ,

    I did not want to Update the Thread, i just wanted you or anyone reading the thread to be on track about the the progress of the challenge.

    As i said in the Previous thread that , the difference between the two examples is that one uses Text command, meaning a normal select to get data and the other is using a Storedprocedure. Now as i am using a sqldatasource control as shown in the example demos, all the examples i came across were using a normal select statement not a Storedprocedure. I changed my storedprocedure to create a solid table at the end of the the execution, i will call this sp before i show the page, and in the sqldatasource i changed it to look at the select statement from the table created by the storedprocedure. After i did that i could see my appointments.

    Now i hope this not what i think, because i will not be happy with this control.

    The End product of the storedprocedure was the select statement , that means the end product of the sp should be what is on the scheduler. but it seems like the Scheduler ignores the end product of the stored Procedure.  I had to create the solid table and query the appointments from it in order to see them.

    Can you confirm if the Scheduler can work with StoredProcedures as it can work with sqlDatasource control , that happens to have an option for storedprocedures ?

    I will not continue using the solid table , i have to use the #temp tables because this will be accessed by 1000's of people , so the solid table will not be an option.

    Thank you

  6. Vuyiswa Maseko
    Vuyiswa Maseko avatar
    187 posts
    Member since:
    Jan 2010

    Posted 05 May 2010 Link to this post

    Good Day

    I have found the Solution to my Problem. the SQl Data-source Control was not passing all the parameter.

    Thanks

Back to Top
UI for ASP.NET Ajax is Ready for VS 2017