Solved

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

Posted on 2009-04-03
6
313 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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:yagu99
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 10

Expert Comment

by:Espavo
Comment Utility
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
Comment Utility
It is a web app
0
 
LVL 10

Accepted Solution

by:
Espavo earned 450 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

13 Experts available now in Live!

Get 1:1 Help Now