Multiple Selections with Check Boxes and List Boxes

Or:

<form id="MyForm" runat="server">


<asp:ListBox id="lbxMyListBox" runat="server" SelectionMode="Multiple" />


</form>

Once the controls are on the page, populating them with data follows the familiar pattern: we open a connection, call the ExecuteReader() method of a command object, assign field names to the web server control's DataTextField and DataTextValue properties, perform the data bind, and close the connection. So far, so good.

Using Data from Multiple Selections

Handling multiple selections, however, requires more code and planning than the data-aware controls we've worked with so far. If you don't handle multiple selections properly, your code will tend to react only to the first item selected.

You can test this by making a copy of the radio button example, changing the control type to CheckBoxList , and making no changes to the way you handle the data. If you select Buchanan and Callahan, you'll just get the data for Buchanan.

The correct technique is the same for CheckBoxList, ListBox, and DropDownList controls, and consists of two steps:

• Loop through each item in the list, and check if it was selected. If it was selected, your code must react to the selection - usually by adding the value or text to a string that will be used elsewhere in the code.

• Then, check if any items were selected at all - is the string longer then zero? Write code in an If...Then...Else structure to handle the case of a selection, and the case of no selection.

To assist in this task, ADO.NET allows enumeration over its list objects, which means that we can use a ForEach...In loop to test every item in the list. The syntax for the "Is this item selected?" loop could therefore be the following:

Dim strSelectionResult As String


Dim liThisOne As ListItem


For Each liThisOne In ckbEmployees.Items


If liThisOne.Selected Then


strSelectionResult &= liThisOne.Value


End If



Next



' Remove next line prior to deployment


Response.Write("strSelectionResult = <br/>" & strSelectionResult & "<hr/>")

In the above code, we create a string (strSelectionResult) to hold the output of our loop - the values of the selected items, concatenated together. Often, though, we'll want to have some characters before and after each value in the string. Furthermore, we may have some text that goes outside of the whole list of selected values, such as the SQL WHERE keyword to start things off. You can imagine that such requirements will result in code that looks something like this:

For Each liThisOne In ckbEmployees.Items If liThisOne.Selected Then

strSelectionResult &= "



MyLeadingCharacters"


strSelectionResult &= liThisOne.Value

StrSelectionResult &= "



MyFollowingCharacters"


End If Next

strSelectionResult = "WHERE " & strSelectionResult

Once we've looped through the choices, we must check whether any of the items were selected. This is easy to do, since the presence of a selection will result in the length of our string immediately after the loop being greater than zero. The following sample assumes that you have a DataGrid object named dgMyDataGrid, and that you only want the grid to appear if a selection has been made.

If strSelectionResult.Length > 0 Then


dgMyDataGrid.Visible = True


' Work with the strSelectionResult, perhaps


' in an SQL statement to fill another grid


Else


dgMyDataGrid.Visible = False


End If

As a side issue - but mentioned here because SQL statements can quickly get quite complex -I find it invaluable to use trace statements in my development code. You can do this in a sophisticated way in Visual Studio, but even when you're using Notepad, it's not hard to write key troubleshooting information to the page. Bear in mind that if you do have a problem with the SQL statement, you may have to exit the method prior to making the connection, so that your trace information will show up on the page. We'll demonstrate this in the next example.

Try It Out - Multiple Selections

We're going to create a page that lists all the Northwind employees , with a check box next to each name . When one or more boxes are checked, we'll show a DataGrid containing information about the selected employee(s).

1. Create a new page named Multiple_selections.aspx , and fill it with the following code. This is the longest example we've used so far, but you should be familiar with most of its elements from the work we've already done.

   
   <%@ Import namespace="System.Data" %>
   
   
   <%@ Import namespace="System.Data.SqlClient" %>
   
   
   <html>
   
   
   <head><title>Multiple Selections</title></head>
   
   
   <body>
   
   
   <h3>Multiple Selections</h3>
   
   
   <form runat="server">
   
   
   <asp:CheckBoxList id="ckbEmployees" runat="server"
   
   
   RepeatLayout="table"
   
   
   RepeatDirection="vertical"
   
   
   RepeatColumns="3"
   
   
   
   CellPadding
   
   ="9"
   
   
   CellSpacing="18"
   
   
   TextAlign="right"
   
   
   OnSelectedIndexChanged="subListChange"
   
   
   AutoPostBack="true" />
   
   
   <br/><br/>
   
   
   <asp:DataGrid id="dgEmployee" runat="server" />
   
   
   </form>
   
   
   </body>
   
   
   </html>
   
   
   <script language="vb" runat="server">
   
   
   Sub Page_Load(Source As Object, E As EventArgs)
   
   
   If Not IsPostBack Then
   
   
   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
   
   
   Dim strSQLforCheckBoxes As String = "SELECT LastName, EmployeeID " & _
   
   
   "FROM Employees ORDER BY LastName;"
   
   
   Dim objConnection As New SqlConnection(strConnection)
   
   
   Dim objCommand As New SqlCommand(strSQLforCheckBoxes, objConnection)
   
   
   objConnection.Open()
   
   
   ckbEmployees.DataSource = objCommand.ExecuteReader()
   
   
   ckbEmployees.DataTextField = "LastName"
   
   
   ckbEmployees.DataValueField = "EmployeeID"
   
   
   ckbEmployees.DataBind()
   
   
   objConnection.Close()
   
   
   End If
   
   
   End Sub
   
   
   Sub subListChange(S As Object, E As EventArgs)
   
   
   ' Remove next line prior to deployment
   
   
   Response.Write("subListChange triggered<hr/>")
   
   
   Dim strWhereClause As String = ""
   
   
   Dim liThisOne As ListItem
   
   
   For Each liThisOne in ckbEmployees.Items
   
   
   If liThisOne.Selected Then
   
   
   strWhereClause &= "EmployeeID=" & liThisOne.Value & " OR "
   
   
   End If
   
   
   Next
   
   
   ' Remove next line prior to deployment
   
   
   Response.Write("strWhereClause = <br/>" & strWhereClause & "<hr/>")
   
   
   If strWhereClause.Length > 0 Then
   
   
   dgEmployee.Visible = True
   
   
   ' This line
   
   removes
   
   the final " OR " from the WHERE clause
   
   
   strWhereClause = Left(strWhereClause, StrWhereClause.Length - 4)
   
   
   strWhereClause = "WHERE " & strWhereClause
   
   
   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")
   
   
   Dim strSQLforGrid As String = "SELECT TitleOfCourtesy, FirstName, " & _
   
   
   "LastName, Country, Region, City, Notes " & _
   
   
   "FROM Employees " & strWhereClause
   
   
   Dim objConnection As New SqlConnection(strConnection)
   
   
   Dim objCommand As New SqlCommand(strSQLforGrid, objConnection)
   
   
   ' Remove next line prior to deployment
   
   
   Response.Write("strSQLforGrid = <br/>" & strSQLforGrid & "<hr/>")
   
   
   objConnection.Open()
   
   
   dgEmployee.DataSource = objCommand.ExecuteReader()
   
   
   dgEmployee.DataBind()
   
   
   objConnection.Close()
   
   
   Else
   
   
   dgEmployee.Visible = False
   
   
   End If
   
   
   End Sub
   
   
   </script>
   
   

2. Take a look at the results, which ( assuming that you've commented out the debugging lines) should resemble the following:

   

3. Then, when you make a selection of two names, you should get:

   

How It Works

In order of execution, what happens first is that in the Page_Load() handler, a set of check boxes is populated from a data store. Then, in subListChange() , we have code that determines what (if anything) was selected in the check boxes. Last, we have code that populates the DataGrid control. Keep clear in your mind that we connect, use a command, and bind twice - once for the check boxes that show the names, and then again for the DataGrid that displays the information.

Starting with the HTML, and ignoring any formatting detail, the first thing to note is that setting the AutoPostBack attribute makes a 'submit' button unnecessary - whenever a box is checked or unchecked, the DataGrid will be repopulated. Also note that we've wired up a method called subListChange() to run whenever there is a change in the selection.

<html> ... <form runat="server"> <asp:CheckBoxList id="ckbEmployees" runat="server" ... OnSelectedIndexChanged="subListChange" AutoPostBack="True" /> <br/><br/> <asp:DataGrid id="dgEmployee" runat="server" /> </form> </body> </html>

The code that places the check boxes contains nothing new - as usual, it's wrapped in the "If Not IsPostBack" syntax to make sure that the check box list isn't repopulated after every selection. When the user clicks on a check box, the list object will trigger the SelectedIndexChanged event, which calls the subListChange() method below. In it, we build up a string of values from the selected items that will form a SQL WHERE clause for the DataGrid .

Sub subListChange(S As Object, E As EventArgs) ' Remove next line prior to deployment Response.Write("subListChange triggered<hr/>") Dim strWhereClause As String = "" Dim liThisOne As ListItem For Each liThisOne in ckbEmployees.Items If liThisOne.Selected Then strWhereClause &= "EmployeeID=" & liThisOne.Value & " OR " End If Next

Clearly, it's possible that there will be no selection at all, if the user's action was to uncheck the only checked box. To deal with this, we take a look at the length of the string holding the WHERE clause. If selections were made, then we execute all of the connection, command and binding code for the DataGrid object.

' Remove next line prior to deployment Response.Write("strWhereClause = <br/>" & strWhereClause & "<hr/>") If strWhereClause.Length > 0 Then dgEmployee.Visible = True ' This line removes the final ' OR ' from the WHERE clause strWhereClause = Left(strWhereClause, strWhereClause.Length - 4) strWhereClause = "WHERE " & strWhereClause Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim strSQLforGrid As String = "SELECT TitleOfCourtesy, FirstName, " & _ "LastName, Country, Region, City, Notes " & _ "FROM Employees " & strWhereClause Dim objConnection As New SqlConnection(strConnection) Dim objCommand As New SqlCommand(strSQLforGrid, objConnection) ' Remove next line prior to deployment Response.Write("strSQLforGrid = <br/>" & strSQLforGrid & "<hr/>") objConnection.Open() dgEmployee.DataSource = objCommand.ExecuteReader() dgEmployee.DataBind() objConnection.Close()

If the check box list contained no selections, then we can just hide the DataGrid, keeping the page neat and tidy.

Else dgEmployee.Visible = False End If