ASP.NET - how to Display using Dropdownlist state of particular country?

Asked By shazi on 02-Jul-11 12:59 PM
hi

i am developing one page in that three dropdownlist.. like country state and city


i want to make filterring like selection of the particular country its related state in another dropdownlist
and after that selection of the particular state its related city..

Should i create seperate table for this ?

How can i do this ?

Hope vill cum wid some idea

Please let me know
Peter Bromberg replied to shazi on 02-Jul-11 02:06 PM
You need three separate tables.

When the user selects a country, you would bring back the rows of the states in that country.

When the user selects a state, you would bring back the rows of all cities matching that country and state.
shazi replied to Peter Bromberg on 02-Jul-11 02:11 PM
Thank you for ur advice
Kirtan Patel replied to shazi on 02-Jul-11 04:00 PM
Create 3 Tables like this 

Country 
---------
CountryID
CountryName
CountryCode

State
-------
StateID
CountryID
StateName

Cities
-------
StateID
CityName


Riley K replied to shazi on 02-Jul-11 10:01 PM
Create Tables like below with some Id's

 COUNTRY Table

CountryName    CId

USA     1
Germany              2
Japan                   3

State Table

StateName  SID   CId

NewYork      1       1
California      2       1

CITY Table

CityName     CtyId   SId

Alameda       1          1
SFo                2          1

If you maintain Ids then you can bind those appropriate ID's to DropDownList Value property  

Reena Jain replied to shazi on 02-Jul-11 11:14 PM
hi,

By the above suggestion you can create tables in database and like this you can implement it in code. You can actually get the data as required in each seleectedindex changed event of the first dropdownlist to fill the second one. here is the code for you


DataSet DS = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlCommand Cmd = new SqlCommand("Select * From tblcountry", Conn);
SqlDataAdapter DA = new SqlDataAdapter(Cmd);
DA.Fill(DS, "country");
DropDownList1.DataSource = DS.Tables["country"];
DropDownList1.DataTextField = "countryname";
DropDownList1.DataValueField = "countryid";
DropDownList1.DataBind();
DropDownList1.Items.Insert(0, "Select");
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
if (DropDownList1.SelectedValue != "Select")
{
SqlCommand Cmd1 = new SqlCommand("Select * From tblstate Where countryid=" + DropDownList1.SelectedValue + "", Conn);
SqlDataAdapter DA1 = new SqlDataAdapter(Cmd1);
DA1.Fill(DS, "state");
DropDownList2.DataSource = DS.Tables["state"];
DropDownList2.DataTextField = "statename";
DropDownList2.DataValueField = "stateid";
DropDownList2.DataBind();
DropDownList2.Items.Insert(0, "Select");
}
}
protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
{
if (DropDownList2.SelectedValue != "Select")
{
SqlCommand Cmd1 = new SqlCommand("Select * From tblcity Where stateid=" + DropDownList2.SelectedValue + "", Conn);
SqlDataAdapter DA1 = new SqlDataAdapter(Cmd1);
DA1.Fill(DS, "city");
DropDownList3.DataSource = DS.Tables["city"];
DropDownList3.DataTextField = "cityname";
DropDownList3.DataValueField = "cityid";
DropDownList3.DataBind();
DropDownList3.Items.Insert(0, "Select");
}
}

hope this will help you
shazi replied to Kirtan Patel on 03-Jul-11 01:49 AM
Thank you
shazi replied to Riley K on 03-Jul-11 02:05 AM
thank you
shazi replied to Reena Jain on 03-Jul-11 02:53 AM
Hai,


   i didnt get .here i can only display country name . After selecting country name if i select state its showing blank and ihave already stored some values which is having same country id to refer .

Here is code which i did.

example.css
......................
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class dropdown : System.Web.UI.Page
{
    DataSet DS = new DataSet();
    protected void Page_Load(object sender, EventArgs e)
    {
    if (!IsPostBack)
    {
      SqlConnection Conn = new SqlConnection("");
     
      SqlCommand Cmd = new SqlCommand("Select * From country_details", Conn);
      SqlDataAdapter DA = new SqlDataAdapter(Cmd);
      DA.Fill(DS, "country");
      DropDownList1.DataSource = DS.Tables["country"];
      DropDownList1.DataTextField = "country_name";
      DropDownList1.DataValueField = "country_id";
      DropDownList1.DataBind();
      DropDownList1.Items.Insert(0, "Select");
    }
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
    if (DropDownList1.SelectedValue != "Select")
    {
      SqlConnection Conn = new SqlConnection(" ");

      SqlCommand Cmd1 = new SqlCommand("Select * From state_details Where country_name='" + DropDownList1.SelectedValue + "' ", Conn);
      SqlDataAdapter DA1 = new SqlDataAdapter(Cmd1);
      DA1.Fill(DS, "state");
      DropDownList2.DataSource = DS.Tables["state"];
      DropDownList2.DataTextField = "state_name";
      DropDownList2.DataValueField = "state_id";
      DropDownList2.DataBind();
      DropDownList2.Items.Insert(0, "Select");
    }
    
    /*SqlConnection cn = new SqlConnection("");
      
    cn.Open();
    SqlCommand cmd = new SqlCommand("select country_id, country_name from country_details", cn);



    cmd.Connection = cn;



    SqlDataAdapter da = new SqlDataAdapter(cmd);



    da.Fill(ds);

    cmbcountry.DataSource = ds;

    cmbcountry.DataTextField =

    "CountryName";



    cmbcountry.DataValueField =

    "CountryId";



    cmbcountry.DataBind();

    cn.Close();*/
    }
    protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
    {
    if (DropDownList2.SelectedValue != "Select")
{
    SqlConnection Conn = new SqlConnection(" ");
     
SqlCommand Cmd1 = new SqlCommand("Select * From city_details Where stateid=" + DropDownList2.SelectedValue + "", Conn);
SqlDataAdapter DA1 = new SqlDataAdapter(Cmd1);
DA1.Fill(DS, "city");
DropDownList3.DataSource = DS.Tables["city"];
DropDownList3.DataTextField = "city_name";
DropDownList3.DataValueField = "city_id";
DropDownList3.DataBind();
DropDownList3.Items.Insert(0, "Select");
}
}
     
    
    protected void DropDownList3_SelectedIndexChanged(object sender, EventArgs e)
    {


    }
    }


example.aspx
.........................
 <asp:DropDownList ID="DropDownList1" runat="server"
    DataValueField="country_id" DataTextField="country_name"
    onselectedindexchanged="DropDownList1_SelectedIndexChanged">
    
    </asp:DropDownList>
    <br />
    <br />
   <asp:DropDownList ID="DropDownList2" runat="server" DataValueField="state_id" DataTextField="state_name"
    onselectedindexchanged="DropDownList2_SelectedIndexChanged" >
    <asp:ListItem Selected="True" Text="-- Select State --" Value=""></asp:ListItem>
       
    </asp:DropDownList>
    <br />
    <br />
   <asp:DropDownList ID="DropDownList3" runat="server" DataValueField="city_id" DataTextField="city_name"
    onselectedindexchanged="DropDownList3_SelectedIndexChanged">
   <asp:ListItem Selected="True" Text="-- Select City --" Value=""></asp:ListItem>
      
    </asp:DropDownList>

please let me know where i gone wrong
Reena Jain replied to shazi on 04-Jul-11 02:13 AM
Hi,

there is a selected value having country id not country name as well either bind "select" from design page or from code page. so change your code like this

SqlConnection Conn = new SqlConnection(" ");
 
SqlCommand Cmd1 = new SqlCommand("Select * From state_details Where country_id='" + DropDownList1.SelectedValue + "' ", Conn);
SqlDataAdapter DA1 = new SqlDataAdapter(Cmd1);
DA1.Fill(DS, "state");
DropDownList2.DataSource = DS.Tables["state"];
DropDownList2.DataTextField = "state_name";
DropDownList2.DataValueField = "state_id";
DropDownList2.DataBind();

try above and let me know
Ravi S replied to shazi on 04-Jul-11 02:21 AM
HI

use this

ddlState.Attributes.Add("onchange","set_city_state(this,ddlCity.ClientID);">


refer the link for details

http://www.eggheadcafe.com/searchform.aspx?search=dropdownlist+work+flow+selected+country+state+loaded+help+bind+webservices
http://www.codeproject.com/KB/aspnet/CitiesDropDownList.aspx
Jitendra Faye replied to shazi on 04-Jul-11 04:47 AM

If you want to display data based on DropDownList selection, means if you want to implement cascading then you have to implement code in  SelectedIndexChanged() Event of DropDownList.

before that set AutoPostBack Property of All DropDownList ti true.

I m giving sample code for that-

Here i m doing cascading for country, state and city.

1. here i m filling DropDownList1 with county

//for binding DropDownList1 with country

private void DataGrid_Load(object sender, EventArgs e)
{
 getdata();
}

//function for getting country
private void getdata()
{
SqlConnection cn = new SqlConnection("constring");
cn.Open();
string strQuery = "Select conid,conname from countrytab";
SqlDataAdapter da = new SqlDataAdapter(strQuery, cn);
DataSet ds = new DataSet();
da.Fill(ds);
DropDownList1.DisplayMember = "conid";
DropDownList1.ValueMember = "conname";
DropDownList1.DataSource = ds.Tables[0];
}

2. here i m filling DropDownList2 with state, based on selection on DropDownList1

 protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("constring");
cn.Open();
string strQuery = "Select stateid,statename from statetab where conid='" + DropDownList1.SelectedValue .ToString() + "'";
SqlDataAdapter da = new SqlDataAdapter(strQuery, cn);
DataSet ds = new DataSet();
da.Fill(ds);
DropDownList2 .DisplayMember = "stateid";
DropDownList2.ValueMember = "statename";
DropDownList2.DataSource = ds.Tables[0];
}


3. here i m filling DropDownList3 with city, based on selection on DropDownList2

 protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("constring");
cn.Open();
string strQuery = "Select cityid,cityname from citytab where conid='" + DropDownList1.SelectedValue .ToString() + "' and stateid='" + DropDownList2.SelectedValue .ToString() + "' ";
SqlDataAdapter da = new SqlDataAdapter(strQuery, cn);
DataSet ds = new DataSet();
da.Fill(ds);
DropDownList3.DisplayMember = "cityid";
DropDownList3.ValueMember = "cityname";
DropDownList3.DataSource = ds.Tables[0];
}

USE THIS CODE AND LET ME KNOW.


Devil Scorpio replied to shazi on 05-Jul-11 06:01 AM
Hi Shazi,

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 http://www.geodatasource.com/world-major-cities.html from http://www.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.


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

http://www.codeproject.com/KB/aspnet/CitiesDropDownList.aspx?display=Mobile

Hitesh Arora replied to Peter Bromberg on 04-Apr-12 08:04 AM
there are so many countery in which states are not there so what to do for those type of counteries