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

Community · Accepted Answer · 2017-05-23 10:30:40Z

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, 2017 at 10:30

CommunityBot

11 silver badge

answered Dec 16, 2016 at 16:51

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mihocu Over a year ago

thank you. don't have time to test it at the moment but will absolutely do and mark the answer good if worked!

Damith Over a year ago

@mihocu hope you understand the concept, given links will help you to do the coding your self.

Collectives™ on Stack Overflow

combobox population based on each other sql c#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related