cascading comboBox in windows form using c#

Question

I am trying to Fill Combobox2 on combobox1 selectedText changed from the same table in windows form application. I am using sql serevr 2008 database. I am unable to fill combobox2 on combobox selected text changed.

Here is what i have tried:

private void Purchase_Load(object sender, EventArgs e)
    {
        fillName();
        comboBoxName.SelectedIndex = -1;

    }

   private void comboBoxName_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (comboBoxName.SelectedText != "")
        {
            fillMake();
        }


    }

   private void fillName()
   {
       SqlConnection con = new SqlConnection(@"Data Source=ashish-pc\;Initial Catalog=HMS;Integrated Security=True");
       con.Open();
       string str = "Select Item_Name from Item";
       SqlCommand cmd = new SqlCommand(str, con);
       SqlDataAdapter adp = new SqlDataAdapter(str, con);
       DataTable dtItem = new DataTable();
       adp.Fill(dtItem);
       cmd.ExecuteNonQuery();
       comboBoxName.DataSource = dtItem;
       comboBoxName.DisplayMember = "Item_Name";
       comboBoxName.ValueMember = "Item_Make";


   }
    private void fillMake()
    {
        SqlConnection con = new SqlConnection(@"Data Source=ashish-pc\;Initial Catalog=HMS;Integrated Security=True");
        con.Open();
        string str = "Select Item_Make from Item Where Item_Name='" + comboBoxName.SelectedText + "'";
        SqlCommand cmd = new SqlCommand(str, con);
        SqlDataAdapter adp = new SqlDataAdapter(str, con);
        DataTable dtItem = new DataTable();
        adp.Fill(dtItem);
        cmd.ExecuteNonQuery();
        comboBoxName.DataSource = dtItem;
        comboBoxName.DisplayMember = "Item_Make";
        comboBoxName.ValueMember = "Item_Name";
        comboBoxName.SelectedIndex = -1;
        comboBoxName.Text = "Select";
    }

Sql server table for Items

Item_Code  Item_Name  Item_Make Item_Price UnitofMeasurement

           Cable        anchor  45.0000       meter
           Cable        polycab 30.0000       meter
           Button       anchor  15.0000       unit
           Button       havells 20.0000       unit
           Switch       cona    70.0000       unit

I have searched for solution but was unfortunate. please help me out. Thanks in advance.

Answer 1

It's a little difficult to figure out what you're trying to do, but it sounds like you are trying to populate a second combo box (comboBoxMake?) depending on what is selected in comboBoxName. I am basing this answer on that assumption. Apologies if I have this wrong.

There are lot of things that need attention in this code. Let's look at fillName() first.

   private void fillName()
   {
       SqlConnection con = new SqlConnection(@"Data Source=ashish-pc\;Initial Catalog=HMS;Integrated Security=True");
       con.Open();
       string str = "Select Item_Name from Item";
       SqlCommand cmd = new SqlCommand(str, con);
       SqlDataAdapter adp = new SqlDataAdapter(str, con);
       DataTable dtItem = new DataTable();
       adp.Fill(dtItem);
       cmd.ExecuteNonQuery();
       comboBoxName.DataSource = dtItem;
       comboBoxName.DisplayMember = "Item_Name";
       comboBoxName.ValueMember = "Item_Make";
   }

You need to Dispose() your database objects. This can be accomplished pretty cleanly with using { .. } blocks.

You don't need to manually open the connection; filling the table with the data adapter does this automatically.

You don't need the call to ExecuteNonQuery().

You should use the SqlDataAdapter constructor overload that takes a command object, since you have already manually created the command.

Finally, based on my assumption of your goal I have added a distinct to your query so it only gets the unique Item_Names.

private void fillName()
{
    string str = "Select distinct Item_Name from Item";
    using (SqlConnection con = new SqlConnection(@"Data Source=ashish-pc\;Initial Catalog=HMS;Integrated Security=True"))
    {
        using (SqlCommand cmd = new SqlCommand(str, con))
        {
            using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
            {
                DataTable dtItem = new DataTable();
                adp.Fill(dtItem);
                comboBoxName.DataSource = dtItem;
                comboBoxName.DisplayMember = "Item_Name";
                comboBoxName.ValueMember = "Item_Name";
            }
        }
    }
}

On to fillMake(). The same suggestions apply that I noted above. Additionally:

Parameterize your SQL. Parameterize your SQL. Not only is this far, far safer than concatenating your SQL together, it is much cleaner. Seriously, read about SQL injection: http://en.wikipedia.org/wiki/SQL_injection

The fillMake() method in your original post seems to be repopulating comboBoxName. Is this correct? You mention two combo boxes but your code only references one. I am assuming you mean to populate another combo box (comboBoxMake?) here:

private void fillMake()
{
    string str = "Select Item_Make from Item Where Item_Name = @item_name";
    using (SqlConnection con = new SqlConnection(@"Data Source=ashish-pc\;Initial Catalog=HMS;Integrated Security=True"))
    {
        using (SqlCommand cmd = new SqlCommand(str, con))
        {
            cmd.Parameters.AddWithValue("@item_name", comboBoxName.Text);
            using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
            {
                DataTable dtItem = new DataTable();
                adp.Fill(dtItem);
                comboBoxMake.DataSource = dtItem;
                comboBoxMake.DisplayMember = "Item_Make";
                comboBoxMake.ValueMember = "Item_Make";
                comboBoxMake.SelectedIndex = -1;
                comboBoxMake.Text = "Select";
            }
        }
    }
}

Lastly, change the code in the event handler so it looks at the Text rather than the SelectedText property:

private void comboBoxName_SelectedIndexChanged(object sender, EventArgs e)
{
    if (!string.IsNullOrEmpty(comboBoxName.Text))  // Text instead of SelectedText
    {
        fillMake();
    }
}