RadGrid AutoDelete with Custom SqlDataSource DeleteCommand

[Mike]

Hi all,

My scenario is as follows. Instead of deleting a record, I would like to Update two fields. The first is "UpdatedBy", which holds a value representing the deleting user. The second is "Deleted", the flag I use to ascertain whether a record has been removed from the system. I would like to do this with a custom DeleteCommand in my SqlDataSource. I have it successfully working in 1 RadGrid, but in another I am getting errors that I need to declare my @Scalar variables. All parameters I use I have declared, but for some reason it just wont recognize them.

This is my working RadGrid:

01.<asp:SqlDataSource ID="StaffSitesDataSource" runat="server" ConnectionString='<%$ ConnectionStrings:MainConnection %>' DeleteCommand=" UPDATE [SitesStaff] SET [UpdatedBy] = @UpdatedBy WHERE [SitesStaffId] = @SitesStaffId; DELETE FROM [SitesStaff] WHERE [SitesStaffId] = @SitesStaffId" InsertCommand="INSERT INTO [SitesStaff] ([SiteId], [StaffId], [UpdatedBy]) VALUES (@SiteId, @StaffId, @UpdatedBy)" SelectCommand="SELECT t0.SitesStaffId, t1.SiteName FROM [SitesStaff] t0 INNER JOIN Sites t1 ON t0.SiteId = t1.SiteId WHERE ([StaffId] = @StaffId)">
02.                            <SelectParameters>
03.                                <asp:ControlParameter ControlID="hdnFldSelectedStaffId" PropertyName="Value" Name="StaffId" Type="Int32"></asp:ControlParameter>
04.                            </SelectParameters>
05.                            <DeleteParameters>
06.                                <asp:Parameter Name="SitesStaffId" Type="Int32"></asp:Parameter>
07.                                <asp:Parameter Name="UpdatedBy" Type="String"></asp:Parameter>
08.                            </DeleteParameters>
09.                            <InsertParameters>
10.                                <asp:Parameter Name="SiteId" Type="Int32"></asp:Parameter>
11.                                <asp:ControlParameter ControlID="hdnFldSelectedStaffId" PropertyName="Value" Name="StaffId" Type="Int32"></asp:ControlParameter>
12.                                <asp:Parameter Name="UpdatedBy" Type="String"></asp:Parameter>
13.                            </InsertParameters>
14.                        </asp:SqlDataSource>
15.
16.                        <telerik:RadGrid ID="rgStaffSites" runat="server" AllowSorting="True" Skin="Windows7" Font-Size="9pt" AutoGenerateDeleteColumn="true" DataSourceID="StaffSitesDataSource" AllowMultiRowSelection="true" OnInsertCommand="rgStaffSites_InsertCommand">
17.                            <MasterTableView AutoGenerateColumns="False" DataSourceID="StaffSitesDataSource" DataKeyNames="SitesStaffId" AllowAutomaticDeletes="true" AllowAutomaticUpdates="false" AllowAutomaticInserts="false" EditMode="PopUp" CommandItemDisplay="Top">
18.                                <CommandItemTemplate>
19.                                    <telerik:RadToolBar RenderMode="Lightweight" ID="rtbRgStaffSites" runat="server" Skin="Windows7" AutoPostBack="true">
20.                                        <Items>
21.                                            <telerik:RadToolBarButton Text="Add new" CommandName="InitInsert" ImageUrl="~/Images/AddRecord.gif" Visible='<%# Not rgStaffSites.MasterTableView.IsItemInserted %>'></telerik:RadToolBarButton>
22.                                            <telerik:RadToolBarButton Text="Delete selected Sites" CommandName="DeleteSelected" ImageUrl="~/Images/Delete.gif"></telerik:RadToolBarButton>
23.                                            <telerik:RadToolBarButton Text="Refresh Sites" CommandName="RebindGrid" ImageUrl="~/Images/Refresh.gif"></telerik:RadToolBarButton>
24.                                            <telerik:RadToolBarButton Value="UserName" OuterCssClass="rightButton">
25.                                                <ItemTemplate>
26.                                                    <asp:Label ID="lblSelectedUserName2" runat="server"></asp:Label>
27.                                                </ItemTemplate>
28.                                            </telerik:RadToolBarButton>
29.                                        </Items>
30.                                    </telerik:RadToolBar>
31.                                </CommandItemTemplate>
32.                                <EditFormSettings InsertCaption="Add Site">
33.                                    <PopUpSettings CloseButtonToolTip="Cancel Add" Modal="false" OverflowPosition="Center" />
34.                                    <EditColumn ButtonType="ImageButton"></EditColumn>
35.                                </EditFormSettings>
36.                                <Columns>
37.                                    <telerik:GridClientSelectColumn UniqueName="SelectColumn" ItemStyle-Width="25px" HeaderStyle-Width="25px"></telerik:GridClientSelectColumn>
38.                                    <telerik:GridBoundColumn DataField="SitesStaffId" ReadOnly="True" Display="false" Visible="true" HeaderText="SitesStaffId" SortExpression="SitesStaffId" UniqueName="SitesStaffId" DataType="System.Int32" FilterControlAltText="Filter SitesStaffId column"></telerik:GridBoundColumn>
39.                                    <telerik:GridTemplateColumn HeaderText="Site" UniqueName="SiteName" DataField="SiteName" FilterControlAltText="Filter SiteName column" ItemStyle-HorizontalAlign="Left">
40.                                        <ItemTemplate>
41.                                            <%# Eval("SiteName") %>
42.                                        </ItemTemplate>
43.                                        <EditItemTemplate>
44.                                            <telerik:RadComboBox ID="rgStaffSites_rcbSites" runat="server" DataSourceID="SitesDataSource" DataTextField="SiteName" DataValueField="SiteId" Skin="Windows7" CheckBoxes="true"></telerik:RadComboBox>
45.                                        </EditItemTemplate>
46.                                    </telerik:GridTemplateColumn>
47.                                </Columns>
48.                            </MasterTableView>
49.                            <ClientSettings>
50.                                <ClientEvents OnPopUpShowing="PopUpShowing" />
51.                                <Selecting AllowRowSelect="true" EnableDragToSelectRows="true" />
52.                            </ClientSettings>
53.                        </telerik:RadGrid>

 

This is the RadGrid I cant get to work:

01.<asp:SqlDataSource ID="TasksCollaboratorsDataSource" runat="server" ConnectionString='<%$ ConnectionStrings:MainConnection %>' DeleteCommandType="Text" DeleteCommand="UPDATE [TasksSharing] SET [UpdatedBy] = @UpdatedBy, [Deleted] = @Deleted WHERE [ShareId] = @ShareId" InsertCommand="INSERT INTO [TasksSharing] ([TaskId], [TimeShared], [SharedBy], [SharedWith], [Implementing], [Accountable], [AccessLevel], [Deleted], [UpdatedBy]) VALUES (@TaskId, @TimeShared, @SharedBy, @SharedWith, @Implementing, @Accountable, @AccessLevel, @Deleted, @UpdatedBy)" SelectCommand="SELECT * FROM [TasksSharing] WHERE ([TaskId] = @TaskId AND [Deleted] = @Deleted)" UpdateCommand="UPDATE [TasksSharing] SET [UpdatedBy] = @UpdatedBy, [Implementing] = @Implementing, [Accountable] = @Accountable, [AccessLevel] = @AccessLevel WHERE [ShareId] = @ShareId">
02.                                  <DeleteParameters>
03.                                      <asp:Parameter Name="UpdatedBy" Type="String"></asp:Parameter>
04.                                       <asp:Parameter Name="Deleted" Type="Boolean" DefaultValue="True"></asp:Parameter>
05.                                      <asp:Parameter Name="ShareId" Type="Int32"></asp:Parameter>
06.                                  </DeleteParameters>
07.                                  <InsertParameters>
08.                                      <asp:Parameter Name="TaskId" Type="Int32"></asp:Parameter>
09.                                      <asp:Parameter Name="TimeShared" Type="DateTime"></asp:Parameter>
10.                                      <asp:Parameter Name="SharedBy" Type="String"></asp:Parameter>
11.                                      <asp:Parameter Name="SharedWith" Type="String"></asp:Parameter>
12.                                      <asp:Parameter Name="Implementing" Type="Boolean"></asp:Parameter>
13.                                      <asp:Parameter Name="Accountable" Type="Boolean"></asp:Parameter>
14.                                      <asp:Parameter Name="AccessLevel" Type="String"></asp:Parameter>
15.                                      <asp:Parameter Name="UpdatedBy" Type="String"></asp:Parameter>
16.                                      <asp:Parameter Name="Deleted" Type="Boolean" DefaultValue="True"></asp:Parameter>
17.                                  </InsertParameters>
18.                                  <SelectParameters>
19.                                      <asp:ControlParameter Name="TaskID" ControlID="hdnFldSelectedTaskID" Type="Int32" DefaultValue="0" ConvertEmptyStringToNull="true"></asp:ControlParameter>
20.                                      <asp:Parameter Name="Deleted" Type="Boolean" DefaultValue="False"></asp:Parameter>
21.                                  </SelectParameters>
22.                                  <UpdateParameters>
23.                                      <asp:Parameter Name="Implementing" Type="Boolean"></asp:Parameter>
24.                                      <asp:Parameter Name="Accountable" Type="Boolean"></asp:Parameter>
25.                                      <asp:Parameter Name="AccessLevel" Type="String"></asp:Parameter>
26.                                      <asp:Parameter Name="ShareId" Type="Int32"></asp:Parameter>
27.                                      <asp:Parameter Name="UpdatedBy" Type="String"></asp:Parameter>
28.                                  </UpdateParameters>
29.                              </asp:SqlDataSource>
30.
31.
32.                              <telerik:RadGrid ID="rgTasksCollaborators" runat="server" DataSourceID="TasksCollaboratorsDataSource" AllowSorting="True" AllowFilteringByColumn="True" Skin="Windows7"
33.                                  AllowAutomaticDeletes="true" AllowAutomaticInserts="false" AllowAutomaticUpdates="false" AllowMultiRowSelection="true" AutoGenerateDeleteColumn="true" OnItemCommand="rgCollaborators_ItemCommand">
34.                                  <ClientSettings>
35.                                      <Selecting AllowRowSelect="True" EnableDragToSelectRows="true"></Selecting>
36.                                  </ClientSettings>
37.                                  <MasterTableView DataSourceID="TasksCollaboratorsDataSource" AutoGenerateColumns="False" DataKeyNames="ShareId" CommandItemDisplay="Top">
38.                                      <CommandItemTemplate>
39.                                          <telerik:RadToolBar ID="rtbTasksCollaborators" RenderMode="Lightweight" runat="server" Skin="Windows7" AutoPostBack="true">
40.                                              <Items>
41.                                                  <telerik:RadToolBarButton Text="Add Collaborator" CommandName="InitInsert" ImageUrl="~/Images/AddRecord.gif"></telerik:RadToolBarButton>
42.                                                  <telerik:RadToolBarButton Text="Remove Selected Collaborators" CommandName="DeleteSelected" ImageUrl="~/Images/Delete.gif"></telerik:RadToolBarButton>
43.                                                  <telerik:RadToolBarButton Text="Refresh Colaborators" CommandName="RebindGrid" ImageUrl="~/Images/Refresh.gif" OuterCssClass="rightButton"></telerik:RadToolBarButton>
44.                                              </Items>
45.                                          </telerik:RadToolBar>
46.                                      </CommandItemTemplate>
47.                                      <ItemStyle CssClass="defaultRadGridItem" />
48.                                      <AlternatingItemStyle CssClass="defaultRadGridItem" />
49.                                      <EditFormSettings>
50.                                          <EditColumn ButtonType="ImageButton"></EditColumn>
51.                                          <PopUpSettings Width="600px" CloseButtonToolTip="Cancel" />
52.                                          <FormTemplate>
53.                                          </FormTemplate>
54.                                      </EditFormSettings>
55.                                      <Columns>
56.                                          <telerik:GridClientSelectColumn UniqueName="selectColumn"></telerik:GridClientSelectColumn>
57.                                          <telerik:GridEditCommandColumn UniqueName="editColumn"></telerik:GridEditCommandColumn>
58.                                          <telerik:GridBoundColumn DataField="ShareId" ReadOnly="True" Display="false" HeaderText="ShareId" SortExpression="ShareId" UniqueName="ShareId" DataType="System.Int32" FilterControlAltText="Filter ShareId column"></telerik:GridBoundColumn>
59.                                          <telerik:GridBoundColumn DataField="TaskId" Display="false" HeaderText="TaskId" SortExpression="TaskId" UniqueName="TaskId" DataType="System.Int32" FilterControlAltText="Filter TaskId column"></telerik:GridBoundColumn>
60.                                          <telerik:GridDateTimeColumn DataField="TimeShared" HeaderText="Time Shared" SortExpression="TimeShared" UniqueName="TimeShared" DataType="System.DateTime" FilterControlAltText="Filter TimeShared column"></telerik:GridDateTimeColumn>
61.                                          <telerik:GridBoundColumn DataField="SharedBy" HeaderText="Shared By" SortExpression="SharedBy" UniqueName="SharedBy" FilterControlAltText="Filter SharedBy column"></telerik:GridBoundColumn>
62.                                          <telerik:GridBoundColumn DataField="SharedWith" HeaderText="Shared With" SortExpression="SharedWith" UniqueName="SharedWith" FilterControlAltText="Filter SharedWith column"></telerik:GridBoundColumn>
63.                                          <telerik:GridCheckBoxColumn DataField="Implementing" HeaderText="Implementing" SortExpression="Implementing" UniqueName="Implementing" DataType="System.Boolean" FilterControlAltText="Filter Implementing column"></telerik:GridCheckBoxColumn>
64.                                          <telerik:GridCheckBoxColumn DataField="Accountable" HeaderText="Accountable" SortExpression="Accountable" UniqueName="Accountable" DataType="System.Boolean" FilterControlAltText="Filter Accountable column"></telerik:GridCheckBoxColumn>
65.                                          <telerik:GridDropDownColumn DataField="AccessLevel" HeaderText="Access Level" SortExpression="AccessLevel" UniqueName="AccessLevel" FilterControlAltText="Filter AccessLevel column" DataSourceID="AccessLevelsDatasource" ListTextField="ItemValue" ListValueField="ItemId"></telerik:GridDropDownColumn>
66.                                          <telerik:GridCheckBoxColumn DataField="Deleted" Display="false" HeaderText="Deleted" SortExpression="Deleted" UniqueName="Deleted" DataType="System.Boolean" FilterControlAltText="Filter Deleted column"></telerik:GridCheckBoxColumn>
67.                                          <telerik:GridBoundColumn DataField="UpdatedBy" Display="false" HeaderText="UpdatedBy" SortExpression="UpdatedBy" UniqueName="UpdatedBy" FilterControlAltText="Filter UpdatedBy column"></telerik:GridBoundColumn>
68.                                      </Columns>
69.                                  </MasterTableView>
70.                              </telerik:RadGrid>

 

I feel like it is something obvious that I am not seeing. Any help would be greatly appreciated.

Thanks,

Mike

[Eyup]

Hello Meir,

In such complex scenarios you can remove the AllowAutomaticDeletes property and use the DeleteCommand or ItemCommand event handler to make the query manually:
http://demos.telerik.com/aspnet-ajax/grid/examples/data-editing/manual-crud-operations/defaultcs.aspx

I hope this will prove helpful.

Regards,
Eyup
Telerik by Progress

[Mike]

Hi,

Thanks  for the reply. I am aware of and have already implemented the ItemCommand as a workaround. I use the ItemCommand quite heavily in my project. I just like to have all my databinding in one location (in this case SqlDataSource) when possible. I have my SqlDataSource solution working fine in many places, except this one. I guess I was looking for some insight into how the AllowAutomaticDeletes operates with the SqlDataSource especially when parameters are involved to help me debug my scenario.

 

Anyway, thanks for the help. 

Mike

[Eyup]

Hi Mike,

Yeah, RadGrid supports basic DataSource pre-defined commands internally, but custom and complicated queries can be performed manually:
http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/data-editing/automatic-datasource-operations

Regards,
Eyup
Telerik by Progress