Help with Database updates

[Erik Hinds]

I have two tables. Disciplines and DisciplineReveiwers

I assign users (reviewers) to a discipline. The source listbox lists the disciplines and the destination listbox lists the DisciplineReviewers. Populating the boxes and transferring items works well. THe problem is that it does not update the database when doing so. Can some one take a look and tell me what I'm doing wrong? I don't want to update the Disciplines table, just the DisciplinesReviwer table.

 <ul class="listbox-list">
                    <li class="listbox-item">
                         <telerik:RadListBox ID="radListDisciplines" runat="server" AllowTransfer="True"
                            AutoPostBackOnDelete="True" AutoPostBackOnTransfer="True" DataSourceID="sqlListDisciplines"
                            TransferToID="radListAssigned" DataTextField="DisciplineName" DataValueField="DisciplineId" Width="300px" DataKeyField="DisciplineId">
                         </telerik:RadListBox>
                    </li>
                    <li class="listbox-item">
                        <telerik:RadListBox ID="radListAssigned" runat="server"
                            DataSourceID="sqlReviewersDisciplines"
                             DataTextField="DisciplineName" DataValueField="DisciplineId" AllowDelete="True"
                             AutoPostBack="True" AutoPostBackOnDelete="True" AutoPostBackOnTransfer="True"
                             DataKeyField="DisciplineReviewerId" TransferToID="radListDisciplines" Width="300px">
                        </telerik:RadListBox>
                    </li>
                 </ul>
<div style="clear:both;"></div>

                <asp:SqlDataSource ID="sqlListDisciplines" runat="server" ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>"
                    SelectCommand="Dej_DisciplineList" SelectCommandType="StoredProcedure">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="hidOrgId" Name="OrgId" PropertyName="Value" Type="Int32" />
                    </SelectParameters>
                </asp:SqlDataSource>

                <asp:SqlDataSource ID="sqlReviewersDisciplines" runat="server" ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>"
                    SelectCommand="Dej_ListDisciplineReviewerByReviewer"
                    SelectCommandType="StoredProcedure"
                    DeleteCommand="Dej_DisciplineReviewerDelete"
                    DeleteCommandType="StoredProcedure"
                    InsertCommand="Dej_DisciplineReviewerAdd"
                    InsertCommandType="StoredProcedure">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="hidReviewerId" Name="UserId" PropertyName="Value"
                            Type="Int32" />
                    </SelectParameters>
                    <DeleteParameters>
                        <asp:Parameter Name="DisciplineReviewerId" Type="Int32" />
                    </DeleteParameters>
                    <InsertParameters>
                        <asp:Parameter Name="DisciplineId" Type="Int32" />
                        <asp:Parameter Name="ReviewerId" Type="Int32" />
                    </InsertParameters>
                </asp:SqlDataSource>

[Genady Sergeev]

Hi Erik Hinds,

You have forgotten to set the AllowAutomaticUpdate property to true on the first RadListBox. After setting it, the database update should work as expected. More information on how to use the auto update functionality can be found here.

Sincerely yours,
Genady Sergeev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Erik Hinds]

Ok, I tried that but I get an object reference error

DotNetNuke.Services.Exceptions.PageLoadException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. at Telerik.Web.UI.ControlItem.get_Index() at Telerik.Web.UI.RadListBoxItem.get_DataKey() at Telerik.Web.UI.RadListBoxItem.FindDataItem(IEnumerable data) at Telerik.Web.UI.RadListBoxItem.ExtractValues() at Telerik.Web.UI.RadListBox.PerformInsert(IList`1 items, Int32 position) at Telerik.Web.UI.RadListBox.PerformTransfer(RadListBox sourceListBox, RadListBox destinationListBox, IList`1 items) at Telerik.Web.UI.RadListBox.Transfer(IList`1 itemsToTransfer, RadListBox sourceListBox, RadListBox destinationListBox) at Telerik.Web.UI.RadListBox.OnTransfer(ListBoxPostBackCommand command, Func`2 callback) at Telerik.Web.UI.RadListBox.RaisePostBackEvent(String eventArgument) at Telerik.Web.UI.RadListBox.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace ---

I have the datakey assigned, although it is different from the source listbox.

[Erik Hinds]

Sorry, after reading your post more carefully I noticed you said on the first listbox. I assumed it was the second list box.

However, when it is on the first listbox, it gives me an error
Deleting is not supported by data source 'sqlListDisciplines' unless DeleteCommand is specified.

I don't want to remove the item from the table, I just want to remove it from the first listbox.
 So in review

The first listbox is fed from a table. When the items are transferred to the second listbox, I want to insert that item in the table binding the second listbox. The item should be removed from the 1st listbox so it can only be assigned once. Transferring from the second list box should run a delete query on that table and add the item to the first listbox. After add/delete I can rebind the items in listbox 1 and loop through the items in listbox 2 and remove them from listbox one.

Honestly, I thought this would be easier to do, but I may have to go back to the old fashion way.

[Genady Sergeev]

Hi Erik Hinds,

You can hook on the Delete event of the first datasource and cancel it or set AllowDelete='false" on the first RadListBox. However, you still need the delete statements, nevermind that they are not used. In order to enable the database update of the second RLB you need to have AllowAutomaticUpdates turned on on the both RadListBoxes.

 I have prepared sample project that demonstrates the approach. You can find it as an attachment.



Greetings,
Genady Sergeev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[ManniAT]

Hi Erik,

maybe also my Code Project sample could be a solution for this situation.
http://www.telerik.com/community/code-library/aspnet-ajax/listbox/m-n-relations-with-radlistbox.aspx
I have a similar situation and favor my solution because of no postbacks for every "listbox move" and also no database inserts / deletes - except the one needed after the whole form is filled.
The negative aspect - you have to iterate through a list and submit your additions in code behind.
Thats an example of how the code for this looks:

UCOptionSelector osX = eI.FindControl("osProdukte") as UCOptionSelector;
if (osX != null) {
    foreach (RadListBoxItem rI in osX.SelectedItems) {
        int nSelID = int.Parse(rI.Value);
        dC.Produkte2Gespraech.InsertOnSubmit(new Produkte2Gespraech() { GespraechID = nGespraechID, ProduktID = nSelID });
    }
}

Regards

Manfred

[Eugene Roeder]

I am getting a null value in my parameter PersonID when I am transferring a value from rlbUnrelatedPerson to rlbRelatedPerson.  My insert statement fails at the DB level because I don't allow null values.  Here is my web page

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true"
    CodeFile="LinkPeople.aspx.cs" Inherits="Admin_LinkPeople" %>



<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">


<script type="text/javascript">
function OnClientTransferring(sender, args)
{
    if(args.get_sourceListBox() != sender)
    {
        args.set_cancel(true);
        alert('Please remove item by deleting it by hitting the X button.');
    }
}

//window.onload = pageLoad() {
//    var $ = $telerik.$;
//    var listBox = $find("rlbUnrelatedPerson");

//    $(".rlbTransferToDisabled", listBox.get_element()).css("display", "none");
//}

</script>


    <style type="text/css">
        .style1
        {
            width: 100%;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <telerik:RadComboBox ID="RadComboBox1" runat="server" DataSourceID="SqlDataSource1"
        DataTextField="store#" DataValueField="storeID" Skin="Black"
        AutoPostBack="true" Width="225px">
    </telerik:RadComboBox>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:JJMasterDataConnectionString %>"
        SelectCommand="sp_Select_Store_Info_On_Link_People_Page" SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>
    <br />
    <br />
    <br />
    <br />
    <telerik:RadListBox
        ID="rlbUnrelatedPerson"
        runat="server" AllowTransfer="True"  AllowAutomaticUpdates="true"
        AllowDelete="false" AllowReorder="false"
        DataKeyField="PersonID"
        DataSortField="n1"
        DataTextField="n1"
        DataValueField="PersonID"
        DataSourceID="SqlDataSource2"
        TransferToID="rlbRelatedPerson" Width="230px" Height="200px" Skin="Black"
        OnClientTransferring="OnClientTransferring" Allow
        AutoPostBackOnTransfer="true"
        onselectedindexchanged="rlbUnrelatedPerson_SelectedIndexChanged" >
    </telerik:RadListBox>
    <telerik:RadListBox ID="rlbRelatedPerson" runat="server" AllowDelete="True" AutoPostBackOnDelete="true"
        DataKeyField="PersonID" DataSortField="n1" DataSourceID="SqlDataSource3" DataTextField="n1"
        DataValueField="PersonID" Skin="Black" Width="230px" Height="200px" AllowAutomaticUpdates="True"
        AutoPostBack="true" style="top: 0px; left: 0px">
    </telerik:RadListBox>
    <br />

    <telerik:RadTextBox ID="RadTextBox1" Runat="server" Width="300px">
    </telerik:RadTextBox>
    <br />

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:JJMasterDataConnectionString %>"
        SelectCommand="sp_Select_Unrelated_People_Info_On_Link_People_Page"
        SelectCommandType="StoredProcedure"
        DeleteCommand="sp_Delete_Unrelated_People_Info_On_Link_People_Page"
        DeleteCommandType="StoredProcedure"
        InsertCommand="sp_Insert_Unrelated_People_Info_On_Link_People_Page"
        InsertCommandType="StoredProcedure"
        UpdateCommand="sp_Update_Unrelated_People_Info_On_Link_People_Page"
        UpdateCommandType="StoredProcedure"
        OldValuesParameterFormatString="original_{0}">
        <SelectParameters>
        <asp:ControlParameter ControlID="RadComboBox1" DefaultValue="" Name="StoreID" PropertyName="SelectedValue"
                Type="Int64" />
        </SelectParameters>
        <DeleteParameters>
            <asp:Parameter Name="original_PersonID" Type="Int64" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="original_PersonID" Type="Int64" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="original_PersonID" Type="Int64" />
        </InsertParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:JJMasterDataConnectionString %>"
        SelectCommand="sp_Select_Related_People_Info_On_Link_People_Page"
        SelectCommandType="StoredProcedure"
        DeleteCommand="sp_Delete_Related_People_Info_On_Link_People_Page"
        DeleteCommandType="StoredProcedure"
        InsertCommand="EXEC sp_Insert_Related_People_Info_On_Link_People_Page @PersonID,@StoreID"
        UpdateCommand="sp_Update_Related_People_Info_On_Link_People_Page"
        UpdateCommandType="StoredProcedure"
        OldValuesParameterFormatString="original_{0}"
        OnDeleted="DestinationDataSource_Deleted"
        OnInserted="DestinationDataSource_Inserted" >
        <SelectParameters>
            <asp:ControlParameter ControlID="RadComboBox1" Name="StoreID" PropertyName="SelectedValue"
                Type="Int64" />
        </SelectParameters>
        <DeleteParameters>
            <%--<asp:ControlParameter ControlID="rlbRelatedPerson" Name="PersonID" PropertyName="DataValueField" Type="Int64" />--%>
            <asp:Parameter Name="original_PersonID" Type="Int64" />
            <asp:ControlParameter ControlID="RadComboBox1" Name="StoreID" PropertyName="SelectedValue"
                Type="Int64" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="PersonID" Type="Int64" />
            <asp:Parameter Name="n1" Type="String" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="PersonID" Type="Int64" />
            <asp:ControlParameter ControlID="RadComboBox1" Name="StoreID" PropertyName="SelectedValue"
                Type="Int64" />
        </InsertParameters>
    </asp:SqlDataSource>
</asp:Content>

I am following the same model as Erik Hinds first post above.  I don't want to delete from the rlbUnrelatedPerson control/table but need to add entries to the rlbRelatedPerson control/table that are also tied to the RadComboBox1 that gives the store info that a person belongs too.

All of my select statements are wired correctly and the delete on the rlbRelatedPerson works properly also it is just the null value for PersonID in the Insert statement when I transfer.

I have been using the RadTextBox1 as my debug value.

Any help please would be really appreciated.

[Genady Sergeev]

Hi Eugene Roeder,

The RadListBox database update functionality does not support scenarios where items are transferred from a table to "many to many" table. You will need to handle this scenario with custom database manipulation code.

Regards,
Genady Sergeev
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.