Populating and saving checkboxlist items to the database I have inherited an application that has code to save and populate values in an ASP.NET checkboxlist control. The existing code is very lengthy and does not appear to be efficient to me. I plan on rewriting this application and have complete control over the database design.

All of the checkboxlist items are stored in an SQL table as bit fields. Each row of checkboxlist values are assigned to a user ID. Once again, my goal is to find a more efficient way to save and populate selected checkboxlist items with less code. I'm open to changing the table design, using Linq or lambda if it helps.  I'm new to ASP.NET but there must be a better way of doing this.

Current table design
UserID  AA  BC  AC
1       1    1  0 
2       0    1  1
3       1    1  0 
4       1    1  1

'Code to populate selected checkboxlist items(there are a total of 22 bit values)

Dim CodeData as datarow
CodeData = CodeDataTable.Rows(0)
For Each li As ListItem In Me.cblCodes.Items
                    If li.Text = "AA" Then
                       If Not CodeData.IsOneNull Then
                          If CodeData.One = True Then
                               li.Selected = True
                        End If
                    End If
                End If
                  If li.Text = "BC" Then
                    If Not CodeData.IsTwoNull Then
                        If CodeData.Two = True Then
                            li.Selected = True
                        End If
                    End If
                End If
         If li.Text = "AC" Then
                    If Not CodeData.IsTwoNull Then
                        If CodeData.Two = True Then
                            li.Selected = True
                        End If
                    End If
                End If  
            ......

Next

'Code to save checkboxlist values
 Dim AA As Boolean = False
 Dim BC As Boolean = False
 Dim AC As Boolean = False
 Dim AD As Boolean = False


 AA = Me.cblCodes.Items.FindByText("AA").Selected()
 BC = Me.cblCodes.Items.FindByText("BC").Selected
 AC = Me.cblCodes.Items.FindByText("AC").Selected
 AD = Me.cblCodes.Items.FindByText("AD").Selected

 CodeTableAdapter.UpdateCodes(UserID,AA,BC,AC,AD.......)
I think it's reasonable to look at whether a single column design is possible - it certainly makes life easier:

For Each DataRow dr in CodeDataTable.Rows
    Dim li as ListItem = Me.cblCodes.Items.FindByText(CType(dr["Text"], String))
    li.Selected = True
Next


You could alternatively do a two column design, with a Text varchar and a Value bit:

For Each DataRow dr in CodeDataTable.Rows
    Dim li as ListItem = Me.cblCodes.Items.FindByText(CType(dr["Text"], String))
    li.Selected = CType(dr["Value"], Boolean)
Next


But even the wide table could be done with less repetitive code by using DataTable.Columns to get the names of the columns:

Dim codeData as DataRow = CodeDataTable.Rows(0)
For Each DataColumn dc in CodeDataTable.Columns
    Dim li as ListItem = Me.cblCodes.Items.FindByText(dc.Name)
    If Not codeData.IsNull(dc) AndAlso CType(codeData[dc], Boolean) Then
        li.Selected = True
    End If
Next