Creating Dependent DropDownLists...

Forcing the contents of one DropDownList to change based upon the selection of a value from another DropDownList was often a little messy in clasic ASP. In .NET it is far easier.

By: John Kilgo Date: November 30, 2003 Download the code.

Forcing the contents of DropDownList2 to change based upon the selection made from DropDownList1 is a fairly common requirement of dynamic web pages. .NET makes this fairly simple to handle by using the OnSelectedIndexChanged event and setting AutoPostBack to True. To do this dynamically, retrieving the contents of DropDownList2 from a database table, you need a key - foreign key relationship as the value of each DropDownList.

In this example program we will use the Authors, TitleAuthor, and Titles tables of the Pubs database. In the Page_Load event we will fill the first DropDownList with Author names as text, and author ID as the value. Upon selection of an Author from the Authors DropDownList, we will, using the OnSeledtedIndexChanged event, use the selected au_id value in the WHERE clause of SQL statement to populate the second DropDownList with Title ID's. We will then use the OnSelectedIndexChanged event of the title id's DropDownList to fill a TextBox with the Title of the book from the Titles table.

The DependentDropDowns.aspx page is shown below. Notice that setting up the OnSelectedIndexChanged events in both dropdowns, and that AutoPostBack is set to true for each.

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DependentDropDowns.aspx.vb" Inherits="DotNetJohn.DependentDropDowns"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>DependentDropDowns</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<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">
<table border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td>Authors</td>
    <td>Title_ID</td>
    <td>Title</td>
  </tr>
  <tr>
    <td><asp:DropDownList ID="ddlAuthors"
                          runat="server"
                          AutoPostBack="True"
                          OnSelectedIndexChanged="FillTitleAuthor" /></td>
    <td><asp:DropDownList ID="ddlTitleAuthor"
                          Runat="server"
                          AutoPostBack="True"
                          OnSelectedIndexChanged="FillTitles" /></td>
    <td><asp:TextBox ID="txtTitles" Runat="server" Columns="50" ReadOnly="True" /></td>
  </tr>
</table>
</form>
</body>
</HTML>

Following is the codebehind file presented in two parts. This first part includes the Page_Load event where we are first filling the Authors dropdown with the last and first names of the authors.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DependentDropDowns
  Inherits System.Web.UI.Page

' - Web Form Designer Generated Code Omitted -

  Private strConnection As String = ConfigurationSettings.AppSettings("PubsConnection")
  Private strSql As String
  Private objConn As SqlConnection

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not IsPostBack Then
      'Fill Authors DropDown
      strSql = "SELECT au_id, au_lname + ', ' + au_fname [FullName] FROM Authors ORDER BY au_lname"
      objConn = New SqlConnection(strConnection)
      Dim objCmd As New SqlCommand(strSql, objConn)
      Dim _reader As SqlDataReader
      Try
        objConn.Open()
        _reader = objCmd.ExecuteReader
        With ddlAuthors
          .DataSource = _reader
          .DataValueField = "au_id"
          .DataTextField = "FullName"
          .DataBind
        End With
        ddlAuthors.Items.Insert(0, "-Select-")
      Catch ex As SqlException
        Response.Write(ex.ToString)
      Finally
        _reader.Close()
        objConn.Dispose()
      End Try
    End If
  End Sub

The remainder of the codebehind file is presented below. It first contains the FillTitleAuthor() subroutine called by the OnSelectedIndexChange event of the authors dropdown. Notice the WHERE clause in the SQL statement. There we are populating au_id with the author id associated with the selected Author from the first dropdown. The resulting SQL statement is used to populate the second DropDownList. (Many of the Authors have only one title_id but the principal is the same no matter how many there are.)

A similar process is then used to get the title for the TextBox using the title_id selected from the second DropDownList.

  Protected Sub FillTitleAuthor(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlAuthors.SelectedIndexChanged
    'Clear Titles Textbox
    txtTitles.Text = ""
    'Fill TitleAuthor DropDown
    strSql = "SELECT au_id, title_id FROM TitleAuthor " & _
             "WHERE au_id='" & ddlAuthors.SelectedValue() & "'" & _
             " ORDER BY title_id"
    objConn = New SqlConnection(strConnection)
    Dim objCmd As New SqlCommand(strSql, objConn)
    Dim _reader As SqlDataReader
    Try
      objConn.Open()
      _reader = objCmd.ExecuteReader
      With ddlTitleAuthor
        .DataSource = _reader
        .DataValueField = "title_id"
        .DataTextField = "title_id"
        .DataBind
      End With
      ddlTitleAuthor.Items.Insert(0, "-Select-")
    Catch ex As SqlException
      Response.Write(ex.ToString)
    Finally
      _reader.Close()
      objConn.Dispose()
    End Try
  End Sub

  Protected Sub FillTitles(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ddlTitleAuthor.SelectedIndexChanged
    'Clear Titles Textbox
    txtTitles.Text = ""
    'Fill Titles TextBox
    strSql = "SELECT title FROM Titles " & _
             "WHERE title_id='" & ddlTitleAuthor.SelectedValue() & "'"
    objConn = New SqlConnection(strConnection)
    Dim objCmd As New SqlCommand(strSql, objConn)
    Dim _reader As SqlDataReader
    Try
      objConn.Open()
      _reader = objCmd.ExecuteReader(CommandBehavior.SingleRow)
      If _reader.Read Then
        txtTitles.Text = _reader("title")
      End If
    Catch ex As SqlException
      Response.Write(ex.ToString)
    Finally
      _reader.Close()
      objConn.Dispose()
    End Try
  End Sub

End Class

You may run the example program here.
You may download the code here.