combobox population based on each other sql c#

Question

i have 2 boxes, i'd like to populate the 1st from sql and the 2nd based on the first (querying sql too)

public void Country()
    {

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT CountryName FROM Country", conn))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);

                    DataRow dr = dt.NewRow();
                    dr["CountryName"] = "";
                    dt.Rows.InsertAt(dr, 0);
                    this.country.DisplayMember = "CountryName";
                    this.country.ValueMember = "CountryName";
                    this.country.DataSource = dt;
                }
            }
        }
    }

public void City()
    {

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT CityName FROM City", conn))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);

                    DataRow dr = dt.NewRow();
                    dr["CityName"] = "";
                    dt.Rows.InsertAt(dr, 0);
                    this.country.DisplayMember = "CityName";
                    this.country.ValueMember = "CityName";
                    this.country.DataSource = dt;
                }
            }
        }
    }

T A B L E S

Country

PK CountryName

City

PK CityName

FK CountryName

I believe i should change City's SqlCommand, maybe a WHERE statement? so if Country1 is chosen, in the City box it only shows City1, if Country2 then City2 and so on. how can i sort this, anyone knows? thanks

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

SELECT DISTINCT CityName FROM City where CountryName =@CountryName

Change the select statement with where clause as above,then you can set the parameter value using this.country.SelectedValue

You can load the second combobox on first combobox selelected index changed event. Call the City() inside first combobox selected index changed event with the where condition to filter city based on country

few links for related code:

c# Using Parameters.AddWithValue in SqlDataAdapter

Cascading ComboBox In Windows Application Using C#

edited May 23 '17 at 10:30

Community

1
1

answered Dec 16 '16 at 16:51

Damith

62,401
13
102
153

thank you. don't have time to test it at the moment but will absolutely do and mark the answer good if worked! – mihocu Dec 16 '16 at 17:00
@mihocu hope you understand the concept, given links will help you to do the coding your self. – Damith Dec 16 '16 at 17:08

combobox population based on each other sql c#

1 Answers1

Linked