Simple Way to Implement Country/State/City Dropdown List in ASP.NET (VB)

Hexa How

Rate me:

3.52/5 (13 votes)

17 Jul 2009GPL34 min read

168.8K

9.8K

Simple way to implement country/state/city drop down list in ASP.NET (VB)

Download source - 24.08 KB

Introduction

This article aims to show how to create 3 dropdownlists to display countries, states and cities. When you select a country in the countries dropdownlist, the states dropdownlist will fetch the related states for that country. Then when you select a state, the cities dropdownlist will fetch the related cities for that state.

Using the Code

This article assumes that you are familiar with basic HTML as well as basic ASP.NET code. The code in this article is actually custom made to use the World Major Cities data from GeoDataSource.com. That said, it is still quite straightforward to modify the code to work with data from other sources. I have included a sample of the data file in the text file GEODATASOURCE-WORLD-MAJOR-CITIES-SAMPLES.TXT and to make life easier for you, I have also included 2 SQL scripts for importing into either a MySQL database or a Microsoft SQL Server database. For MySQL, the script filename is called countrystatecity-mysql-sample.sql. For Microsoft SQL Server, the file is called countrystatecity-mssql-sample.sql.

In this example, the 2 important files are index.aspx which contains the HTML code and the index.aspx.vb which contains the .NET code which does all the real work. The code is meant to work in Microsoft Visual Studio 2003.

Let's start off with the index.aspx file which is displayed below:

It is a fairly simple HTML file containing 3 ASP.NET dropdownlists. Note that both the countries and states dropdownlists have AutoPostBack="True". This is because the moment you select a value in either of the dropdownlists, the page will reload to get new values for the next dropdownlist.

XML

<%@ Page Language="vb" AutoEventWireup="false" 
    Codebehind="index.aspx.vb" Inherits="CitiesDropDownListASPDotNetVBSample.index"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML>
	<HEAD>
		<title>ASP.NET VB Sample</title>
		<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	</HEAD>
	<body>
		<form id="Form1" method="post" runat="server">
			<asp:DropDownList runat="server" AutoPostBack="True" 
				id="countries" Width="360px" >
			</asp:DropDownList><BR>
			<BR>
			<asp:DropDownList id="states" runat="server" 
				AutoPostBack="True" Width="360px">
			</asp:DropDownList>
			<BR>
			<BR>
			<asp:DropDownList id="cities" runat="server" Width="360px">
			</asp:DropDownList>
		</form>
	</body>
</HTML>

Alright, now for the actual code that does all the work. Below is the code for index.aspx.vb file:

At the top, you will see the variable declaration section, isMSSQL is a boolean variable which you must set to True if you are using a Microsoft SQL Server database. Otherwise, you may leave it as False. The sqlStr variable contains the SQL statement for getting the data for the countries dropdownlist. The other two variables contain the SQL statements to get the data for the states and cities dropdownlists respectively.

VB.NET

Private isMSSQL As Boolean = False  'change here to
				' modify the SQL statements for SQL Servers
Private sqlStr As String = "select distinct COUNTRY_CODE, COUNTRY_NAME _
	from countrystatecity where COUNTRY_NAME <> '' _
	and STATE <> '' order by COUNTRY_NAME"
Private sqlStr2 As String = "select distinct STATE from countrystatecity _
	where COUNTRY_CODE = NNN'XXX' and STATE <> '' order by STATE"
Private sqlStr3 As String = "select distinct CITY_NAME from countrystatecity _
	where COUNTRY_CODE = NNN'XXX' and STATE = NNN'YYY' order by CITY_NAME"

You may have noticed the following code which you will have to uncomment based on the type of database you are using. You will also have to put in your username and password to connect to your database. This variable holds the connection string that you will need to connect to your database. Your actual connection string may vary from what you see below depending on your database type as well as the database version.

VB.NET

'sample connection string for MySQL
'connStr = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=countrystatecitydb;_
 	USER=[your username];PASSWORD=[your password];OPTION=3;"

'sample connection string for SQL Server
'connStr = "Driver={SQL Server Native Client 10.0};Server=localhost;_
	Database=countrystatecitydb;Uid=[your username];Pwd=[your password];"

In the Page_Load subroutine, you will see the following code. Basically what the code does is get the SQL statement and then pass it to the getDataSet function which will return a dataset with the database data. If you are using the Microsoft SQL Server database, remember to set the variable isMSSQL to True during the variable declaration. As you can see, here we check that if isMSSQL is True, then we will replace the "NNN" to be "N". The reason for this is that having the "N" is how Microsoft SQL Server casts a value to Unicode encoding. After all 3 dropdownlists have been bound with data, we insert another option into the top of each dropdownlist. This will display by default "Please select a country." for the countries dropdownlist and similarly for the other 2 dropdownlists.

VB.NET

If Not IsPostBack Then
    countries.DataSource = getDataSet(sqlStr)
    countries.DataTextField = "COUNTRY_NAME"
    countries.DataValueField = "COUNTRY_CODE"
    countries.DataBind()

    sqlStr2A = sqlStr2.Replace("XXX", currCountry.Replace("'", "''"))
    sqlStr3A = sqlStr3.Replace("XXX", currCountry.Replace("'", "''"))
    sqlStr3A = sqlStr3A.Replace("YYY", currState.Replace("'", "''"))

    If isMSSQL Then
        sqlStr2A = sqlStr2A.Replace("NNN", "N")
        sqlStr3A = sqlStr3A.Replace("NNN", "N")
    Else
        sqlStr2A = sqlStr2A.Replace("NNN", "")
        sqlStr3A = sqlStr3A.Replace("NNN", "")
    End If

    states.DataSource = getDataSet(sqlStr2A)
    states.DataTextField = "STATE"
    states.DataValueField = "STATE"
    states.DataBind()

    cities.DataSource = getDataSet(sqlStr3A)
    cities.DataTextField = "CITY_NAME"
    cities.DataValueField = "CITY_NAME"
    cities.DataBind()

    countries.Items.Insert(0, New ListItem("Please select a country.", ""))
    states.Items.Insert(0, New ListItem("Please select a state.", ""))
    cities.Items.Insert(0, New ListItem("Please select a city.", ""))
End If

In the countries_SelectedIndexChanged subroutine, you will see what happens when you select a value in the countries dropdownlist. What happens here is that there is a specific country selected now. So the states dropdownlist will have to display the states for that country. By replacing "XXX" in the SQL statement for states, we have now changed the SQL statement to get only states that are specific to the selected country. At this point, cities will still show no data because no specific state has been selected yet. Again, the SQL statement is passed to the getDataSet function to retrieve a dataset with the database data.

VB.NET

Private Sub countries_SelectedIndexChanged(ByVal sender As System.Object, _
	ByVal e As System.EventArgs) Handles countries.SelectedIndexChanged
    currCountry = countries.SelectedValue
    currState = ""

    sqlStr2A = sqlStr2.Replace("XXX", currCountry.Replace("'", "''"))
    sqlStr3A = sqlStr3.Replace("XXX", currCountry.Replace("'", "''"))
    sqlStr3A = sqlStr3A.Replace("YYY", currState.Replace("'", "''"))

    If isMSSQL Then
        sqlStr2A = sqlStr2A.Replace("NNN", "N")
        sqlStr3A = sqlStr3A.Replace("NNN", "N")
    Else
        sqlStr2A = sqlStr2A.Replace("NNN", "")
        sqlStr3A = sqlStr3A.Replace("NNN", "")
    End If

    states.DataSource = getDataSet(sqlStr2A)
    states.DataTextField = "STATE"
    states.DataValueField = "STATE"
    states.DataBind()

    cities.DataSource = getDataSet(sqlStr3A)
    cities.DataTextField = "CITY_NAME"
    cities.DataValueField = "CITY_NAME"
    cities.DataBind()

    states.Items.Insert(0, New ListItem("Please select a state.", ""))
    cities.Items.Insert(0, New ListItem("Please select a city.", ""))
End Sub

The codes in the states_SelectedIndexChanged subroutine will run when you select a specific state. The "XXX" in the SQL statement is replaced with the selected country code and "YYY" replaced with the selected state name. Now the SQL statement will fetch only cities that are specific to the selected country and state. Again, pass the SQL statement to the getDataSet function and it will return the database data in a dataset.

VB.NET

Private Sub states_SelectedIndexChanged(ByVal sender As System.Object, _
	ByVal e As System.EventArgs) Handles states.SelectedIndexChanged
    currCountry = countries.SelectedValue
    currState = states.SelectedValue

    sqlStr3A = sqlStr3.Replace("XXX", currCountry.Replace("'", "''"))
    sqlStr3A = sqlStr3A.Replace("YYY", currState.Replace("'", "''"))

    If isMSSQL Then
        sqlStr3A = sqlStr3A.Replace("NNN", "N")
    Else
        sqlStr3A = sqlStr3A.Replace("NNN", "")
    End If

    cities.DataSource = getDataSet(sqlStr3A)
    cities.DataTextField = "CITY_NAME"
    cities.DataValueField = "CITY_NAME"
    cities.DataBind()

    cities.Items.Insert(0, New ListItem("Please select a city.", ""))
End Sub

Conclusion

I hope this was a very simple and straightforward example of how to display countries, states and cities in 3 dropdownlists.

History

17^th July, 2009: Initial version

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Written By

Hexa How

United States

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Simple Way to Implement Country/State/City Dropdown List in ASP.NET (VB)

Introduction

Using the Code

Conclusion

History

License

Comments and Discussions