How to create Geographical location in 3 different levels?

Question

My scenario is something like this:

There are 3 dropdownlists as Country,State and City. If i choose a particular country from the 1st list(Country) ,only the states corresponding to the particular country must be listed in the State_list. similarly for the next two levels. The important thing is that i have to maintain these data only in a single table. That table has 3 fields as Geographic_Location_ID, Geographic_Name, Geographic_Level.

The level determines which Geographic_Name should be shown in the List.

Can anyone pls help?

Answer 1

Your table makes no sense. Without being self-referential, how could you know, which states bleong to which country?

Answer 2

The table sounds insufficient as Stefan stated.  As an example, tell me how you can find from your table, the State that Tokyo belongs?

 

You basically need to query unique countries from that field of the table, then unique states for the selected country, and lastly unique cities from the selected state and country.

--
Mike

Answer 3

The table has one more field to find which state belongs to which country.

this is the table. From the Parent_ID we can find which belongs to which country/state.

Geographic_Location_ID	Geographic_Name	Geographic_Level	Parent_ID
100	India	1
101	Maharastra	2	100
102	Antheri	3	101

If i choose India as the Country ,then the corresponding Geogrpahic_Name with the parent_ID of India should be returned to the list. 

Answer 4

Hello, As i understand the question. following query might help

Select  Geographic_Name from SQLTable where Parent_ID = (Select Geographic_Location_ID from 
SQLTable where Geographic_Name = 'InputParameter')

---

please Mark as the Answer, If this answers your question. If this post is helpful, please vote as helpful.

Answer 5

To get all the countries:

 

    SELECT DISTINCT(Geographic_Name) FROM MyTable WHERE Geographic_Level = 1

 

To get all states in a country, you need the ID of the country, so for India:

 

    SELECT DISTINCT(Geographic_Name) FROM MyTable WHERE Geographic_Level = 2 and Parent_ID=100

 

To get all the cities in a state, you need the ID of the state, so for Maharastra:

 

    SELECT DISTINCT(Geographic_Name) FROM MyTable WHERE Geographic_Level = 3 and Parent_ID = 101

 

These queries can be used to fill the dropdown lists. When the country is changed, call the second and third fill queries, when the state is changed, call the third query.

 

--
Mike

Answer 6

The query is correct.

SELECT DISTINCT(Geographic_Name) FROM MyTable WHERE Geographic_Level = 2 and Parent_ID=100

But I want to get the ID of the selected Country from the dropdownlist in runtime. how to get it?

Answer 7

Well, data binding. Either load the table into a DataTable or use the Entity Framework.

Answer 8

Sorry i could not understand.Can you explain more clearly

Answer 9

E.g.

• Data Binding Overview
• Understanding Simple Data Binding
• A Detailed Data Binding Tutorial
• Data Binding in .NET / C# Windows Forms

Answer 10

Hi,thanks all for the valuable suggestions and replies. Here is how i resolved my issue:

    SqlConnection Conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\ADMIN\\Documents\\sample.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
    DataSet DS = new DataSet();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlCommand Cmd = new SqlCommand("Select * From Geogrpahic_Location where Level='1' ", Conn);
            SqlDataAdapter DA = new SqlDataAdapter(Cmd);
            DA.Fill(DS, "Geogrpahic_Location");
            CountryIDDropdownList.DataSource = DS.Tables["Geogrpahic_Location"];
            CountryIDDropdownList.DataTextField = "Name";
            CountryIDDropdownList.DataValueField = "Id";
            CountryIDDropdownList.DataBind();
            CountryIDDropdownList.Items.Insert(0, "Select");
            StateDropdownList.Items.Insert(0, "Select");
            CityDropdownList.Items.Insert(0, "Select");
        }
    }
    protected void Next_Click(object sender, EventArgs e)
    {
        Response.Redirect("AddNewPeople3.aspx");
    }
    protected void Back_Click(object sender, EventArgs e)
    {
        Response.Redirect("AddNewPeople.aspx");
    }
    protected void CountryIDDropdownList_SelectedIndexChanged(object sender, EventArgs e)
    {

        if (CountryIDDropdownList.SelectedValue != "Select")
        {
            SqlCommand Cmd1 = new SqlCommand("Select * From Geogrpahic_Location Where Parent_Id=" + CountryIDDropdownList.SelectedValue + "", Conn);
            SqlDataAdapter DA1 = new SqlDataAdapter(Cmd1);
            DA1.Fill(DS, "Geogrpahic_Location");
            StateDropdownList.DataSource = DS.Tables["Geogrpahic_Location"];
            StateDropdownList.DataTextField = "Name";
            StateDropdownList.DataValueField = "Id";
            StateDropdownList.DataBind();
            StateDropdownList.Items.Insert(0, "Select");
        }
    }
    protected void StateDropdownList_SelectedIndexChanged(object sender, EventArgs e)
    {

        if (StateDropdownList.SelectedValue != "Select")
        {
            SqlCommand Cmd2 = new SqlCommand("Select * From Geogrpahic_Location Where Parent_Id=" + StateDropdownList.SelectedValue + "", Conn);
            SqlDataAdapter DA2 = new SqlDataAdapter(Cmd2);
            DA2.Fill(DS, "Geogrpahic_Location");
            CityDropdownList.DataSource = DS.Tables["Geogrpahic_Location"];
            CityDropdownList.DataTextField = "Name";
            CityDropdownList.DataBind();
            CityDropdownList.Items.Insert(0, "Select");
        }
    }

Answer 11

Hi jessi carmel,

  Welcome to MSDN Forum Support.

  I am glad to hear that you have solved your problem, and thank you for sharing your solution in this.

  Sincerely,

  Jason Wang

---

Jason Wang [MSFT]
MSDN Community Support | Feedback to us