ListBox with many to many database relationship

[José-Miguel]

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

[Peter Milchev]

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