When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article
Jobs

ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
 
Print this Page!
Published: Wednesday, January 20, 2010

Accessing and Updating Data in ASP.NET: Filtering Data Using a CheckBoxList

By Scott Mitchell


A Multipart Series on ASP.NET's Data Source Controls
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

  • Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
  • Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.
  • Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
  • Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.
  • Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
  • Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.
  • Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )

    Introduction


    Filtering Database Data with Parameters, an earlier installment in this article series, showed how to filter the data returned by ASP.NET's data source controls. In a nutshell, the data source controls can include parameterized queries whose parameter values are defined via parameter controls. For example, the SqlDataSource can include a parameterized SelectCommand, such as: SELECT * FROM Books WHERE Price > @Price. Here, @Price is a parameter; the value for a parameter can be defined declaratively using a parameter control. ASP.NET offers a variety of parameter controls, including ones that use hard-coded values, ones that retrieve values from the querystring, and ones that retrieve values from session, and others.

    Perhaps the most useful parameter control is the ControlParameter, which retrieves its value from a Web control on the page. Using the ControlParameter we can filter the data returned by the data source control based on the end user's input. While the ControlParameter works well with most types of Web controls, it does not work as expected with the CheckBoxList control. The ControlParameter is designed to retrieve a single property value from the specified Web control, but the CheckBoxList control does not have a property that returns all of the values of its selected items in a form that the CheckBoxList control can use. Moreover, if you are using the selected CheckBoxList items to query a database you'll quickly find that SQL does not offer out of the box functionality for filtering results based on a user-supplied list of filter criteria.

    The good news is that with a little bit of effort it is possible to filter data based on the end user's selections in a CheckBoxList control. This article starts with a look at how to get SQL to filter data based on a user-supplied, comma-delimited list of values. Next, it shows how to programmatically construct a comma-delimited list that represents the selected CheckBoxList values and pass that list into the SQL query. Finally, we'll explore creating a custom parameter control to handle this logic declaratively. Read on to learn more!

    - continued -

    Filtering Data Based On A Set Of Values


    Drop-down lists are a very common user interface element used to filter data. For example, when searching for a product on Amazon.com you can search the entire store or limit your search to a particular area, such as Books, Electronics, Toys, and so on. These various stores are listed in a drop-down list, allowing the end user to search against a particular store. Creating a SQL query to perform such a filtered search is pretty straightforward. Assuming that there is a Products table with a StoreId column that specifies what store each product belongs to, the following SQL query would suffice:

    SELECT ...
    FROM Products
    WHERE ProductName LIKE @ProductName AND StoreId = @StoreId

    But what if Amazon wanted to let its shoppers search across multiple stores? Perhaps a user might want to search for all products with a particular name in the Books, Electronics, and DVDs stores. To accommodate this, Amazon might use a list of checkboxes, with one checkbox for each store. The user could then type in their search criteria and select the stores to search. But how would you create a SQL query to filter the products based on the user's store selections?

    SQL includes an IN keyword, which can be used in the following way:

    SELECT ...
    FROM Products
    WHERE ProductName LIKE @ProductName AND StoreId IN (1,3,9)

    Unfortunately, the IN keyword does not work with parameters. The following statement will fail:

    DECLARE @StoreIds nvarchar(100) = '1,3,9'

    SELECT ...
    FROM Products
    WHERE ProductName LIKE @ProductName AND StoreId IN (@StoreIds)

    The good news is that there is a (relatively) easy workaround. The IN keyword can use a query against a table, like so:

    SELECT ...
    FROM Products
    WHERE ProductName LIKE @ProductName AND StoreId IN (SELECT StoreId FROM SomeTable)

    You can build a User-Defined Function (UDF) that takes a comma-delimited string as an input and returns a table that contains one record for each input. In Creating a User Defined Function in SQL for Comma-Delimited Searches author Corey Aldebol shows how to build such a UDF, which he named Split. The Split UDF takes in two inputs - a delimited string and the delimiter character - and returns a table with one record for each value in the delimited string. Using the Split UDF you can filter the results based on a parameter, like so:

    DECLARE @StoreIds nvarchar(100) = '1,3,9'

    SELECT ...
    FROM Products
    WHERE ProductName LIKE @ProductName AND StoreId IN (SELECT Value FROM dbo.Split(@StoreIds,','))

    The subquery SELECT Value FROM dbo.Split(@StoreIds,',') returns a table with three rows:

    IDValue
    11
    23
    39

    For step-by-step instructions on creating and using the Split UDF, refer to Creating a User Defined Function in SQL for Comma-Delimited Searches.

    Filtering Data Based On a CheckBoxList's Selections


    The demo available for download at the end of this article illustrates how to implement such a multi-category filtering user interface in an ASP.NET page using a CheckBoxList control. The demo includes a database with two tables: Employees and Departments. The records in the Departments table define the various departments in the company - IT, Sales, Marketing, and so forth. The records in the Employees table model the employees at the company, and include attributes like Name, Salary, and DepartmentID, among others.

    To let users view users in one or more departments, we could build an ASP.NET page that contains a CheckBoxList control that's populated with the records from the Departments table and a GridView control that displays the Employees table's records whose DepartmentID values belong to one of the selected departments from the CheckBoxList. The screen shot below shows this demo in action. Note that each department is listed as a checkbox. The Executive and Marketing departments are selected, which displays those employees in the Marketing and Executive departments.

    The employees in the selected departments are displayed.

    The GridView in this demo is populated using a SqlDataSource control that contains the following SelectCommand:

    SELECT e.Name, e.Salary, d.DepartmentName
    FROM Employees e
       INNER JOIN Departments d ON
          e.DepartmentID = d.DepartmentID
    WHERE e.DepartmentID IN (SELECT Value FROM dbo.Split(@DepartmentIDs, ','))
    ORDER BY e.Name

    The query joins the Employees and Departments tables, but only returns those employees whose DepartmentID value is in the set of values returned by the Split UDF. The Split UDF is passed in a parameter named @DepartmentIDs, which is a comma-delimited list of the DepartmentID values selected from the CheckBoxList.

    There's only one challenge left remaining - converting the CheckBoxList's selected items into a comma-delimited string and assigning that string to the @DepartmentIDs parameter. Ideally, we'd be able to use the ControlParameter to craft a comma-delimited string containing the values of the selected items in the CheckBoxList. However, the ControlParameter doesn't offer such functionality. The good news is that we can craft and assign this comma-delimited string to the parameter programmatically, which we'll see how to do in the next section. Following that, we'll look at building a custom parameter control to handle this for us declaratively.

    Programmatically Converting a CheckBoxList's Selected Items Into a Comma-Delimited String


    In Examining the Data Source Control's Events we explored the various events that are raised by the data source controls when the perform their various operations. When getting data the data source control raises its Selecting event, which gives us (the page developer) an opportunity to inspect or modify the request before it gets transmitted. When using a SqlDataSource control we can modify the Command object from the Selecting event handler, which allows us to set parameter values, if needed.

    In this case we need to set the @DepartmentIDs parameter to the comma-delimited list of selected values in the CheckBoxList. This can be accomplished in the Selecting event handler with the following code:

    Protected Sub dsEmployeesByDepartments_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles dsEmployeesByDepartments.Selecting
       'Determine which checkboxes have been checked
       Dim selectedDepartmentIDs As New List(Of String)
       For Each li As ListItem In cblDepartments.Items
          If li.Selected Then
             selectedDepartmentIDs.Add(li.Value)
          End If
       Next

       'Convert the List of DepartmentIDs into a comma-delimited string
       Dim departmentIDsAsCommaDelimitedList As String = String.Join(",", selectedDepartmentIDs.ToArray())

       'Assign departmentIDsAsCommaDelimitedList to the @DepartmentIDs parameter
       e.Command.Parameters("@DepartmentIDs").Value = departmentIDsAsCommaDelimitedList
    End Sub

    The first block of code enumerates the items in the cblDepartments CheckBoxList. For each selected list item - that is, for each checked checkbox - the value of that list item is added to a list of strings, selectedDepartmentIDs. Once this list has been computed, the next step is to turn it into a comma-delimited string. This is accomplished using the String.Join method, which combines an array of strings into a single string, delimiting each array element with a specified string (in this case, a comma). Finally, this comma-delimited list is assigned to the @DepartmentIDs parameter.

    That's all there is to it! With the above code in the SqlDataSource control's Selecting event handler, whenever the SqlDataSource goes to the database to retrieve data it will set the value of the @DepartmentIDs parameter to the comma-delimited string of selected items in the CheckBoxList.

    Creating a Custom Parameter Control - CheckBoxListParameter


    While programmatically crafting and assigning this comma-delimited string to the parameter certainly works, it requires a bit of code. One of the major benefits of the data source controls, though, is that they can be used entirely declaratively. The variety of parameter controls allow us to filter data based on querystring values, session, or other Web controls on the page without having to write a line of code, so why should it be any different when filtering against a CheckBoxList? We can obviate the need for writing code by creating our own custom parameter control.

    In Creating Custom Parameter Controls, an earlier article in this series, we looked at creating custom parameter controls. Creating a custom parameter control entails creating a class that extends the Parameter class, which is the base class for all data source parameter controls. The Parameter spells out the core functionality of every parameter control. At a minimum, a parameter control must provide an Evaluate() method that returns the value for the parameter. The Evaluate method simply returns the value for the parameter and, as such, the code for this method is typically very short and simple.

    The download available at the end of this article includes a collection of custom parameter controls in a Class Library project named skmParameters. One of the parameter controls is named CheckBoxListParameter. As its name suggests, this control operates on a CheckBoxList control and returns a comma-delimited list of the selected values in a specified CheckBoxList. The most pertinent portions of the CheckBoxListParameter class follow. Note that some methods have been omitted for brevity. Selecting event handler with the following code:

    Public Class CheckBoxListParameter
       Inherits Parameter

       Public Property ControlID() As String
          Get
             Dim val As Object = MyBase.ViewState("ControlID")
             If val Is Nothing Then
                Return String.Empty
             Else
                Return val.ToString()
             End If
          End Get
          Set(ByVal value As String)
             If (Me.ControlID <> value) Then
                ViewState("ControlID") = value
                MyBase.OnParameterChanged()
             End If
          End Set
       End Property


       Protected Overrides Function Evaluate(ByVal context As HttpContext, ByVal control As Control) As Object
          'Make sure the page developer has specified a value for the ControlID property
          If String.IsNullOrEmpty(Me.ControlID) Then
             Throw New ArgumentException("ControlID parameter not specified.")
          End If

          'Find the control referenced by the ControlID property
          Dim referencedControl As Control = FindControl(control, Me.ControlID)
          If referencedControl Is Nothing Then
             Throw New InvalidOperationException(String.Format("Could not find CheckBoxList control '{0}'.", Me.ControlID))
          ElseIf Not TypeOf referencedControl Is CheckBoxList Then
             Throw New InvalidOperationException(String.Format("Control '{0}' is not a CheckBoxList control.", Me.ControlID))
          End If


          'Gather up the selected values of the CheckBoxList
          Dim selectedDepartmentIDs As New List(Of String)
          For Each li As ListItem In CType(referencedControl, CheckBoxList).Items
             If li.Selected Then
                selectedDepartmentIDs.Add(li.Value)
             End If
          Next

          'Return a comma-delimited string of the selected values
          Return String.Join(",", selectedDepartmentIDs.ToArray())
       End Function
    End Class

    The CheckBoxListParameter class contains a single property named ControlID, which the page developer uses to specify the ID of the CheckBoxList control to use. The Evaluate method starts by ensuring that the ControlID property is set and that is references a valid CheckBoxList control on the page. After those checks the code should look similar - it's identical to the code we put in the SqlDataSource control's Selecting event handler earlier in this article. Namely, the code loops through the items in the CheckBoxList, adding the value of each selected item to a List of string. It then uses String.Join to convert that List into a comma-delimited string.

    To use the CheckBoxListParameter in an ASP.NET page you must first add the skmParameters.dll file to your web application's Bin folder. Next, add the following @Reference directive at the top of the pages where you intend on using the custom parameter controls:

    <%@ Register Assembly="skmParameters" Namespace="skmParameters" TagPrefix="skm" %>

    Finally, add the appropriate declarative markup in the data source control's parameters section. For example, to use the CheckBoxListParameter to set a parameter value for the SqlDataSource control's SelectCommand, use markup like so:

    <asp:SqlDataSource ID="dsEmployeesByDepartments" runat="server" ...
         SelectCommand="SELECT ... FROM ... WHERE DepartmentID IN (SELECT Value FROM dbo.Split(@DepartmentIDs,','))">
       <SelectParameters>
          <skm:CheckBoxListParameter Name="DepartmentIDs" ControlID="cblDepartments" />
       </SelectParameters>
    </asp:SqlDataSource>

    Note how the CheckBoxListParameter markup defines the name of the parameter via its Name attribute and the ID of the CheckBoxList control on the page via its ControlID property. That's all there is to it - no code needed.

    Happy Programming!

  • By Scott Mitchell


    Attachments:


  • Download the code used in this article

    Further Readings:


  • Filtering Database Data with Parameters
  • Creating a User Defined Function in SQL for Comma-Delimited Searches
  • Examining the Data Source Control's Events
  • Creating Custom Parameter Controls
  • A Multipart Series on ASP.NET's Data Source Controls
    ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

  • Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
  • Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.
  • Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
  • Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.
  • Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
  • Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.
  • Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article