Solved

Dynamically fill a drop down list from data in a database in Visual Studio

Posted on 2009-04-03
6
324 Views
Last Modified: 2013-11-26
I'm trying to dynamically fill 3 drop down lists from data in my database using Visual Studio.

DDL 1 = States
DDL 2 = Cities
DDL 3 = Zip Codes

DDL 1 should get its data from the database.  Then depending on what state is selected in DDL 1, DDL 2 should populate with the Cities for that State.  Then DDL 3 should populate with the zip codes available for that city.  All drop down lists should get the data from two database tables.

(E.g.  DDL 1 I select FL as the State, then DDL 2 populates with Orlando, Jacksonville, Miami,  I select Orlando as the City.  Then, DDL 3 populates with the zip codes for Orlando.)

How is this accomplished in visual studio?
Thanks for the help
0
Comment
Question by:yagu99
  • 3
  • 2
6 Comments
 
LVL 10

Assisted Solution

by:Espavo
Espavo earned 450 total points
ID: 24065934
The best, and easiest, way to do this is to use the AJAX CascadingDropDown control, with a web-service...
Go here for the info:

http://www.asp.net/AJAX/AjaxControlToolkit/Samples/CascadingDropDown/CascadingDropDown.aspx
 
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 50 total points
ID: 24067213
0
 

Author Comment

by:yagu99
ID: 24070206
It has to be completed in Visual Studio 2008 (vb code).  

Here are the two tables with their attributes

Categories Table
CategoryID  int  identity PK
CategoryName nvarchar(100)
Description nvarchar(100)
ParentCategoryID int FK to dbo.Categories.CategoryID   Allows Nulls
ParentCodeID  int   FK to dbo.Codes.CodesID  Allows Nulls


Codes Table
CodesID int Identity PK
CategoryID  int FK to dbo.Categories.CategoryID
CodeName nvarchar(100)
Description  nvarchar(100)



I have the folllowing data in the Categories table:
CategoryID = 1
CategoryName = States
Description = US States
ParentCategoryID = NULL
ParentCodeID = NULL

CategoryID = 2
CategoryName = Cities
Description = US Cities
ParentCategoryID = 1
ParentCodeID = NULL

CategoryID = 3
CategoryName = Zip Codes
Description = US Zips
ParentCategoryID = 2
ParentCodeID = NULL


I have the following information in the Codes Table:
CodeID = 1
CategoryID = 1
CodeName = Florida
Description = State of Florida

CodeID = 2
CategoryID = 1
CodeName = Georgia
Description = State of Georgia

CodeID = 3
CategoryID = 2
CodeName = Orlando
Description = City of Florida

CodeID = 4
CategoryID = 2
CodeName = Miami
Description = City of Florida

CodeID = 5
CategoryID = 2
CodeName = Atlanta
Description = City of  Georgia

CodeID = 6
CategoryID = 2
CodeName = Cleveland
Description = Cityof Georiga

CodeID = 7
CategoryID = 3
CodeName = 11111
Description = Zip of Orlando

CodeID = 8
CategoryID = 3
CodeName = 11112
Description = Zip of Orlando

CodeID = 9
CategoryID = 3
CodeName = 21111
Description = Zip of Miami

CodeID = 10
CategoryID = 3
CodeName = 21112
Description = Zip of Miami

CodeID = 11
CategoryID = 3
CodeName = 31111
Description = Zip of Atlanta

CodeID = 12
CategoryID = 3
CodeName = 32211
Description = Zip of Atlanta

CodeID = 13
CategoryID = 3
CodeName = 41111
Description = Zip of Cleveland

CodeID = 14
CategoryID = 3
CodeName = 41112
Description = Zip of Cleveland

So based on this idea:

The 1st DDL should be the States
The 2nd DDL should be the cities based on the State Selected
The 3rd DDL should be the zips for the City Selected

The DDL's must read the database & not be statically entered into the VB web form.

I also have two stored procedures where it selects all of the categories & the other selects all of the Codes.  Not really for sure what to do with the ParentCodeID field in the dbo.Categories Table.





0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 10

Expert Comment

by:Espavo
ID: 24070264
As you were asked by emoreau, is this a Web App or Windows App?

If it's a Web App then I can post exactly how I've done it... (Using a Web Service and DataSets, using SQL 2005)
0
 

Author Comment

by:yagu99
ID: 24071541
It is a web app
0
 
LVL 10

Accepted Solution

by:
Espavo earned 450 total points
ID: 24074966
Okay, so here we go...
Here are the DropDownLists, with their Extenders...
And the WebService that "runs" the lot...
I've also included a DB diagram so that you can see how I've created and built my DB tables...
You'll notice that I'm using Strongly Typed Datasets for pulling the data from the DB...
Here's the Query from StateAdaptor.GetStates(sID):
Dim StateAdaptor As New SQLDataSetTableAdapters.PropAreaStateTableAdapter
Dim States As SQLDataSet.PropAreaStateDataTable = StateAdaptor.GetStates(sID)
SELECT DISTINCT AreaState.StateID, AreaState.StateName
FROM            Properties LEFT OUTER JOIN
                         AreaState ON Properties.StateID = AreaState.StateID
WHERE        (Properties.CountryID = @CountryID)
ORDER BY AreaState.StateName
Regards
Gayo

<asp:DropDownList ID="mCountryDropDownList" runat="server" style="width: 155px" CssClass="Form">
                </asp:DropDownList>
                <cc2:CascadingDropDown ID="mCountryDropDownList_CascadingDropDown" 
                    runat="server" Category="Country" LoadingText="Fetching list... please wait..." 
                    PromptText="- Country -" ServiceMethod="GetCountry" 
                    ServicePath="~/AreaMainService.asmx"
                    Enabled="True" TargetControlID="mCountryDropDownList">
                </cc2:CascadingDropDown><br />
                <asp:DropDownList ID="mStateDropDownList" runat="server" style="width: 155px" CssClass="Form">
                </asp:DropDownList>
                <cc2:CascadingDropDown ID="mStateDropDownList_CascadingDropDown" runat="server" 
                    Category="State" LoadingText="Fetching list... please wait..." 
                    ParentControlID="mCountryDropDownList" EmptyText="No Records"
                    PromptText="- All States/Provinces -" ServiceMethod="GetStates" 
                    ServicePath="~/AreaMainService.asmx" TargetControlID="mStateDropDownList">
                </cc2:CascadingDropDown><br />
                <asp:DropDownList ID="mCityDropDownList" runat="server" style="width: 155px" CssClass="Form">
                </asp:DropDownList>
                <cc2:CascadingDropDown ID="mCityDropDownList_CascadingDropDown" runat="server" 
                    Category="City" LoadingText="Fetching list... please wait..." 
                    ParentControlID="mStateDropDownList"  EmptyText="No Records"
                    PromptText="- All Cities/Towns -" ServiceMethod="GetCities" 
                    ServicePath="~/AreaMainService.asmx" TargetControlID="mCityDropDownList">
                </cc2:CascadingDropDown><br />
                <asp:DropDownList ID="mSuburbDropDownList" runat="server" style="width: 155px" CssClass="Form">
                </asp:DropDownList>
                <cc2:CascadingDropDown ID="mSuburbDropDownList_CascadingDropDown" runat="server" 
                    Category="Suburb" LoadingText="Fetching list... please wait..." 
                    ParentControlID="mCityDropDownList" EmptyText="No Records"
                    PromptText="- All Suburbs -" ServiceMethod="GetSuburbs" 
                    ServicePath="~/AreaMainService.asmx" TargetControlID="mSuburbDropDownList">
                </cc2:CascadingDropDown>
 
AreaMainService.asmx
====================
<%@ WebService Language="VB" CodeBehind="~/App_Code/AreaMainService.vb" Class="AreaMainService" %>
 
AreaMainService.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 AreaMainService
    Inherits System.Web.Services.WebService
 
    <WebMethod()> _
 Public Function GetCountry(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        'Article on Caching DD values:
        'http://www.devx.com/dotnet/Article/32782/0/page/3
 
        Dim DDValues As New List(Of CascadingDropDownNameValue)
 
        Dim CountryAdaptor As New SQLDataSetTableAdapters.CountriesTableAdapter
        Dim Countries As SQLDataSet.CountriesDataTable = CountryAdaptor.GetData()
        Dim Country As SQLDataSet.CountriesRow
        For Each Country In Countries
            DDValues.Add(New CascadingDropDownNameValue(Country.countryName, Country.countryId.ToString))
        Next
 
        Return DDValues.ToArray
    End Function
 
 
    <WebMethod()> _
    Public Function GetStates(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim DDValues As New List(Of CascadingDropDownNameValue)
        Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
        Dim sID As Integer
        If Not kv.ContainsKey("Country") Or Not Int32.TryParse(kv("Country"), sID) Then
            Return Nothing
        End If
 
        Dim StateAdaptor As New SQLDataSetTableAdapters.PropAreaStateTableAdapter
        Dim States As SQLDataSet.PropAreaStateDataTable = StateAdaptor.GetStates(sID)
        Dim State As SQLDataSet.PropAreaStateRow
        For Each State In States
            Try : DDValues.Add(New CascadingDropDownNameValue(State.StateName, State.StateID.ToString)) : Catch : End Try
        Next
 
        Return DDValues.ToArray
    End Function
 
    <WebMethod()> _
 Public Function GetCities(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim DDValues As New List(Of CascadingDropDownNameValue)
        Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
        Dim sID As Integer
        If Not kv.ContainsKey("State") Or Not Int32.TryParse(kv("State"), sID) Then
            Return Nothing
        End If
 
        Dim CityAdaptor As New SQLDataSetTableAdapters.PropAreaCityTableAdapter
        Dim Cities As SQLDataSet.PropAreaCityDataTable = CityAdaptor.GetCities(sID)
        Dim City As SQLDataSet.PropAreaCityRow
        For Each City In Cities
            Try : DDValues.Add(New CascadingDropDownNameValue(City.CityName, City.CityID.ToString)) : Catch : End Try
        Next
 
        Return DDValues.ToArray
    End Function
 
    <WebMethod()> _
Public Function GetSuburbs(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
        Dim DDValues As New List(Of CascadingDropDownNameValue)
        Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
        Dim sID As Integer
        If Not kv.ContainsKey("City") Or Not Int32.TryParse(kv("City"), sID) Then
            Return Nothing
        End If
 
        Dim SuburbAdaptor As New SQLDataSetTableAdapters.PropAreaSuburbTableAdapter
        Dim Suburbs As SQLDataSet.PropAreaSuburbDataTable = SuburbAdaptor.GetSuburbs(sID)
        Dim Suburb As SQLDataSet.PropAreaSuburbRow
        For Each Suburb In Suburbs
            Try : DDValues.Add(New CascadingDropDownNameValue(Suburb.SubName, Suburb.SubID.ToString)) : Catch : End Try
        Next
 
        Return DDValues.ToArray
    End Function
 
End Class

Open in new window

CascadingDDListDBDiagram.jpg
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question