Scheduler Datasourceview insert

[Shawn Davis]

I'm trying to modify the DragDrop example to my purposes.  Instead of updating an existing record I'd like to insert a new one.  But it's not working this is what I have.

private void ScheduleAppointment(string itemname, string vendor, string sortcode, decimal cost, DateTime start, DateTime end)

    {

        IDataSource dataSource = SchedulerDataSource;

        DataSourceView view = dataSource.GetView("DefaultView");

 

        IOrderedDictionary data = new OrderedDictionary();

        data.Add("ItemName", itemname);

        data.Add("Start", start);

        data.Add("End", end);

        data.Add("Vendor", vendor);

        data.Add("SortCode", sortcode);

        data.Add("Cost", cost);

 

        //IDictionary keys = new OrderedDictionary();

        //keys.Add("AppointmentID", id);

 

        view.Insert(data, OnDataSourceOperationComplete);

    }

<asp:SqlDataSource ID="SchedulerDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:Sitefinity %>"
    SelectCommand="SELECT [inventory_id], [item_date], [deadline_date], [item_name], [sort_code], [vendor] FROM [inventory] WHERE ([item_date] IS NOT NULL) AND ([deadline_date] IS NOT NULL)"
    InsertCommand="INSERT INTO [inventory] ([item_name], [item_date], [deadline_date], [vendor], [sort_code], [cost], [query_code], [committee_code]) VALUES (@ItemName, @Start, @End, @Vendor, @SortCode, @Cost, @Module, @Committee)"
    UpdateCommand="UPDATE [inventory] SET [item_date] = @Start, [deadline_date] = @End, [item_name] = @Subject WHERE inventory_id = @AppointmentID"
    DeleteCommand="DELETE FROM [inventory] WHERE [inventory_id] = @AppointmentID">
    <InsertParameters>
        <asp:Parameter Name="ItemName" Type="String" />
        <asp:Parameter Name="Start" Type="DateTime" />
        <asp:Parameter Name="End" Type="DateTime" />
        <asp:Parameter Name="Vendor" Type="String" />
        <asp:Parameter Name="SortCode" Type="String" />
        <asp:Parameter Name="Cost" Type="Decimal" />
        <asp:Parameter Name="Module" Type="String" DefaultValue="LUNCH" />
        <asp:Parameter Name="Committee" Type="String" DefaultValue="FOOD" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="ItemName" Type="String" />
        <asp:Parameter Name="Start" Type="DateTime" />
        <asp:Parameter Name="End" Type="DateTime" />
        <asp:Parameter Name="AppointmentID" Type="Int32" />
    </UpdateParameters>
    <DeleteParameters>
        <asp:Parameter Name="AppointmentID" Type="Int32" />
    </DeleteParameters>
</asp:SqlDataSource>

Thanks in advance!

[Plamen]

Hello Shawn,

Here is a sample project that shows the desired functionality.

Hope this will be helpful.

Best wishes,
Plamen Zdravkov
the Telerik team

[Shawn Davis]

Aside from some different column names, that's exactly what I have.  I'm adapting this code to work as a user control for a sitefinity 3.7 website.  Is there anything in the javascript (or elsewhere) that wouldn't play well in that environment?

I'm going to include all of my code just to save time.  Thanks very much again!

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="DragDropFoodScheduling.ascx.cs"
    Inherits="DragDropFoodScheduling" %>
<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
<telerik:RadScriptBlock ID="RadScriptBlock1" runat="server">

    <script type="text/javascript">


        function rowDropping(sender, eventArgs) {
            // Fired when the user drops a grid row
            var htmlElement = eventArgs.get_destinationHtmlElement();
            var scheduler = $find('<%= RadScheduler1.ClientID %>');

            if (isPartOfSchedulerAppointmentArea(htmlElement)) {
                // The row was dropped over the scheduler appointment area
                // Find the exact time slot and save its unique index in the hidden field
                var timeSlot = scheduler._activeModel.getTimeSlotFromDomElement(htmlElement);

                $get("TargetSlotHiddenField").value = timeSlot.get_index();

                // The HTML needs to be set in order for the postback to execute normally
                eventArgs.set_destinationHtmlElement("TargetSlotHiddenField");
            }
            else {
                // The node was dropped elsewhere on the document
                eventArgs.set_cancel(true);
            }
        }

        function isPartOfSchedulerAppointmentArea(htmlElement) {
            // Determines if an html element is part of the scheduler appointment area
            // This can be either the rsContent or the rsAllDay div (in day and week view)
            return $telerik.$(htmlElement).parents().is("div.rsAllDay") ||
                            $telerik.$(htmlElement).parents().is("div.rsContent")
        }

        function onRowDoubleClick(sender, args) {
            sender.get_masterTableView().editItem(args.get_itemIndexHierarchical());
        }
    </script>

</telerik:RadScriptBlock>
<input type="hidden" runat="server" id="TargetSlotHiddenField" />
<telerik:RadAjaxManager runat="server" ID="RadAjaxManager1">
    <AjaxSettings>
        <telerik:AjaxSetting AjaxControlID="RadScheduler1">
            <UpdatedControls>
                <telerik:AjaxUpdatedControl ControlID="RadScheduler1" />
            </UpdatedControls>
        </telerik:AjaxSetting>
        <telerik:AjaxSetting AjaxControlID="RadGrid1">
            <UpdatedControls>
                <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
            </UpdatedControls>
        </telerik:AjaxSetting>
    </AjaxSettings>
</telerik:RadAjaxManager>
<telerik:RadSplitter runat="server" ID="RadSplitter1" Height="652px" Width="880px"
    CssClass="exampleContainer" Orientation="Horizontal" Skin="Office2007">
    <telerik:RadPane runat="server" ID="TopPane" Height="500px" Scrolling="None">
        <telerik:RadScheduler ID="RadScheduler1" runat="server" Skin="Office2007" Height="100%"
            Width="100%" RowHeaderWidth="52" OverflowBehavior="Scroll" ShowFooter="false"
            SelectedView="MonthView" DataSourceID="SchedulerDataSource" DataKeyField="inventory_id"
            DataStartField="item_date" DataEndField="deadline_date" DataSubjectField="item_name"
            CustomAttributeNames="sort_code, vendor, cost" RowHeight="37px" OnAppointmentCommand="RadScheduler1_AppointmentCommand"
            FirstDayOfWeek="Monday" LastDayOfWeek="Friday">
            <AdvancedForm Modal="true" />
            <AppointmentTemplate>
                <%# Eval("Subject") %>
                <asp:Button runat="server" ID="UnscheduleAppointment" CssClass="simpleButton" CommandName="Unschedule"
                    Text=" " ToolTip="Unschedule this appointment" /></AppointmentTemplate>
            <TimeSlotContextMenuSettings EnableDefault="true" />
            <AppointmentContextMenuSettings EnableDefault="true" />
            <TimelineView UserSelectable="false" />
            <MultiDayView UserSelectable="true" />
            <DayView UserSelectable="false" />
            <WeekView UserSelectable="true" />
            <MonthView UserSelectable="true" />
        </telerik:RadScheduler>
    </telerik:RadPane>
    <telerik:RadSplitBar runat="Server" ID="RadSplitBar1" />
    <telerik:RadPane runat="server" ID="BottomPane">
        <%-- <div style="float: left; height: 100%; width: 55px; background: #E3EFFF; text-align: center;
            border-right: 1px solid #6593CF;">
        </div>--%>
        <div style="margin-left: 0px; border: none;">
            <telerik:RadGrid runat="server" ID="RadGrid1" DataSourceID="GridDataSource" GridLines="None"
                AutoGenerateColumns="False" OnRowDrop="RadGrid1_RowDrop" Skin="Office2007" Style="border: none;
                outline: 0" Height="100%" AllowAutomaticInserts="True" AllowAutomaticUpdates="true"
                ShowFooter="true" OnItemCreated="RadGrid1_ItemCreated" OnItemCommand="RadGrid1_ItemCommand"
                AllowSorting="true">
                <ClientSettings AllowRowsDragDrop="True">
                    <Selecting AllowRowSelect="True" />
                    <ClientEvents OnRowDropping="rowDropping" OnRowDblClick="onRowDoubleClick" />
                </ClientSettings>
                <MasterTableView DataKeyNames="ID" InsertItemDisplay="Bottom" EditMode="InPlace">
                    <SortExpressions>
                        <telerik:GridSortExpression FieldName="Vendor" SortOrder="Ascending" />
                    </SortExpressions>
                    <Columns>
                        <telerik:GridTemplateColumn DataField="ItemName" HeaderText="Item">
                            <ItemTemplate>
                                <asp:Label runat="server" ID="Label1" Text='<%# Bind("ItemName") %>'></asp:Label></ItemTemplate>
                            <FooterTemplate>
                                <asp:LinkButton Text="Add new menu item" CommandName="<%# RadGrid.InitInsertCommandName %>"
                                    runat="server" ID="LinkButton1"></asp:LinkButton></FooterTemplate>
                            <EditItemTemplate>
                                <asp:TextBox runat="Server" ID="TextBox1" Text='<%# Bind("ItemName") %>' Width="100%"></asp:TextBox></EditItemTemplate>
                        </telerik:GridTemplateColumn>
                        <telerik:GridBoundColumn DataField="Cost" HeaderText="Cost" SortExpression="Cost"
                            UniqueName="Cost" Visible="true">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="SortCode" HeaderText="SortCode" SortExpression="SortCode"
                            UniqueName="SortCode" Visible="true">
                        </telerik:GridBoundColumn>
                        <telerik:GridTemplateColumn DataField="Vendor" HeaderText="Vendor" HeaderStyle-Width="150px">
                            <ItemTemplate>
                                <asp:Label runat="Server" ID="Label2" Text='<%#Bind("Vendor") %>'></asp:Label></ItemTemplate>
                            <EditItemTemplate>
                                <telerik:RadComboBox runat="server" ID="priorityComboBox" SelectedValue='<%#Bind("Vendor") %>'
                                    Skin="Office2007" Width="80px" DataSourceID="SqlDSVendors" DataTextField="VendorName"
                                    DataValueField="VendorName" >
                                </telerik:RadComboBox>
                                <asp:LinkButton ID="btnUpdate" Text="Save" runat="server" CommandName='<%# (Container is GridDataInsertItem) ? RadGrid.PerformInsertCommandName : RadGrid.UpdateCommandName %>'></asp:LinkButton><asp:LinkButton
                                    ID="btnCancel" Text="Cancel" runat="server" CommandName='<%# RadGrid.CancelCommandName %>'></asp:LinkButton></EditItemTemplate>
                        </telerik:GridTemplateColumn>
                    </Columns>
                </MasterTableView></telerik:RadGrid></div>
    </telerik:RadPane>
</telerik:RadSplitter>
<asp:SqlDataSource ID="SchedulerDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:Sitefinity %>"
    SelectCommand="SELECT [inventory_id], [item_date], [deadline_date], [item_name], [sort_code], [vendor] FROM [inventory] WHERE ([item_date] IS NOT NULL) AND ([deadline_date] IS NOT NULL)"
    InsertCommand="INSERT INTO [inventory] ([item_name], [item_date], [deadline_date], [vendor], [sort_code], [cost], [query_code], [committee_code]) VALUES (@ItemName, @Start, @End, @Vendor, @SortCode, @Cost, @Module, @Committee)"
    UpdateCommand="UPDATE [inventory] SET [item_date] = @Start, [deadline_date] = @End, [item_name] = @Subject WHERE inventory_id = @AppointmentID"
    DeleteCommand="DELETE FROM [inventory] WHERE [inventory_id] = @AppointmentID">
    <InsertParameters>
        <asp:Parameter Name="ItemName" Type="String" />
        <asp:Parameter Name="Start" Type="DateTime" />
        <asp:Parameter Name="End" Type="DateTime" />
        <asp:Parameter Name="Vendor" Type="String" />
        <asp:Parameter Name="SortCode" Type="String" />
        <asp:Parameter Name="Cost" Type="Decimal" />
        <asp:Parameter Name="Module" Type="String" DefaultValue="LUNCH" />
        <asp:Parameter Name="Committee" Type="String" DefaultValue="FOOD" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="ItemName" Type="String" />
        <asp:Parameter Name="Start" Type="DateTime" />
        <asp:Parameter Name="End" Type="DateTime" />
        <asp:Parameter Name="AppointmentID" Type="Int32" />
    </UpdateParameters>
    <DeleteParameters>
        <asp:Parameter Name="AppointmentID" Type="Int32" />
    </DeleteParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="GridDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:Sitefinity %>"
    SelectCommand="SELECT [ID], [ItemName], [SortCode], [Vendor], [Cost] FROM [VendorMenu] WHERE ([Active] = 1)"
    UpdateCommand="UPDATE [VendorMenu] SET [ItemName] = @ItemName, [SortCode] = @SortCode, [Vendor] = @Vendor,  [Cost] = @Cost WHERE ID = @AppointmentID"
    InsertCommand="INSERT INTO [VendorMenu] ([ItemName], [SortCode], [Vendor], [Cost]) VALUES (@ItemName, @SortCode, @Vendor, @Cost)">
    <UpdateParameters>
        <asp:Parameter Name="ItemName" Type="String" />
        <asp:Parameter Name="SortCode" Type="String" />
        <asp:Parameter Name="Vendor" Type="String" />
        <asp:Parameter Name="Cost" Type="Decimal" />
        <asp:Parameter Name="AppointmentID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="ItemName" Type="String" />
        <asp:Parameter Name="SortCode" Type="String" />
        <asp:Parameter Name="Vendor" Type="String" />
        <asp:Parameter Name="Cost" Type="Decimal" />
        <asp:Parameter Name="Active" Type="Boolean" DefaultValue="True" />
    </InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDSVendors" runat="server" ConnectionString="<%$ ConnectionStrings:Sitefinity %>"
    SelectCommand="SELECT [ID], [VendorName] FROM [Vendor]"></asp:SqlDataSource>

And 

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Web.UI;
using Telerik.Web.UI;
using Telerik.Web.UI.Scheduler.Views;


public partial class DragDropFoodScheduling : UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void RadScheduler1_AppointmentInsert(object sender, SchedulerCancelEventArgs e)
    {
        //e.Appointment.Attributes["sort_code"] = "";
        //e.Appointment.Attributes["vendor"] = "";
    }

    protected void RadGrid1_RowDrop(object sender, GridDragDropEventArgs e)
    {
        GridDataItem dataItem = e.DraggedItems[0];

        Hashtable values = new Hashtable();
        dataItem.ExtractValues(values);

        //int id = (int)dataItem.GetDataKeyValue("ID");
        string itemname = (string)values["ItemName"];
        string vendor = (string)values["Vendor"];
        string sortcode = (string)values["SortCode"];
        decimal cost = (decimal)values["Cost"];
        string targetSlotIndex = TargetSlotHiddenField.Value;

        if (targetSlotIndex != string.Empty)
        {
            HandleSchedulerDrop(itemname, vendor, sortcode, cost, targetSlotIndex);
            TargetSlotHiddenField.Value = string.Empty;
        }
        RadScheduler1.Rebind();
        RadGrid1.Rebind();
        RadAjaxManager1.AjaxSettings.AddAjaxSetting(RadGrid1, RadScheduler1);
    }

    private void HandleSchedulerDrop(string itemname, string vendor, string sortcode, decimal cost, string targetSlotIndex)
    {
        RadScheduler1.Rebind();

        ISchedulerTimeSlot slot = RadScheduler1.GetTimeSlotFromIndex(targetSlotIndex);

        TimeSpan duration = TimeSpan.FromHours(1);
        if (slot.Duration == TimeSpan.FromDays(1))
        {
            duration = slot.Duration;
        }

        ScheduleAppointment(itemname, vendor, sortcode, cost, slot.Start, slot.Start.Add(duration));
    }


    protected void RadScheduler1_AppointmentCommand(object sender, AppointmentCommandEventArgs e)
    {
        if (e.CommandName == "Unschedule")
        {
            int id = (int)e.Container.Appointment.ID;

            string sortcode = "";

            if (!string.IsNullOrEmpty(e.Container.Appointment.Attributes["sort_code"]))
                sortcode = e.Container.Appointment.Attributes["sort_code"];

            string vendor = "";

            if (!string.IsNullOrEmpty(e.Container.Appointment.Attributes["vendor"]))
                vendor = e.Container.Appointment.Attributes["vendor"];

            UnscheduleAppointment(id, sortcode, vendor);
            RadScheduler1.Rebind();
            RadGrid1.Rebind();

            RadAjaxManager1.AjaxSettings.AddAjaxSetting(RadScheduler1, RadGrid1);
        }
    }

    private void UnscheduleAppointment(int id, string sortCode, string vendor)
    {
        IDataSource dataSource = GridDataSource;
        DataSourceView view = dataSource.GetView("DefaultView");

        IOrderedDictionary data = new OrderedDictionary();
        data.Add("ItemDate", null);
        data.Add("End", null);
        data.Add("Due", sortCode);
        data.Add("Priority", vendor);

        IDictionary keys = new OrderedDictionary();
        keys.Add("AppointmentID", id);

        view.Update(keys, data, new OrderedDictionary(), OnDataSourceOperationComplete);
    }

    private void ScheduleAppointment(string itemname, string vendor, string sortcode, decimal cost, DateTime start, DateTime end)
    {
        IDataSource dataSource = SchedulerDataSource;
        DataSourceView view = dataSource.GetView("DefaultView");

        IOrderedDictionary data = new OrderedDictionary();
        data.Add("ItemName", itemname);
        data.Add("Start", start);
        data.Add("End", end);
        data.Add("Vendor", vendor);
        data.Add("SortCode", sortcode);
        data.Add("Cost", cost);

        //IDictionary keys = new OrderedDictionary();
        //keys.Add("AppointmentID", id);

        view.Insert(data, OnDataSourceOperationComplete);
    }

    private static bool OnDataSourceOperationComplete(int count, Exception e)
    {
        if (e != null)
        {
            throw e;
        }
        return true;
    }

    protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)
    {
        if (e.Item is GridFooterItem && RadGrid1.MasterTableView.IsItemInserted)
        {
            e.Item.Visible = false;
        }
    }

    protected void RadGrid1_ItemCommand(object source, GridCommandEventArgs e)
    {
        if (e.CommandName == RadGrid.InitInsertCommandName)
        {
            e.Canceled = true;
            //Prepare an IDictionary with the predefined values
            ListDictionary newValues = new ListDictionary();
            newValues["Vendor"] = "";
            newValues["SortCode"] = "";
            newValues["ItemName"] = "Enter the Food Description Here";
            //Insert the item and rebind
            e.Item.OwnerTableView.InsertItem(newValues);
        }
    }
}

[Shawn Davis]

I also notice that when I double-click on the radgrid at the bottom of the page and then cancel, all the java script stops working I can no longer drag & drop or double-click the radgrid.

[Veronica]

Hi Shawn Davis,

As this concerns Sitefinity I am taking this thread. Unfortunately I'll need some more time to look at it. I'll contact you as soon as possible.

Thank you for your patience.

Kind regards,
Veronica Milcheva
the Telerik team

[Veronica]

Hello Shawn Davis,

I tried to reproduce the issue but to no avail.

Could you please send me your Sitefinity project as well as the database so I can inspect it and help you?

Thank you! 

All the best,
Veronica Milcheva
the Telerik team