Schedule dont display my Appointments in a Viewer

[Vuyiswa]

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

[Peter]

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.

[Vuyiswa]

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

[Vuyiswa]

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

[Vuyiswa]

Good Day

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

Thanks