RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Simplify Dependent Lists with the Microsoft AJAX CascadingDropDown Control : Page 2

In many Web applications, when a user selects an option from a dropdown list, the set of options on another list must change, a challenge known as the "dependent list problem." The CascadingDropDown control solves the problem with aplomb.

Adding a Web Service to the Project
With the UI of the page out of the way, add a new WebService item to the current project. Using the default name of WebService.asmx, you should find the WebService.vb file located in the App_Code folder of the project.

Open the WebService.vb file and import the following namespaces (place them at the top of the file):

   Imports AtlasControlToolkit
   Imports System.Data
   Imports System.Data.SqlClient
Next, define a GetAuthors() function that will provide data to populate the Authors dropdown list. This is the function named in the ServiceMethod attribute of the CascadingDropDownProperties element in the code above. Here's the function code:

   <WebMethod()> _
   Public Function GetAuthors( _
   ByVal knownCategoryValues As String, _
   ByVal category As String) As _
      Dim values As New _
         System.Collections.Generic.List( _
         Of AtlasControlToolkit.CascadingDropDownNameValue)
      Dim conn As New SqlConnection( _
         "Data Source=.\SQLEXPRESS;Initial Catalog=pubs;" & _
         "Integrated Security=True")
      Dim comm As New SqlCommand( _
         "SELECT au_id, au_lname, au_fname FROM authors", conn)
      Dim reader As SqlDataReader = comm.ExecuteReader
      While reader.Read
         values.Add(New CascadingDropDownNameValue( _
            reader("au_lname") & " " & _
            reader("au_fname"), _
      End While
      Return values.ToArray
   End Function
The method must have exactly the following signature; even the parameter names must match exactly:

   <WebMethod()> _
   Public Function GetAuthors( _
      ByVal knownCategoryValues As String, _
      ByVal category As String) As _
The knownCategoryValues parameter will contain the key/value of the selected category, and the category parameter will contain the name of the category. For example, when the page loads the first time, it passes the following arguments to GetAuthors():

Here, the first argument is empty while the second argument is the category name defined in the CascadingDropDownProperties element.

This function returns an array of name/value pairs of type AtlasControlToolkit.CascadingDropDownNameValue; the return type is mandatory. The code shown connects to the pubs database in a SQL Server 2005 Express server instance and retrieves the authors' names and respective ID values (from the au_id column), using the au_id as the key for the name/value pair. For this function, you don't really care about the arguments passed in because we will load the entire list of authors anyway.

Author's Note: I am assuming you have the pubs database installed on a SQL Server instance. I used SQL Server 2005 Express Edition, which ships with Visual Studio 2005.

The relationships between the authors, titleauthor and titles tables are shown in Figure 2.

Figure 2. Pubs Database Relationships: The figure shows the relationships between the authors, titleauthor, and titles tables in the pubs database.
When a user selects an author from the Authors dropdown list, you'll need to retrieve the titles written by that author from the database, so the next function you need to define is GetTitles() (see Listing 1).

As you can see, the GetTitles() function is a little more complex than GetAuthors(). First, this function gets called only when the user selects an author in the "Authors" DropDownList control. At that point, the application invokes the GetTitles() function to fill the titles list with titles written by the selected author, using arguments such as:

In the preceding example call, the "Author" is the key and the "238-95-7766" is its corresponding value.

Using those values, the GetTitles() function will return only the relevant titles based on the author id (238-95-7766). To make it easy for you to extract the category value(s) contained in the knownCategoryValues parameter, the CascadingDropDown class has a helper method called ParseKnownCategoryValuesString():

   Dim kv As StringDictionary = CascadingDropDown. _
To check if the argument contains an au_id, the function contains the following code:

      If Not (kv.ContainsKey("Author")) Then
         Return Nothing
      End If
Finally, GetTitles() retrieves the list of titles belonging to the specific author, which it finds by joining the titleauthor and titles table when it builds the SQL statement:

      Dim sql As String = _
         "SELECT titles.title, titles.title_id FROM titleauthor," & _
         " titles WHERE (titleauthor.title_id = titles.title_id)" & _
         " AND (titleauthor.au_id='" & kv("Author") & "')"

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date