Solved

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

Posted on 2009-04-03
6
329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

732 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