Creating an AJAX CascadingDropDown extender control using a database

A very cool control in the AJAX Control Toolkit is the CascadingDropDown extender. Basically, this control will allow you to have linked dropdowns. For instance, maybe you want a state dropdown to link to a county dropdown and subsequently to a city dropdown.

To accomplish this you need three things:

1. An asp.net dropdown control.
2. A web service that connects to an XML file or database for the dropdown data.
3. The CascadingDropDown extender control linked to the dropdown to extend.

For our example, we'll be using newspaper publications that link to newspaper products. So when a user selects a publication the second dropdown will display all of the products available for that publication.

Let's start with the code on the .aspx page.

1. First, let's set the EnableEventValidation="false" in the page directive at the top of the .aspx page.
Here is explanation why you need to turn it off ?
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.

2. Add your asp.net dropdown controls.
Publication dropdown: <asp:DropDownList ID="ddlPublication" runat="server"></asp:DropDownList>
Product dropdown: <asp:DropDownList ID="ddlProduct" runat="server"></asp:DropDownList>

Nothing special here. Just regular old dropdowns.

3. Add the CascadingDropDown extenders.
Publication extender: <cc1:CascadingDropDown ID="cddPublication" runat="server" Category="pub" TargetControlID="ddlPublication" PromptText="[Select Pub]" LoadingText="Loading Pubs..." ServicePath="pubinfo.asmx" ServiceMethod="GetPublications"></cc1:CascadingDropDown>
Product extender: <cc1:CascadingDropDown ID="cddProduct" runat="server" Category="product" ParentControlID="ddlPublication" TargetControlID="ddlProduct" PromptText="[Select Product]" LoadingText="Loading Products..." ServicePath="pubinfo.asmx" ServiceMethod="GetProducts"></cc1:CascadingDropDown>

Next, lets create the web service.

1. In Visual Studio, right click on your project and select "Add New Item".
2. Select "Web Service" and give your web service a name.
3. Now that we have a web service file, let's set up the service methods. Your file should look similar to the code below.

<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<System.Web.Script.Services.ScriptService()> _
Public Class pubinfo

     Inherits System.Web.Services.WebService

     'Set up the database connection string. This should come from the connections strings in your web.config file
     Public condatabase1String As String = System.Configuration.ConfigurationManager.ConnectionStrings("database1ConnectionString").ToString()

'GET PUBLICATIONS METHOD

<WebMethod()> _
Public Function GetPublications(ByVal knownCategoryValues As String, ByVal category As String) As AjaxControlToolkit.CascadingDropDownNameValue()

     'Create a connection to the sql database using the condatabase1String
     Dim sqlConn As New SqlConnection(condatabase1String)

     'Open the connection
     sqlConn.Open()

     'Execute the stored procedure
     Dim sqlSelect As New SqlCommand("EXEC up_MySP", sqlConn)

     'Set the command type
     sqlSelect.CommandType = System.Data.CommandType.Text

     'Create a sql adapter
     Dim sqlAdapter As New SqlDataAdapter(sqlSelect)

     'Create a dataset
     Dim myDataset As New DataSet()

     'Fill the dataset with the results from the stored procedure
     sqlAdapter.Fill(myDataset)

     'Close the connection
     sqlConn.Close()

     'Create a new list to hold the different publications
     Dim cascadingValues As New List(Of AjaxControlToolkit.CascadingDropDownNameValue)()

     'Loop through the rows in the dataset/datatable
     For Each dRow As DataRow In myDataset.Tables(0).Rows
          'Get the publication and publication name
          Dim pub_name As String = UCase("(" & dRow("pub").ToString() & ") " & dRow("pub_name").ToString())

          Dim pub As String = dRow("pub").ToString()

          'Add the values to the list
          cascadingValues.Add(New AjaxControlToolkit.CascadingDropDownNameValue(pub_name, pub))
     Next

     'return the list to the drop down
     Return cascadingValues.ToArray()

End Function

'GET PRODUCTS METHOD

<WebMethod()> _
Public Function GetProducts(ByVal knownCategoryValues As String, ByVal category As String) As AjaxControlToolkit.CascadingDropDownNameValue()

     'Get the publications list
     Dim pubValues As StringDictionary = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

     'Create a variable to hold the selected pub
     Dim pub As String

     'Get the selected pub
     pub = Convert.ToString(pubValues("pub"))

     'Create a connection to the sql database using the condatabase1String
     Dim sqlConn As New SqlConnection(condatabase1String)

     'Open the connection
    sqlConn.Open()

     'Execute the stored procedure.
     Dim sqlSelect As New SqlCommand("EXEC up_MySP2" & pub, sqlConn)

     'Set the command type
     sqlSelect.CommandType = System.Data.CommandType.Text

     'Create a sql adapter
     Dim sqlAdapter As New SqlDataAdapter(sqlSelect)

     'Create a dataset
     Dim myDataset As New DataSet()

     'Fill the dataset with the results from the stored procedure
     sqlAdapter.Fill(myDataset)

     'Close the connection
     sqlConn.Close()

     'Create a new list to hold the different publications
     Dim cascadingValues As New List(Of AjaxControlToolkit.CascadingDropDownNameValue)()

     'Loop through the rows in the dataset/datatable
     For Each dRow As DataRow In myDataset.Tables(0).Rows

          'Get the product and product name
          Dim product As String = dRow("prod_code").ToString()

          Dim product_name As String = UCase("(" & dRow("prod_code").ToString() & ") " & Left(dRow("product_name").ToString(), 20))

          'Add the values to the list
          cascadingValues.Add(New AjaxControlToolkit.CascadingDropDownNameValue(product_name, product))
     Next

     'return the list to the drop down
     Return cascadingValues.ToArray()

     End Function
 
End Class