Home » C# ProgrammingRSS

Filling state dropdownlist according to country dropdownlist from database

Hello,

I've been looking for an answer to this problem of mine but not yet able to get any solution.Please help me out. I'm having 2 dropdownlists, one for country and another for state.I want the states to be filled according to the country selected from the country ddl. I'm having: 

Two dropdownlist controls as-ddlCountry and ddlState Two database tables as-Country and State Country table has fields named- C_ID(pk) and Country State table fields are-S_ID(pk), State, C_ID During page load, I'm able to bind the ddlCountry to the "Country" table in  databaseHence I could bind ddlState too  But I'm getting confused, how to bind the ddlState control so that it will show state as per country selected. I don't want to use ajax Please help me out.

Thanks and Regards,

Zinnia

 

13 Answers Found

 
 
 

Answer 3

Hi first of all set Allow Post Back for ddlcountry

and bind  ddlcountry if(!Page.IsPostBack)

{

//bind ddl  country

}

protected void _ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {

//here bind your state  ddl  and paas ddlstate.SelectedValue in where clause of sql query
        
    }


try and then reply...


 

Answer 4

If I'm understanding what you want to do correctly, then you're not going to bind  the ddlState control  on page  load, but rather on PostBack (i.e. after a selection of the country  has been made).  So yeah, ddlCountry is no problem  because you can just bind that straight away, but because your choice of states  depends on your choice of country, you query is going to involve a variable --- therefore you can't bind it from the get-go, because you don't know that variable yet (depends on whatever the user selects for country).

I myself recently had this same question as I'm just starting out with ASP.NET, C#, and SQL.  I found the following function to be handy:

private SqlDataReader GetDr(String sqltext)
{
        SqlDataReader dr;
        SqlConnection sqlconn = new SqlConnection(sqlconnstring);
        SqlCommand sqlcmd = new SqlCommand (sqltext, sqlconn);
        sqlcmd.Connection.Open();
        dr = sqlcmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        return dr;
}

Then in order to bind my second dropdown list, I simply have three lines, one of which calls GetDr().  If it was all in one table, your query would look something like:

String queryforstates = "SELECT States FROM States WHERE Country = '" + selectedcountry + "'";
ddlState.DataSource = GetDr(queryforstates);
ddlState.DataBind();

But looking at your description, you have two tables, so you're going to have to do an inner join to match up the C_ID in the state  table to get the Country field in the Country table, but I'll leave that to you :)

Good luck.

 

 

Answer 5

michael.ward:

If I'm understanding what you want to do correctly, then you're not going to bind  the ddlState control  on page  load, but rather on PostBack (i.e. after a selection of the country  has been made).  So yeah, ddlCountry is no problem  because you can just bind that straight away, but because your choice of states  depends on your choice of country, you query is going to involve a variable --- therefore you can't bind it from the get-go, because you don't know that variable yet (depends on whatever the user selects for country).

I myself recently had this same question as I'm just starting out with ASP.NET, C#, and SQL.  I found the following function to be handy:

private SqlDataReader GetDr(String sqltext)  {          SqlDataReader dr;          SqlConnection sqlconn = new SqlConnection(sqlconnstring);          SqlCommand sqlcmd = new SqlCommand (sqltext, sqlconn);          sqlcmd.Connection.Open();          dr = sqlcmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);          return dr;  }  

Then in order to bind my second dropdown list, I simply have three lines, one of which calls GetDr().  If it was all in one table, your query would look something like:

String queryforstates = "SELECT States FROM States WHERE Country = '" + selectedcountry + "'";  ddlState.DataSource = GetDr(queryforstates);  ddlState.DataBind();  

But looking at your description, you have two tables, so you're going to have to do an inner join to match up the C_ID in the state  table to get the Country field in the Country table, but I'll leave that to you :)

Good luck.



Hi Michael,

It's not working :(

Anyway how is it that u have taken a single table?What are your fields?Do lemme know, so that I can try it too.

 

Answer 6

Set the autopostback =true for ddlcountry.

In the page_Load

1)Bind the ddlCountry first ,TextFeild-country Name,Value Feild-C_ID

2) Then call a function BindStates(Convert.Toint32(ddlcountry.selecteditem.value)) ;

3) write a function

BindStates(int countryid)

{

select * from States where C_ID=countryid

Bind the values to dropdown.

}

4)in the ddlCountry_SelectedItemChanged()

{

ddlState.Items.Clear();

BindStates(Convert.Toint32(ddlcountry.selecteditem.value)) ;

}

 

 

 

Answer 7

Sorry, what I meant was that your SQL query will vary based on your tables  (I just offered up a single-table example to fill the query in my example code).

Like I said though, I'm totally new myself, so I could be wrong --- however I just copy/pasted my code, and it works.  What errors are you getting exactly?

Make sure that all of this is happening when your dropdownlist  selected index is changed (i.e. when a choice is actually made from the first dropdown list).  Here is my ASP.NET code for the dropdown list:

            <asp:DropDownList ID="CountryList" runat="server" 
                AppendDataBoundItems="True"
                DataSourceID="CountriesTable" DataTextField="CountryName" 
                DataValueField="CountryName" 
                onselectedindexchanged="CountryList_SelectedIndexChanged"  
                AutoPostBack="True">
            </asp:DropDownList>

Somethings you may have to add manually are the AutoPostBack="true" and onselectedindexchanged="functionname", and then in the code behind, have a corresponding function (in my case CountryList_SelectedIndexChanged).  So essentially this is saying, your first dropdown list will cause a postback AND execute the CountryList_SelectedIndexChanged function when a country  is selected.  And then in that function you would include your code to fill the second dropdown list.

 

Answer 8

Hello,

Have done (AutoPostBack="true" and onselectedindexchanged="functionname") but still not getting any any result. 

I'm having problem  in the query.As far as I've understood the prob myself, since I've taken 2 individual tables  for country  and state  & their is only one field common in both the tables i.e, C_ID, I've to store the C_ID value of the selected  country somewhere.Then in the selectedIndexChanged event of ddlCountry, I've to pass this C_ID value to ddlState so that it will show  the states  as per the country.This I'm not being able to do.Can anyone help  me out?Please help.

Thanks and regards,

Zinnia

 

Answer 9

for state  dropdown fill use query like this

select s.S_ID,s.State from State s inner join Country c on s.C_ID=c.C_ID and c.C_ID='"+ ddlstate.SelectedValue +"'


also you can check query in query designer of MGMT Studio


Thanks...

 

Answer 10

Hi,

According to your description ,please check the sample below,hope it can help  you.

1.Code in .aspx:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Country:<asp:DropDownList ID="DropDownList1" runat="server" 
            onselectedindexchanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true">
        </asp:DropDownList>
        State:<asp:DropDownList ID="DropDownList2" runat="server" 
            onselectedindexchanged="DropDownList2_SelectedIndexChanged"  AutoPostBack="true">
        </asp:DropDownList>
    </div>
    </form>
</body>
</html>     


2.Code in .cs:

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind();
        }
    }
    public void Bind()
    {
        DataTable dt = Get_source();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            this.DropDownList1.Items.Add(new ListItem(dt.Rows[i]["CountryName"].ToString(), dt.Rows[i]["CountryID"].ToString()));
        }
        this.DropDownList2.Items.Add(new ListItem("---Please select Country first---", "0"));
    }
    public DataTable Get_source()// you can get the data(Country) from your database,I get the value like this in order to make the page  convenient to run and read
    {
        DataTable dt = new DataTable();
        DataRow dr;
        string CountryNames = "Country1,Country2,Country3,Country4";
        string CountryIDs = "1,2,3,4";
        string[] list1 = CountryNames.Split(',');
        string[] list2 = CountryIDs.Split(',');
        dt.Columns.Add(new DataColumn("CountryName"));
        dt.Columns.Add(new DataColumn("CountryID"));
        for (int i = 0; i < list1.Length; i++)
        {
            dr = dt.NewRow();
            dr["CountryName"] = list1[i];
            dr["CountryID"] = list2[i];
            dt.Rows.Add(dr);
        }
        return dt;
    }
    public DataTable Get_Statesource()// you can get the data from your database
    {
        DataTable dt = new DataTable();
        DataRow dr;
        string StateIDs = "1,2,3,4,5,6,7,8";
        string CountryIDs = "1,2,2,2,2,3,3,1";
        string StateNames = "State1,State2,State3,State4,State5,State6,State7,State8";
        string[] list1 = StateIDs.Split(',');
        string[] list2 = CountryIDs.Split(',');
        string[] list3 = StateNames.Split(',');
        dt.Columns.Add(new DataColumn("StateID"));
        dt.Columns.Add(new DataColumn("CountryID"));
        dt.Columns.Add(new DataColumn("StateName"));
        for (int i = 0; i < list1.Length; i++)
        {
            dr = dt.NewRow();
            dr["StateID"] = list1[i];
            dr["CountryID"] = list2[i];
            dr["StateName"] = list3[i];
            dt.Rows.Add(dr);
        }
        return dt;
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string CountryID = this.DropDownList1.SelectedValue;
        DataTable dt = Get_Statesource();


        DataRow[] drs = dt.Select("CountryID=" + CountryID);
        DataTable newtable = new DataTable();
        newtable.Columns.Add(new DataColumn("StateID"));
        newtable.Columns.Add(new DataColumn("CountryID"));
        newtable.Columns.Add(new DataColumn("StateName"));
        foreach (DataRow row in drs)
        {
            DataRow dr = newtable.NewRow();
            dr.ItemArray = row.ItemArray;
            newtable.Rows.Add(dr);
        }
        if (newtable.Rows.Count > 0)
        {
            this.DropDownList2.Items.Clear();
            for (int i = 0; i < newtable.Rows.Count; i++)
            {
                this.DropDownList2.Items.Add(new ListItem(newtable.Rows[i]["StateName"].ToString(), newtable.Rows[i]["StateID"].ToString()));
            }
        }
        else
        {
            this.DropDownList2.Items.Clear();
            this.DropDownList2.Items.Add(new ListItem("---Sorry,there is no information---", "0"));
        }
    }
    protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
    {
        Response.Write("StateInformation:<br/>StateName:" + this.DropDownList2.SelectedItem.Text + "<br/>StateId:" + this.DropDownList2.SelectedValue);
    }
         


 

 

Answer 11

Rajneesh Verma:

for state  dropdown fill use query like this

select s.S_ID,s.State from State s inner join Country c on s.C_ID=c.C_ID and c.C_ID='"+ ddlstate.SelectedValue +"'


also you can check query in query designer of MGMT Studio


Thanks...


Hello,

Thanks a lot for the query.It's workingBut another problen has arisedI'm able to bind  ddlCountry and ddlState in Page loadBut databinding for ddlSate is not working in ddlCountry_SelectedIndexChanged eventThe code I'm using is as below-


protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            SqlConnection con = new SqlConnection("Data Source=PROF-C1FADB1C45\\SQLEXPRESS;Initial Catalog=Railway;Integrated Security=True");
            con.Open();
            SqlDataAdapter das = new SqlDataAdapter("SELECT State FROM State", con);
            DataSet dss = new DataSet();
            con.Close();
            das.Fill(dss);
            ddlState.DataSource = dss;
            ddlState.DataTextField = "State";
            ddlState.DataTextField = "State";
            ddlState.DataBind();
        }        
    }
    protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (this.IsPostBack)
        {
            SqlConnection con = new SqlConnection("Data Source=PROF-C1FADB1C45\\SQLEXPRESS;Initial Catalog=Railway;Integrated Security=True");
            con.Open();
            SqlDataAdapter dac = new SqlDataAdapter("SELECT City.City FROM City INNER JOIN State ON City.S_ID = State.S_ID WHERE (State.State =' " + ddlState.SelectedValue + " ')", con);
            DataSet dsc = new DataSet();
            con.Close();
            dsc.Tables.Add("City");
            dac.Fill(dsc,"City");
            ddlCity.DataSource = dsc.Tables["City"];
            ddlCity.DataTextField = "City";
            ddlCity.DataTextField = "City";
            ddlCity.DataBind();
        }
    }

protected void Page_Load(object sender, EventArgs e)

    {

        if (!this.IsPostBack)

        {

            SqlConnection con = new SqlConnection("Data Source=Z\\SQLEXPRESS;Initial                      Catalog=Proj;Integrated Security=True");

            con.Open();

            SqlDataAdapter das = new SqlDataAdapter("SELECT State FROM State", con);

            DataSet ds = new DataSet();

            con.Close();

            das.Fill(ds);


            ddlState.DataSource = ds;

            ddlState.DataTextField = "State";

            ddlState.DataTextField = "State";

            ddlState.DataBind();

        }        

    }

    protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)

    {

        if (this.IsPostBack)

        {

            SqlConnection con = new SqlConnection("Data Source=Z\\SQLEXPRESS;Initial                      Catalog=Proj;Integrated Security=True");

            con.Open();

            SqlDataAdapter dac = new SqlDataAdapter("SELECT City.City FROM City INNER                               JOIN State ON City.S_ID = State.S_ID WHERE (State.State =' " +                                               ddlState.SelectedValue + " ')", con);

            DataSet ds = new DataSet();

            con.Close();

            dac.Fill(ds);


            ddlCity.DataSource = ds;

            ddlCity.DataTextField = "City";

            ddlCity.DataTextField = "City";

            ddlCity.DataBind();

        }

    }


Although it's running  without showing any error, ddlState is not getting populated when I'm selecting some value from ddlCountry.AutoPostBack property is True for ddlCountryWhere is the error then?Please help  me out
 

Answer 12

hi,

forget all the suggestions and use it:

replace your aspx DropDownLists with these:

Country:
        <asp:DropDownList ID="ddlCountry" runat="server" OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged"
         AutoPostBack="True" AppendDataBoundItems="true">
        <asp:ListItem Text="Please Select Country" Value=""></asp:ListItem>
        </asp:DropDownList>

        State:
        <asp:DropDownList ID="ddlState" runat="server">
        <asp:ListItem Text="Select a country  first" Value=""></asp:ListItem>
        </asp:DropDownList>

----------------------------------------------

and in codebehind (aspx.cs):

add these name spaces:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

and then use this code:

protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                string conStr = ConfigurationManager.ConnectionStrings["webConfigConString"].ConnectionString;
                SqlConnection con = new SqlConnection(conStr);
                DataSet ds = new DataSet();
                try
                {
                    con.Open();
                    string sql = "SELECT C_ID, Country FROM Country";
                    SqlDataAdapter da = new SqlDataAdapter(sql, con);
                    da.Fill(ds);
                    ddlCountry.DataSource = ds;
                    ddlCountry.DataTextField = "Country";
                    ddlCountry.DataValueField = "C_ID";
                    ddlCountry.DataBind();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message.ToString());
                }
                finally
                {
                    ds.Dispose();
                    con.Close();
                }
            }
        }

        protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["webConfigConString"].ConnectionString);
            DataSet ds = new DataSet();

            con.Open();
            string sql="SELECT S_ID, state  FROM State WHERE C_ID=@C_ID";
            SqlDataAdapter da=new SqlDataAdapter(sql, con);
            da.SelectCommand.Parameters.AddWithValue("@C_ID", ddlCountry.SelectedValue);
            da.Fill(ds);
            
            ddlState.DataSource = ds;
            ddlState.DataTextField = "State";
            ddlState.DataValueField = "S_ID";
            ddlState.DataBind();

            ds.Dispose();
            con.Close();
        }


Note: I have used connection string name from web.config file, u can replace it with yours one

your Country table  data should be like this:

C_ID       Country

1             India

2             China

your state table data should be like this:

S_ID   State             C_ID

1         Delhi              1

2         Mumbai          1

3         Hong Kong     2

4         Guangdong   2


Hope it will improve your health.

 

Answer 13

 Thanks a lot, it worked.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter