ASP.Net - How to Create Cascading DropDownLists Boxes Using Single Data table

Question

I want to create cascading/dependent DropDown boxes from single data table. All data is in single table such as country,state,city.

When user select country from DropDown list box states should be filled automatically and so on...

There are many example which are using different table but how to do it using single table. please help/suggest/any link....

    string str5 = "SELECT distinct States FROM tblMain 
    da1 = new SqlDataAdapter(str5, con);
    dt1 = new DataTable();
    da1.Fill(dt1);
    ddlEmpName.DataSource = dt1;
    ddlEmpName.DataTextField = "States";
    ddlEmpName.DataValueField = "States";
    ddlEmpName.DataBind();

Answer 1

Maybe this will get you started. All the data is in a single DataTable and will be displayed in the same DropDownList.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            //create a datatable with three columns
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Type", typeof(string));
            dt.Columns.Add("Name", typeof(string));

            //add nine datarows
            dt.Rows.Add(0, "Country", "Netherlands");
            dt.Rows.Add(1, "Country", "Japan");
            dt.Rows.Add(2, "Country", "America");
            dt.Rows.Add(3, "State", "Gelderland");
            dt.Rows.Add(4, "State", "Texas");
            dt.Rows.Add(5, "State", "Echizen");
            dt.Rows.Add(6, "City", "Amsterdam");
            dt.Rows.Add(7, "City", "Tokyo");
            dt.Rows.Add(8, "City", "New York");

            //save the table in a viewstate
            ViewState["myViewState"] = dt;

            //fill the dropdown
            fillDropDownList("Country");
        }
    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        //split the SelectedValue to find the next type of data for the dropdown
        string[] valueArr = DropDownList1.SelectedValue.Split('_');
        string filterType = valueArr[1];

        //get the next type
        if (filterType == "Country")
        {
            filterType = "State";
        }
        else if (filterType == "State")
        {
            filterType = "City";
        }
        else
        {
            filterType = "Country";
        }

        Label1.Text += valueArr[0] + "<br>";

        //refill the dropdown
        fillDropDownList(filterType);
    }

    private void fillDropDownList(string filterType)
    {
        //get the viewstate and cast it as a datatable
        DataTable dt = ViewState["myViewState"] as DataTable;

        //filter the correct rows with Linq
        DataTable dtFiltered = dt.AsEnumerable().Where(row => row.Field<string>("Type") == filterType).CopyToDataTable();

        //clear the existing items in the dropdown
        DropDownList1.Items.Clear();

        //loop the filtered items and add them to the dropdown
        for (int i = 0; i < dtFiltered.Rows.Count; i++)
        {
            string text = dtFiltered.Rows[i][2].ToString();
            //make the value a combination of the name and type to split in SelectedIndexChanged
            string value = text + "_" + dtFiltered.Rows[i][1].ToString();

            DropDownList1.Items.Insert(i, new ListItem(text, value, true));
        }
        DropDownList1.Items.Insert(0, new ListItem("Select...", "", true));
    }

And in the aspx page:

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"></asp:DropDownList>
<br /><br />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>