Using ASP.NET AJAX CascadingDropDown with Visual Basic and a Database

This morning, I had the chance to play around with another ASP.NET AJAX control:  the CascadingDropDown control.  Like the name implies, it's a nifty little AJAX-enabled control that dynamically enables and populates a DropDownList based on the value selected in a previous list.  Kinda like the coding version of those old skool "Choose Your Own Adventure" books.

You've seen this kind of thing in action plenty of times, I'm sure.  Remember the last time you filled out a registration form that had you pick a country, then you suddenly had a "State/Province" list that only included regions within that country?  Well, this is Microsoft's implementation.  Check it out:  http://www.asp.net/AJAX/AjaxControlToolkit/Samples/CascadingDropDown/CascadingDropDown.aspx

As with most of the ASP.NET AJAX stuff, there's also a great "How Do I..." video (http://www.asp.net/learn/ajax-videos/video-77.aspx) from Joe Stagner (http://www.joeon.net).  Thing is, though, that the video uses an XML file for its data source.  Not real helpful for me, seeing that my data was coming from an actual SQL database. 

The sample website has another page that claims to show how to use the CascadingDropDown with a database backend (http://www.asp.net/AJAX/AjaxControlToolkit/Samples/Walkthrough/CCDWithDB.aspx), but I frankly find it wanting.  First of all, it's not really a "tutorial" in the sense that it doesn't really walk you through how to do what I wanted to do step-by-step.  It assumes you know what you're doing to a certain extent, an assumption which we here at Nerdliness.com have proven time and time again can be fatal.

The second problem with it is that the sample uses C#, with no Visual Basic equivalent.  Now, I'm not a real developer.  I don't even play one on TV.  Hell, I'd be lucky to be the dude they bring in to double for the developer-on-TV when they need to adjust the lighting and all that.  Sure, maybe I could be the key grip on the developer show but...  Ok, you get the point.  I'm sticking with VB.NET.

So let's show the newbs how it's done.  We're going to create three DropDownLists and, using the CascadingDropDown control, populate the second list based on what was selected in the first, and the third based on what's selected in the second.

In this example, we're asking people to pick their favorite NBA player (for you hardcore nerdlings, the "NBA" is the National Basketball Association...  you know, that sport with all the really tall guys throwing a ball into a hoop).  In the first DropDownList, we'll ask them to pick the conference (Eastern or Western).  The second DropDownList will be populated with teams in that conference.  Once they pick a team, they'll be able to choose from a couple of players on that team using the third DropDownList.

The data will be stored in three tables called, amazingly enough, CONFERENCE, TEAM, and PLAYER.  This E-R diagram should give you an idea of what those relationships look like:

 

As for the data in each, well, I'm lazy, so I'm not going to include all the teams and all the players.  Just a representative example to show how it works (see attached for a dump of the tables).

 

CONFERENCE	PLAYER
Eastern	Lamarcus Aldridge
Western	Carlos Boozer
	Kobe Bryant
TEAM	Tim Duncan
Boston Celtics	Kevin Garnett
Detroit Pistons	Pau Gasol
Los Angeles Lakers	Manu Ginobili
Orlando Magic	Dwight Howard
Phoenix Suns	Steve Nash
Portland Trailblazers	Shaq
San Antonio Spurs	Paul Pierce
Utah Jazz	Tayshaun Prince
	Brandon Roy
	Hedo Turkoglu
	Rasheed Wallace
	Deron Williams

(Sure, it's West-heavy.  But is it my fault that's where all the talent is?)

That's all stored in a local SQL Server 2005 Express instance, but that doesn't really matter.  You could be pulling it from SQL 2000, Oracle, MySQL, etc., just as long as you configure the connection in your web service appropriately.  Obviously, I can't really tell you how your connection will look, not knowing anything about your setup.

Finally, a last assumption...  I'm using Visual Studio 2008 for this example, but from what I've seen I don't think there's much difference between it and previous versions (as far as this example is concerned, that is).  If you're using a previous version, make sure you've gone through all the getting-ready-to-use-ASP.NET-AJAX stuff in videos 1 and 2 here:  http://www.asp.net/learn/ajax-videos/

Let's get to it.   First, I have a simple default.aspx file with the aforementioned DropDownLists.  Then, I've added one CascadingDropDown control for each of the DropDownLists in our little group.  Of course, all that's in addition to the stuff we need to make ASP.NET AJAX work at all (registering the AjaxControlTookit assembly at the top, and adding a ScriptManager control).

 
<%@ Page Language="VB" AutoEventWireup="false" EnableEventValidation="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Nerdliness.com CCD Visual Basic Test</title>
</head>
<body>        
<form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<div>
        Conference:  <asp:DropDownList ID="ddlConference" runat="server" />
        Team:  <asp:DropDownList ID="ddlTeam" runat="server" />
        Player:  <asp:DropDownList ID="ddlPlayer" runat="server" />
        <cc1:CascadingDropDown
            id="CascadingDropDown1"
            runat="server"
            category="Conference"
            prompttext="Select a Conference..."
            ServiceMethod="GetConferences"
            ServicePath="nbaplayers.asmx"
            TargetControlId="ddlConference"
        />
        <cc1:CascadingDropDown
            id="CascadingDropDown2"
            runat="server"
            category="Team"
            prompttext="Select a Team..."
            ServiceMethod="GetTeams"
            ServicePath="nbaplayers.asmx"
            TargetControlId="ddlTeam"
            ParentControlID="ddlConference"
        />
        <cc1:CascadingDropDown
            id="CascadingDropDown3"
            runat="server"
            category="Player"
            prompttext="Select a Player..."
            ServiceMethod="GetPlayers"
            ServicePath="nbaplayers.asmx"
            TargetControlId="ddlPlayer"
            ParentControlID="ddlTeam"
        />        
</div>
</form>
</body>
</html>
 

The properties of the CascadingDropDown controls are all pretty straightforward and you can probably get the idea of what each does just by the name (see http://www.asp.net/AJAX/AjaxControlToolkit/Samples/CascadingDropDown/CascadingDropDown.aspx for more detailed info).  Only things that really need to be pointed out here are the TargetControlId property (shows the relationship between our CascadingDropDown controls and their matching DropDownList controls), the ServiceMethod property (the method we'll soon create in our web service), and the ServicePath property (the path to our soon-to-be-created web service).  Also note the ParentControlId on the last two, pointing to the DropDownList above it in our little heirarchy.  Without them, there's no way for our little controls to know how they're related.

One other thing about our .aspx file...  According to the ASP.NET AJAX sample site, you need to turn off EventValidation for this whole thing to work:

Finally, in order for the values to be submitted, EventValidation needs to be disabled for the page. EventValidation ensures that the values in each control match the values that were present when the page was rendered, but since these drop downs are populating on the client side, this is never true. Were hoping to find a way to resolve this issue but please ensure that you understand the potential risks of this and validate the data appropriately in your post back when using this control.

You'll notice I did just that in the <%@ Page %> directive (EnableEventValidation="false"). 

This whole file is really just our presentation layer.  Nothing really interesting happening here.  The good stuff goes down inside our web service.  It's also where the ASP.NET AJAX sample page gets tricky.

I mentioned before that the sample page (http://www.asp.net/AJAX/AjaxControlToolkit/Samples/Walkthrough/CCDWithDB.aspx) makes a couple of assumptions I didn't like.  First, they assume you're using C# and don't include a VB snippet.  Bad form, I say.  Worse, though, is the fact that they make the assumption that you've already created methods for pulling the data from your database, and they use arbitrary names for methods that don't usually exist through their example:

So imagine you've got a database with tables for the Make (manufacturer), Model, and Color information, and you're accessing that database through a DataSet to which you've added methods for getting each set of values.

But if you're a newb, or a strict VB dev guy (sure, maybe that's redundant), you might have a hard time making heads or tails out of their code.  Trying typing in Cars.ColorDataTable colorTable = adapter.GetColorsForModel(modelId); and see what IntelliSense does.  Trust me, you'll have lots of colorful squiggly lines and errors in your future.

So here's how we roll at Nerdliness.com.  In our web service, we're going to use a SqlDataReader to pull the info we want from the previously defined CONFERENCE, TEAM, and PLAYER tables instead of using a DataSet with methods defined behind the scenes.  Frankly, I think this is a bit more straight forward and easier to read, especially for someone new to coding.

And here's the full codebehind file:

nbaplayers.vb

 
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Specialized
Imports AjaxControlToolkit
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://nerdliness.com/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class nbaplayers
    Inherits System.Web.Services.WebService
    <WebMethod()> _
    Public Function GetConferences(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
        Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
        Dim strConfQuery As String = "SELECT * FROM CONFERENCE"
        Dim cmdFetchConf As SqlCommand = New SqlCommand(strConfQuery, sqlConn)
        Dim dtrConf As SqlDataReader
        Dim myConferences As New List(Of CascadingDropDownNameValue)
        sqlConn.Open()
        dtrConf = cmdFetchConf.ExecuteReader
        While dtrConf.Read()
            Dim strConfName As String = dtrConf("conf_name").ToString
            Dim strConfId As String = dtrConf("conf_id").ToString
            myConferences.Add(New CascadingDropDownNameValue(strConfName, strConfId))
        End While
        Return myConferences.ToArray
    End Function
    <WebMethod()> _
    Public Function GetTeams(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
        Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
        Dim strTeamQuery As String = "SELECT * FROM TEAM WHERE conf_id = @confid"
        Dim cmdFetchTeam As SqlCommand = New SqlCommand(strTeamQuery, sqlConn)
        Dim dtrTeam As SqlDataReader
        Dim kvTeam As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
        Dim intConfId As Integer
        If Not kvTeam.ContainsKey("Conference") Or Not Int32.TryParse(kvTeam("Conference"), intConfId) Then
            Return Nothing
        End If
        cmdFetchTeam.Parameters.AddWithValue("@confid", intConfId)
        Dim myTeams As New List(Of CascadingDropDownNameValue)
        sqlConn.Open()
        dtrTeam = cmdFetchTeam.ExecuteReader
        While dtrTeam.Read()
            Dim strTeamName As String = dtrTeam("team_name").ToString
            Dim strTeamId As String = dtrTeam("team_id").ToString
            myTeams.Add(New CascadingDropDownNameValue(strTeamName, strTeamId))
        End While
        Return myTeams.ToArray
    End Function
    <WebMethod()> _
    Public Function GetPlayers(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim strConnection As String = ConfigurationManager.ConnectionStrings("nerdlinessConnection").ConnectionString
        Dim sqlConn As SqlConnection = New SqlConnection(strConnection)
        Dim strPlayerQuery As String = "SELECT * FROM PLAYER WHERE team_id = @teamid"
        Dim cmdFetchPlayer As SqlCommand = New SqlCommand(strPlayerQuery, sqlConn)
        Dim dtrPlayer As SqlDataReader
        Dim kvPlayer As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
        Dim intTeamId As Integer
        If Not kvPlayer.ContainsKey("Team") Or Not Int32.TryParse(kvPlayer("Team"), intTeamId) Then
            Return Nothing
        End If
        cmdFetchPlayer.Parameters.AddWithValue("@teamid", intTeamId)
        Dim myPlayers As New List(Of CascadingDropDownNameValue)
        sqlConn.Open()
        dtrPlayer = cmdFetchPlayer.ExecuteReader
        While dtrPlayer.Read()
            Dim strPlayerName As String = dtrPlayer("player_name").ToString
            Dim strPlayerId As String = dtrPlayer("player_id").ToString
            myPlayers.Add(New CascadingDropDownNameValue(strPlayerName, strPlayerId))
        End While
        Return myPlayers.ToArray
    End Function
End Class
 

Ok, so first thing to note are the different namespaces we imported at the top of the file.  Make sure you do the same.

Second, notice that we've uncommented the "<System.Web.Script.Services.ScriptService()> _" line.  In Visual Studio 2008, that line's already sitting in the new web service when you first add it to your project, but in previous versions you have to add it yourself. 

Now the meat of the web service comes in the form of our three WebMethods:  GetConferences, GetTeams, and GetPlayers.  You might recall those names from the properties of our CascadingDropDown controls in our default.aspx file.  These are the methods we're using to gather the data from our tables and send it back to our controls. 

The first method, GetConferences, is a little simpler than the other two.  Since we're populating that right away and without caring what other DropDownLists contain, we don't need a lot of the logic.

Most of the logic in that method is dedicated to connecting to our database, creating our DataReader, and pulling data.  We loop through the results returned by our "SELECT * FROM CONFERENCE" query and, for each line returned, add a line to the myConferences collection.  We then convert myConferences to an array and send that result back to our CascadingDropDown control.

The only non-standard issue stuff here involves that CascadingDropDownNameValue bit.  Like the sample site says (typo and all):

Note that the values are returned as an array of CascadaingDropDownNameValue structures. This structure also includes an isDefaultValue boolean field that allows the specification of which value should be selected when the list is first displayed.

So we create a list of CascadingDropDownNameValue pairs in our WHILE loop, then pass that list back to the control.  Control takes it, populates our list, then feeds that information into the web method used by the next DropDownList in our chain whenever you select a value.  Pretty easy.

The other two methods are nearly identical, save for the fact that one takes CONFERENCE input and gives TEAM output, and the other takes TEAM input and gives PLAYER output.  Let's just look at the GetTeams method, then.

You'll notice that a good portion of this is the same as the first method.  That is, we create a SqlDataReader and populate it based on our parameterized "SELECT * FROM TEAM WHERE conf_id = @confid" query.  That @confid parameter will be supplied by the previous DropDownList.  So if someone selects "Eastern" from the first list, we only want TEAMS that are in the Eastern conference, right?

So what's the difference between this method and the previous?  This bit here:

 Dim kvTeam As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
    Dim intConfId As Integer
    If Not kvTeam.ContainsKey("Conference") Or Not Int32.TryParse(kvTeam("Conference"), intConfId) Then
        Return Nothing
    End If
 

Like the sample site says:

The knownCategoryValues parameter will return a string containing the currently selected category values, as well as the category to retrieve values for.

and...

The CascadingDropDown class has a helper method for unpacking the category values...  This method will return a StringDictionary containing the name/value pairs of the currently selected values.

 
In English?  We're going to look at knownCategoryValues, make sure it contains a key called Conference, and pull out of it the conf_id associated with the selected item.  We then use that conf_id finish dynamically creating our SQL SELECT query:

 cmdFetchTeam.Parameters.AddWithValue("@confid", intConfId)

Execute that query and feed it into our DataReader, loop through the results, add the appropriate info into another list of CascadingDropDownNameValues, and send that back to the CascadingDropDown control to enable and populate our ddlTeam DropDownList.

And that's pretty much it.  Like I mentioned, the second and third methods are pretty much identical, so adding a fourth or fiftieth DropDownList to the chain is a snap.

Hope this clears up some things for any other poor saps out there looking to populate their CascadingDropDowns with a database backend and using Visual Basic.