2 Listboxes - Control which Columns Inserted

[Heath Brown]

Hello,

I have two Listboxes on my page which are bound to two different SQLDataSources.  The two tables are close to the same, but not exactly.  Here is what they consist of:

Table1 (Named as Topics):
Topic_ID (key)
TopicName (string)
Category_ID

Table2 (Named as TopicAssignment):
TopicAssignment_ID (key)
Topic_ID (Int)
Role_ID
SortOrder (Int)

Also on the page, above each Listbox are two RadComboBoxes.  One of them is bound to a SQLDataSource with a list of Categories (and ListBox1 filters by the Category_ID of RadCombobox1 to show only topics of the selected category of Combobox1), the other RadCombo is bound to a SQLDataSource of Roles (which is what Listbox2 uses to filter by Role_ID to only show Topics Assigned to the currently selected Role_ID of RadCombo2).  I have everything ajaxified via the Radajaxproxy and Manager.  Everything is working, including the reorder, EXCEPT:

The transfer from Table1 to Table2 inserts null data into the Topic_ID and Role_ID columns of the TopicAssignment table.  The SortOrder is assigned a value of 0 (which I guess is fine since they can reorder it after the transfer).  How can I control which data is inserted into TopicAssignments (Listbox2) from Topics (Listbox1)?  I want to bring over the Topic_ID (which is in the datasource) and the Role_ID (which I can grab from RadComboBox2.SelectedValue when I transfer from table1 to table2.  I'm not moving from one to the other or back, and I have transfer mode of table1 set to copy not move.  You can only reorder and delete on table2.  You can only transfer on table1 (as the settings I have chosen so far).  Here is my source so far:

<%@ Page Title="" Language="C#" MasterPageFile="~/Mentor.Master" AutoEventWireup="true" CodeBehind="Test.aspx.cs" Inherits="Mentor.Test" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
    <style type="text/css">
        .style4
        {
            border-bottom: 1px solid #d0d7e5;
            font-weight: bold;
            color: #274c89;
            background-color: #dde8fe;
            width: 113px;
        }
        .style5
        {
            border-bottom: 1px solid #d0d7e5;
            font-weight: bold;
            color: #274c89;
            background-color: #dde8fe;
            width: 404px;
        }
        .style6
        {
            border-bottom: 1px solid #d0d7e5;
            font-weight: bold;
            color: #274c89;
            background-color: #dde8fe;
            width: 82px;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <telerik:RadAjaxManagerProxy ID="RadAjaxManagerProxy1" runat="server">
        <AjaxSettings>
            <telerik:AjaxSetting AjaxControlID="RadComboBox1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadComboBox1"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                    <telerik:AjaxUpdatedControl ControlID="RadListBox1"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
            <telerik:AjaxSetting AjaxControlID="RadComboBox2">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadComboBox2"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                    <telerik:AjaxUpdatedControl ControlID="RadListBox1"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                    <telerik:AjaxUpdatedControl ControlID="RadListBox2"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
            <telerik:AjaxSetting AjaxControlID="RadListBox1">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadListBox1"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                    <telerik:AjaxUpdatedControl ControlID="RadListBox2"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
            <telerik:AjaxSetting AjaxControlID="RadListBox2">
                <UpdatedControls>
                    <telerik:AjaxUpdatedControl ControlID="RadListBox1"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                    <telerik:AjaxUpdatedControl ControlID="RadListBox2"
                        LoadingPanelID="RadAjaxLoadingPanel1" />
                </UpdatedControls>
            </telerik:AjaxSetting>
        </AjaxSettings>
    </telerik:RadAjaxManagerProxy>
    <asp:SqlDataSource ID="dsCategory" runat="server"
        ConnectionString="<%$ ConnectionStrings:MentorDBConnectionString %>"
        SelectCommand="SELECT [Category_ID], [CategoryName], [Client_ID] FROM [Categories] WHERE ([Client_ID] = @Client_ID) ORDER BY [CategoryName]">
        <SelectParameters>
            <asp:Parameter DefaultValue="1" Name="Client_ID" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>
        <asp:SqlDataSource ID="dsRole" runat="server"
        ConnectionString="<%$ ConnectionStrings:MentorDBConnectionString %>"
        SelectCommand="SELECT [Role_ID], [Client_ID], [RoleName] FROM [Roles] WHERE ([Client_ID] = @Client_ID) ORDER BY [RoleName]">
            <SelectParameters>
                <asp:Parameter DefaultValue="1" Name="Client_ID" Type="Int32" />
            </SelectParameters>
    </asp:SqlDataSource>
        <asp:SqlDataSource ID="dsTopics" runat="server"
        ConnectionString="<%$ ConnectionStrings:MentorDBConnectionString %>"
        InsertCommand="INSERT INTO [TopicAssignment] ([Topic_ID], [Role_ID], [SortOrder]) VALUES (@Topic_ID, @Role_ID, @SortOrder)"
        SelectCommand="SELECT Topic_ID, TopicName, Category_ID, SortOrder FROM Topics WHERE (Category_ID = @Category_ID) AND (NOT EXISTS (SELECT Topic_ID, Role_ID FROM TopicAssignment WHERE (Topic_ID = Topics.Topic_ID) AND (Role_ID = @Role_ID))) ORDER BY TopicName">
            <SelectParameters>
                <asp:ControlParameter ControlID="RadComboBox1" DefaultValue="1"
                    Name="Category_ID" PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="RadComboBox2" DefaultValue="1" Name="Role_ID"
                    PropertyName="SelectedValue" />
            </SelectParameters>
            <InsertParameters>
                <asp:Parameter Name="Topic_ID" />
                <asp:Parameter Name="Role_ID" />
                <asp:Parameter Name="SortOrder" />
            </InsertParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="rsAssignedTopics" runat="server"
        ConnectionString="<%$ ConnectionStrings:MentorDBConnectionString %>"
        DeleteCommand="DELETE FROM [TopicAssignment] WHERE [TopicAssign_ID] = @TopicAssign_ID"
        InsertCommand="INSERT INTO [TopicAssignment] ([Topic_ID], [Role_ID], [SortOrder]) VALUES (@Topic_ID, @Role_ID, @SortOrder)"
        SelectCommand="SELECT [TopicAssign_ID], [Topic_ID], [Role_ID], [SortOrder] FROM [TopicAssignment] WHERE ([Role_ID] = @Role_ID) ORDER BY SortOrder"
        UpdateCommand="UPDATE [TopicAssignment] SET [Topic_ID] = @Topic_ID, [Role_ID] = @Role_ID, [SortOrder] = @SortOrder WHERE [TopicAssign_ID] = @TopicAssign_ID">
        <SelectParameters>
            <asp:ControlParameter ControlID="RadComboBox2" DefaultValue="1" Name="Role_ID"
                PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>
        <DeleteParameters>
            <asp:Parameter Name="TopicAssign_ID" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="Topic_ID" Type="Int32" />
            <asp:Parameter Name="Role_ID" Type="Int32" />
            <asp:Parameter Name="SortOrder" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="Topic_ID" Type="Int32" />
            <asp:Parameter Name="Role_ID" Type="Int32" />
            <asp:Parameter Name="SortOrder" Type="Int32" />
        </InsertParameters>
    </asp:SqlDataSource>
        <table width="100%" border="0" cellpadding="0" cellspacing="0" class="panelBorders">
        <tr>
            <td height="24" class="headerCell">
                <table border="0" cellspacing="2"
                        cellpadding="2" style="width: 100%">
                    <tr>
                        <td width="2%" align="center">
                            <img src="images/assignpositions.png" width="16"
                            height="16" /></td>
                        <td width="100%" class="headerText">
                            Build Curriculum</td>
                    </tr>
                </table>
            </td>
        </tr>
        <tr>
            <td class="panelContent" valign="top">
                <table border="0" cellspacing="3"
                        cellpadding="3" style="width: 100%">
                    <tr>
                        <td class="style5">
                            Instructions go here</td>
                    </tr>
                </table>
            </td>
        </tr>
    </table>
    <table width="100%" border="0" cellspacing="0" cellpadding="0">
                <tr>
                  <td><img src="images/shim.gif" width="100"
                          height="6" /></td>
                </tr>
            </table>
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td width="485" valign="top">
    <table width="100%" border="0" cellpadding="0" cellspacing="0" class="panelBorders">
        <tr>
            <td height="24" class="headerCell">
                <table border="0" cellspacing="2"
                        cellpadding="2" style="width: 100%">
                    <tr>
                        <td width="2%" align="center">
                            <img src="images/assignpositions.png" width="16"
                            height="16" /></td>
                        <td width="100%" class="headerText">
                            Available Topics</td>
                    </tr>
                </table>
            </td>
        </tr>
        <tr>
            <td class="panelContent" valign="top">
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td height="28" valign="middle" class="style4" align="center">Select Category:</td>
          <td valign="middle" class="comborowBackground">
          <telerik:RadComboBox ID="RadComboBox1" Runat="server" AutoPostBack="True"
                                DataSourceID="dsCategory" DataTextField="CategoryName"
                                DataValueField="Category_ID" EmptyMessage="Select Category"
                  Skin="Office2007" Width="100%">
                            </telerik:RadComboBox>
          </td>
        </tr>
    </table>
    <table width="100%" border="0" cellspacing="2" cellpadding="2">
        <tr>
          <td height="23" valign="middle">
              <telerik:RadListBox ID="RadListBox1" runat="server" AllowTransfer="True"
                  DataKeyField="Topic_ID" DataSourceID="dsTopics" DataTextField="TopicName"
                  DataValueField="Topic_ID" Height="500px" Skin="Office2007"
                  style="top: 0px; left: 0px" TransferToID="RadListBox2" Width="100%"
                  AllowAutomaticUpdates="True" AutoPostBackOnTransfer="True" TransferMode="Copy">
              </telerik:RadListBox>
            </td>
        </tr>
    </table>
            </td>
        </tr>
    </table>
          </td>
        <td width="6">&nbsp;</td>
        <td width="485" valign="top">
    <table width="100%" border="0" cellpadding="0" cellspacing="0" class="panelBorders">
        <tr>
            <td height="24" class="headerCell">
                <table border="0" cellspacing="2"
                        cellpadding="2" style="width: 100%">
                    <tr>
                        <td width="2%" align="center">
                            <img src="images/assignpositions.png" width="16"
                            height="16" /></td>
                        <td width="100%" class="headerText">
                            Topics For</td>
                    </tr>
                </table>
            </td>
        </tr>
        <tr>
            <td class="panelContent" valign="top">
            <table width="100%" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td height="28" valign="middle" class="style6" align="center">Select Role:</td>
          <td height="28" valign="middle" class="comborowBackground">
          <telerik:RadComboBox ID="RadComboBox2" Runat="server" DataSourceID="dsRole"
                                DataTextField="RoleName" DataValueField="Role_ID" EmptyMessage="Select Role"
                                Skin="Office2007" AutoPostBack="True" Width="100%">
                            </telerik:RadComboBox>
          </td>
        </tr>
    </table>
            <table width="100%" border="0" cellspacing="2" cellpadding="2">
        <tr>
          <td height="23" valign="middle">
              <telerik:RadListBox ID="RadListBox2" runat="server"
                  AllowAutomaticUpdates="True" AllowDelete="True" AllowReorder="True"
                  AutoPostBackOnDelete="True" AutoPostBackOnReorder="True"
                  DataKeyField="TopicAssign_ID" DataSortField="SortOrder"
                  DataSourceID="rsAssignedTopics" DataTextField="Topic_ID"
                  DataValueField="TopicAssign_ID" Height="500px" Skin="Office2007"
                  style="top: 0px; left: 0px" TransferToID="RadListBox1" Width="100%" TransferMode="Copy">
              </telerik:RadListBox>
            </td>
        </tr>
    </table>
            </td>
        </tr>
    </table>
          </td>
      </tr>
    </table>
       <table width="100%" border="0" cellspacing="0" cellpadding="0">
                <tr>
                  <td><img src="images/shim.gif" width="100"
                          height="6" /></td>
                </tr>
            </table>
</asp:Content>

Any help is appreciated!  One last thing to mention.  The select statement on table1 checks to see if the topic is already in table2 and won't display the topic in the table1 listbox if it exists already in table2 (preventing duplicates from being selected).

[Heath Brown]

Anyone?  I think I'm just missing something easy.  I guess I'll submit a ticket instead.

[Simon]

Hi Heath Brown,

Thank you for providing your code.

So configured the second ListBox's cannot supply the Role_ID and Topic_ID parameters to the Insert/Update commands therefore the inserted/updated values are nulls.

You could supply the Role_ID parameter by changing its type in the DataSource from Parameter to ControlParameter and configure it to take its value from the SelectedValue property of the Roles ComboBox.

In the same way you could configure the Topic_ID parameter to get the SelectedValue property of the first ListBox (this approach will only work in Single Selection mode).

Greetings,
Simon
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Heath Brown]

You are a genius.  I was so close to this, in fact I did something similar on a 2 grid page.  This works much better though!  Thank you so much for your help.

Regards,
Heath