Click here to Skip to main content
15,881,413 members
Articles / Web Development / ASP.NET

Having the CheckBoxList support many-to-many relationships

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
4 Jul 2007CPOL4 min read 40.9K   26   6
Example of implementing many-to-many multiple choice functionality the declarative way.


Yet again, I am surprised by the lack of basic GUI-functionality of many of the controls in ASP.NET (2.0), and how the upcoming, highly promising new generations of web controls keep disappointing us again and again! My problem this time was to allow the user to, for an item, select one or more related items from a list of checkboxes (preferably using the CheckBoxList control). This relationship is stored as a many-to-many relationship in the database, where the connecting database table contains the keys of the related items.

What I believe is a reasonable requirement of a CheckBoxList control is to, when read from the database, have the user's selections restored as previously saved. However, this is not as simple as it should be! I have seen many implementations of this on the internet, but none that uses the declarative approach using the new SqlDataSource control, I have tried to follow this time... Why must you always extend the basic controls to have basic functionality at hand!?!

I am sorry for my lack of positive attitude...but with a little creative (declarative) programming and some code fixes (non-declarative), I managed to get what I wanted (without having to create my own derived classes). So, this article contains code snippets for solving this problem! And, if you are in the same mode as I was before writing this article, perhaps this article will take you back on track again!?


The most beautiful solution to this problem would be to create my own control, extending the functionality of the CheckBoxList control. My approach would have been to create a new property "DataSelectedField", to be used when databinding the control to the SqlDataSource control, as the DataTextField and DataValueField do. However, the reason I did not go by this approach is of principle not to have to do all the work myself, and that this should have been done by the CheckBoxList already! Also, I'm determined to give the declarative programming style a chance! (However, if you have implemented this, please let me know! ;-)

The Code

My database tables are A and B, having a connecting table A_B, storing the associations between them, having the foreign key columns A_B.AID and A_B.BID. The focus is A, to be related to zero-or-many B. The following Select statement retrieves all rows in B, and its potential associations to a specific A (read LEFT JOIN):

        WHEN A_B.AID IS NULL THEN CAST(B.BID AS varchar(36)) 
    ELSE (CAST(B.BID AS varchar(36)) + '_' + CAST(A_B.AID AS varchar(36)))
    END AS DataValueField

Besides BID and Name, the query returns the value used for knowing if the item should be pre-selected ("DataValueField"), i.e., the pre-selected item should also have a "<underscore>A_B.AID" concatenated to it. @AID is the current A we focus on. (All IDs are of type uniqueidentifier, and treated as text on the client.)

Now you know the data model. I start from the CheckBoxList and go down... The code of the CheckBoxList then, looks like this:

<asp:CheckBoxList ID="checkBoxList" runat="server"

And the referred SqlDataSource code looks like this:

<asp:SqlDataSource ID="sqlDataSource" runat="server"
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
    SelectCommand="<the query as above>"
    DeleteCommand="DELETE FROM A_B WHERE AID = @AID">
        <asp:SessionParameter Name="AID" SessionField="ID" />
        <asp:SessionParameter Name="AID" SessionField="ID" />
        <asp:SessionParameter Name="AID" SessionField="ID" />

Note the InsertCommand's format placeholder (i.e. {0}), which will be used later. But before we go further into saving the information to the database, we must prepare the CheckBoxList for showing pre-selections, like so:

foreach (ListItem listItem in checkBoxList.Items)
    if (listItem.Value != string.Empty)
        string [] split = listItem.Value.Split(new Char[] {'_'});
        listItem.Value = split[0];
        listItem.Selected = (split.Length > 1) ? true : false;

This is where the DataValueField is transformed into Value and Selected (Checked, as it is transformed to from the ListItem to the CheckBox control) fields. This code should be executed after the control is data bound, meaning after the DataBound event has been fired.

So, now we have the list rendered, we go back to the saving part. The key problem of a connecting table is that the "update" to be made when the user saves, in real life, is a delete of unchecked items and insert of the newly checked items. We need to handle this ourselves, using both insert and delete commands, triggering the corresponding methods. So, instead of using the SqlDataSource's predefined commands, we trigger the updates manually from code (but we still have "declared" the queries).

This is the code for controlling updates, and to adjust the insert command:

// Run the DELETE-statement first.

// Construct the sql INSERT-statement for selected items:
StringBuilder sb = new StringBuilder();
foreach (ListItem listItem in checkBoxList.Items)
    if (listItem.Selected)
        if (sb.Length > 0)
if (sb.Length > 0)
    sqlDataSource.InsertCommand =

The code is straightforward, beginning with deleting all, and then constructing the IN-statement list for performing string.Format on the format placeholder, and then adding all selected items in the list. This code may be placed anywhere, e.g., in the Click event of a Button.


  • 2007-06-29: Initial version...feel free to give feedback!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
Sweden Sweden
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

GeneralInserting checkboxlist into database Pin
Mamphekgo Bahula11-Sep-08 22:57
Mamphekgo Bahula11-Sep-08 22:57 
GeneralDataSelectedField Implementation in CheckBoxList [modified] Pin
Bernhard Elbl17-Sep-07 6:30
Bernhard Elbl17-Sep-07 6:30 
GeneralInsert Statement not working Pin
DJFuller4-Sep-07 1:20
DJFuller4-Sep-07 1:20 
GeneralDemo Project Pin
WDI13-Jul-07 21:47
WDI13-Jul-07 21:47 
GeneralRe: Demo Project Pin
_JERKER_24-Jan-08 21:06
_JERKER_24-Jan-08 21:06 
GeneralRe: Demo Project Pin
Dewey25-May-08 8:39
Dewey25-May-08 8:39 

If you believe that, why are you whining so much about MS.

"Yet again I am surprised by the lack of basic GUI-functionality of many of the controls in ASP.NET (2.0), and how the upcoming, highly promising new generations of web controls keeps disappointing us again and again!"

Maybe they wanted YOU to exert some effort(without complaining), so YOU could learn something.

It pays to stop _JERKERING_ off!

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.