locked
How to populate a combobox from a database with respect to other combobox selection RRS feed

  • Question

  • I have a database with three tables Type:with one attribute(tp) Manufacturer:with one attribute(mfcr) Model:with three attributes(mdl,tp,mfcr), tp and mfcr are foreign keys, from the above tables. In the front end i have three comboboxes: Type, Manufacturer and Model. I want them to populate data from these tables, but if i select one "type" it only shows me those types of manufacturers in the manufacturer combobox and when i select type and manufacturers both then it shows me only those types of models in the model combobox. For example i have in table type tp: sports car, suv, muscle car then i have in manufacturer table mfcr: ferrari, lamborghini, chevrolet, ford, hummer, jeep then i have in model table mdl: enzo , 453 italia, gollardo, murcialago, camaro-ss, comaro-ls, mustang-gt, shelby-gt500, hummer-h2, hummer-h3, wrangler, cherokee When i select sports car from type combobox, it shows me only ferrari and lamborghini in the manufacrurer combobox, and when i select manufacturer Lamborghini then it only shows me gollardo and murcialago in the model combobox. Same as with other items in the combobox. I am only able to populate type combobox using datasource property of combobox, as type value can contain all the values from the type table, but i want to populate the manufacturer combobox with respect to its type and same for model. I am using visual studio 2008
    Tuesday, March 8, 2011 1:15 PM

Answers

  • Another alternative is also there ...if you get all the data in a dataser initially...you can filter data as

    var dr = ds.Tables[1].Select("ColumnName='" + dropdown.SelectedValue + "'");

    and bind the dr with the 2nd dropdown and so on.

    Thanks
    Anirban
    Anirban Bhattacharya (Lead Consultant - ESSPL)
    Tuesday, March 8, 2011 6:30 PM
  • Check out this code:

     private void comboBox1_SelectedIndexChanged(object obj, EventArgs e)
        {
          string item = comboBox1.SelectedItem.ToString();
          comboBox2.DataSource = null;
          comboBox2.DataSource = GetDataFromDB(item);
          comboBox2.DisplayMember = "ItemA";
          comboBox2.ValueMember = comboBox2.DisplayMember;
        }
    
        private List<string> GetDataFromDB(string value)
        {
          List<string> list = new List<string>();
          using (SqlConnection sqlConn = new SqlConnection("connectionString"))
          {
            string query = @"SELECT ItemA FROM MyTable WHERE ItemB = @myParam";
            SqlCommand cmd = new SqlCommand(query, sqlConn);
            {
              using(SqlDataReader reader = cmd.ExecuteReader())
              while (reader.Read())
              {
                list.Add((string)reader[0]);
              }
            }
          }
          return list;
        }
    

     

    Code gets data out of database, and binds to the comboBox2. This is my example code, you only modify it to your needs (column names).

    Mitja

    • Marked as answer by Cookie Luo Friday, March 18, 2011 2:08 AM
    Tuesday, March 8, 2011 2:25 PM

All replies

  • Check out this code:

     private void comboBox1_SelectedIndexChanged(object obj, EventArgs e)
        {
          string item = comboBox1.SelectedItem.ToString();
          comboBox2.DataSource = null;
          comboBox2.DataSource = GetDataFromDB(item);
          comboBox2.DisplayMember = "ItemA";
          comboBox2.ValueMember = comboBox2.DisplayMember;
        }
    
        private List<string> GetDataFromDB(string value)
        {
          List<string> list = new List<string>();
          using (SqlConnection sqlConn = new SqlConnection("connectionString"))
          {
            string query = @"SELECT ItemA FROM MyTable WHERE ItemB = @myParam";
            SqlCommand cmd = new SqlCommand(query, sqlConn);
            {
              using(SqlDataReader reader = cmd.ExecuteReader())
              while (reader.Read())
              {
                list.Add((string)reader[0]);
              }
            }
          }
          return list;
        }
    

     

    Code gets data out of database, and binds to the comboBox2. This is my example code, you only modify it to your needs (column names).

    Mitja

    • Marked as answer by Cookie Luo Friday, March 18, 2011 2:08 AM
    Tuesday, March 8, 2011 2:25 PM
  • Another alternative is also there ...if you get all the data in a dataser initially...you can filter data as

    var dr = ds.Tables[1].Select("ColumnName='" + dropdown.SelectedValue + "'");

    and bind the dr with the 2nd dropdown and so on.

    Thanks
    Anirban
    Anirban Bhattacharya (Lead Consultant - ESSPL)
    Tuesday, March 8, 2011 6:30 PM