# 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


• If nought else, you can use AndAlso and stop check for True: If li.Text = "AA" AndAlso Not CodeData.IsOneNull AndAlso CodeData.One Then li.Selected = True. – HABO Jun 15 '13 at 12:20
• Ok, but is it a better approach for me to change the table structure to just store the value in one column? I would have to use a for loop and delete all items before inserting each time the page is saved. Just not sure about the best approach. – user2301181 Jun 16 '13 at 23:57
• If you really need to support nullable bits then I would keep them as separate bit columns. Breaking the repeating code out into a small Sub might help with readability and reliability (DRY). – HABO Jun 17 '13 at 0:14
• I think the code was designed this way to prevent the need to loop thru tons of table rows on page load. There could be 20+ selected checkboxes for one user id. They developer thought the bit field approach would be easier on the database(one row for each user) – user2301181 Jun 17 '13 at 10:29

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

• Your recommendations are extremely helpful and I've learned a lot from your post. Thanks! – user2301181 Jun 18 '13 at 3:02