?
Solved

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

Posted on 2009-04-03
6
Medium Priority
?
331 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 1800 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 200 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
Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

 
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 1800 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 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