Obtaining Data Based Upon Multiple Selections From a ListBox...

You can allow multiple selections in a ListBox control. You may then use the multiple selections in a WHERE clause to get data from a database.

By: John Kilgo

Date: June 7, 2003

Download the code.

This article will demonstrate how to allow multiple selections in a ListBox control and how to build a WHERE clause on the fly that will fill a DataGrid with data based upon the selections made in the ListBox. There is really no new ground broken here. The article just demonstrates a technique or two you may want to have in your bag of tricks.

Lets get started with the .aspx page, MultiSelADO.aspx. There are only three things you should take note of, and they are all in the ListBox setup. First, we have set up a handler for the OnSelectedIndexChanged event. Second, we have set AutoPostBack to True, and third, we have SelectionMode to "Multiple". We will build the WHERE clause for our datagrid's SELECT statement in the ChangeWhereClause subroutine in the code-behind page.

<%@ Page Language="vb" Src="MultiSelADO.aspx.vb" Inherits="MultiSelADO"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>MultiSelADO</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<b>Choose as many names as you wish, in any order,<br>by using the Shift or CTRL key</b>
<p></p>
<asp:ListBox id="lsbEmployee"
             runat="server"
             Font-Name="verdana"
             BackColor="Ivory"
             Height="150px"
             OnSelectedIndexChanged="ChangeWhereClause"
             AutoPostBack="True"
             SelectionMode="Multiple">
</asp:ListBox>
<p></p>
<asp:DataGrid ID="dtgEmployee"
              Runat="server"
              HeaderStyle-BackColor="IndianRed"
              HeaderStyle-ForeColor="White"
              HeaderStyle-Font-Bold="True"
              HeaderStyle-Font-Name="Verdana"
              HeaderStyle-Font-Size="10pt"
              ItemStyle-BackColor="Gainsboro"
              ItemStyle-ForeColor="Black"
              ItemStyle-Font-Name="Verdana"
              ItemStyle-Font-Size="10pt"
              CellPadding="4" />
</form>
</body>
</html>

Now for the code-behind page, MultiSelADO.aspx.vb. To make it easier to talk about, the code-behind page is presented in two parts below. In the Page_Load event we are just populating our ListBox with last names from the employees table from the Northwind database. We are setting the DataTextField (what the user sees) to LastName, and the DataValueField to EmployeeID.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports Microsoft.VisualBasic

Public Class MultiSelADO
  Inherits System.Web.UI.Page

  Protected WithEvents dtgEmployee As System.Web.UI.WebControls.DataGrid
  Protected WithEvents lsbEmployee As System.Web.UI.WebControls.ListBox
  Protected li As System.Web.UI.WebControls.ListItem

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not IsPostBack Then
      Dim strSql As String = "Select LastName, EmployeeID From Employees Order By LastName"
      Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
      Dim objCmd As New SqlCommand(strSql, objConn)
      Try
        objConn.Open()
        lsbEmployee.DataSource = objCmd.ExecuteReader()
        lsbEmployee.DataTextField = "LastName"
        lsbEmployee.DataValueField = "EmployeeID"
        lsbEmployee.DataBind()
      Catch exc As SqlException
        Response.Write(exc.ToString())
      Finally
        objConn.Dispose()
      End Try
    End If
  End Sub

The subroutine ChangeWhereClause is where all the action takes place. In the first section of code we use a For Each loop to cycle through the ListBox looking for selected items. When we find one we append EmployeeID = li.Value to our WHERE clause. We do this for each item that is selected. In the next section of code we first strip off the hanging " Or " left by the last selected item and then append the WHERE clase (strWhereClause) to our SQL SELECT statement. We then execute a Reader and populate the DataGrid. Thats about all there is to it.

  Sub ChangeWhereClause(Sender As System.Object, e As System.EventArgs)
    Dim strWhereClause As String = ""
    For Each li in lsbEmployee.Items
      If li.Selected Then
        strWhereClause &= "EmployeeID=" & li.Value & " Or "
      End If
    Next
    If strWhereClause.Length > 0 Then
      dtgEmployee.Visible = True
      'Chop off last " Or "
      strWhereClause = Left(strWhereClause, strWhereClause.Length() - 4)
      strWhereClause = "WHERE " & strWhereClause
      Dim strSql = "Select FirstName, LastName, Country, Region, City " _
                 & "From Employees " & strWhereClause & " Order By LastName"
      Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
      Dim objCmd As New SqlCommand(strSql, objConn)
      Try
        objConn.Open()
        dtgEmployee.DataSource = objCmd.ExecuteReader()
        dtgEmployee.DataBind()
      Catch exc As SqlException
        Response.Write(exc.ToString())
      Finally
        objConn.Dispose()
      End Try
    Else
      dtgEmployee.Visible = False
    End If
  End Sub

End Class

As I said at the beginning of the article, no new ground was broken here, but I hope the article demonstrates a technique you can use when you find the need.

You may download the code here.