Solved

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

Posted on 2009-04-03
6
320 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 69

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now