ListBox with many to many database relationship

2 posts, 0 answers
  1. José-Miguel
    José-Miguel avatar
    1 posts
    Member since:
    May 2014

    Posted 28 Sep Link to this post

    I am trying to use ListBox in a M:M database relationship.

    Here are my tables

    Users Table (UserID,UserName)
    Event Table (EventID, EventDescription)
    Attendees (AttendeeID, EventID, UserID)

    How I can do something to select users from the User Table that are going to attend an Event, and storage that in Attendees Table!

    Since the Users table have a different ID than the Attendees table... It not filling the proper information in the Attendees Table.

     

    here is my code:

    <telerik:RadListBox runat="server" ID="RadListBox1" DataSourceID="SourceDataSource"
           DataKeyField="UserID" DataTextField="UserName" Width="240px"
           Height="200px" AllowTransfer="True" TransferToID="RadListBox2" AutoPostBackOnTransfer="True"
           AllowTransferOnDoubleClick="True" DataValueField="UserID" TransferMode="Copy" ButtonSettings-ShowTransferAll="False">
    <ButtonSettings TransferButtons="All" ShowTransferAll="False"></ButtonSettings>
          </telerik:RadListBox>

    <asp:SqlDataSource ID="SourceDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:CalendarsConnectionString %>"
        SelectCommand="SELECT UserID, FirstName + ' ' + LastName AS UserName FROM Users ORDER BY UserName"></asp:SqlDataSource>

     

    <telerik:RadListBox runat="server" ID="RadListBox2" DataSourceID="DestinationDataSource"
           DataKeyField="AttendeeID" DataTextField="UserName" DataValueField="UserID"
           Width="240px" Height="200px"
           AllowDelete="True" AutoPostBackOnDelete="true" AllowAutomaticUpdates="True" >
          </telerik:RadListBox>

     

    <asp:SqlDataSource ID="DestinationDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:CalendarsConnectionString %>"
        SelectCommand="SELECT Attendees.AttendeeID, Attendees.CalendarID, Attendees.UserID, Users.FirstName + ' ' + Users.LastName AS UserName FROM Attendees INNER JOIN Users ON Attendees.UserID = Users.UserID WHERE (Attendees.CalendarID = @CalendarID)  ORDER BY UserName"
        DeleteCommand="DELETE FROM Attendees WHERE (AttendeeID = @AttendeeID)"
        InsertCommand="INSERT INTO Attendees( CalendarID, UserID) VALUES ( @CalendarID, @UserID)" UpdateCommand="UPDATE Attendees SET UserID = @UserID WHERE (AttendeeID = @AttendeeID)">
        <DeleteParameters>
         <asp:Parameter Name="AttendeeID" />
        </DeleteParameters>
        <InsertParameters>
         <asp:Parameter DefaultValue="4" Name="CalendarID" Type="Int32" />
         <asp:Parameter Name="UserID" Type="Int32" />
        </InsertParameters>
        <SelectParameters>
         <asp:Parameter DefaultValue="4" Name="CalendarID" />
        </SelectParameters>
        <UpdateParameters>
         <asp:Parameter Name="UserID" />
         <asp:Parameter Name="AttendeeID" />
        </UpdateParameters>
       </asp:SqlDataSource>

     

    any suggestions?

     

    Regards

     

    Jose-Miguel

  2. Peter Milchev
    Admin
    Peter Milchev avatar
    135 posts

    Posted 03 Oct Link to this post

    Hello Jose-Miguel,

    As stated in this forum thread "The RadListBox database update functionality does not support scenarios where items are transferred from a table to "many to many" table.". That means you will need to handle this scenario with custom database manipulation code. 

    Regards,
    Peter Milchev
    Telerik by Progress
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  3. UI for ASP.NET Ajax is Ready for VS 2017
Back to Top