Q: I am using a DataList control on an .ascx control. The datalist is bound to a datatable. On the datalist item template, I have a checkbox server control. I need to create a DataBinder for the checked property. No matter how I bind to a specific column, I get an invalid cast during datalist.databind. The data is coming from SQL Server and can place the data in any format. (I use the DataBinder to work against the text property in the text box or label controls and all is well). How do I do this?
- TL, Cincinnati, Ohio
RELATED: "A Very Special CheckBoxList Control"
A: You would think this would be a whole lot easier than it is. After all, DataGrids bind nicely, so why shouldn't a CheckBoxList? Well, that would be a question for the folks in Redmond, but I can show you how to bind your checklists.
The CheckBoxList control is a collection of CheckBoxes that can be manipulated all at once. That's what gives us the data-binding ability. So let's start with a quick look at the CheckBox control. This control, once you strip off the formatting and such, has two properties: Text and Checked. When you move to the CheckBoxList control, you lose those properties. That's because the CheckBoxList implements a collection of ListItems, which have Text, Value, and Selected properties, two of which you can bind your data to. So a disconnect appears between the CheckBox and the CheckBoxList - but it's a disconnect you can overcome with code.
Figure 1 shows what you need to do to implement this. I'm basing this on the sample database I used in a previous asp.netNOW Q&A column (see Update Data Using a URL). The only difference is that the table can be accessed directly by the login being used.
Dim objConn As SqlConnection = New SqlConnection Dim objCmd As SqlCommand = New SqlCommand( _ "select emailaddress, sendmail from mailinglist", objConn) Dim chkItem As ListItem CheckBoxList1.DataTextField = "EmailAddress" CheckBoxList1.DataValueField = "SendMail" objConn.Open() CheckBoxList1.DataSource = objCmd.ExecuteReader CheckBoxList1.DataBind() For Each chkItem In CheckBoxList1.Items If chkItem.Value = 1 Then chkItem.Selected = True End If Next objConn.Close()
Figure 1. This code checks the CheckBoxList by walking through each ListItem in the CheckBoxList collection and setting Selected to true or false based on whether chkItem.Value has the value 1.
From the MailingList table, you pull two columns: EmailAddress and SendMail. If SendMail is a 1, the checkbox should be checked. I've seen many variations on setting the checkboxes in a CheckBoxList that pass and process a dataset. You can simplify the process by passing in the value that sets the checkbox in the DataValueField. If the CheckBoxList worked the way you'd expect it to, you could pass a value to the DataValueField and it would be resolved automatically to True or False, which would check the box for you. Unfortunately, it doesn't, so you walk through each ListItem in the CheckBoxList collection and set Selected to true or false based on the value.
That's all there is to it, except it really should work the way you expect it to. Of course, there's nothing that says you can't use the CheckBoxList as the basis of a control that does this automatically, but I'll leave that as an exercise for you.The sample code referenced in this article is available for download.
Josef Finsel is a software consultant with a global consulting firm and specializes in .NET and SQL Server. He has published a number of VB, .NET, and SQL Server-related articles, and when he isn't hanging around the aspnetpro forums, you can find him working on the syntax for FizzBin.NET, a programming language that works the way programmers have always suspected. He's also author of The Handbook for Reluctant Database Administrators (Apress).