Styjun

I need to get productID from product table and pass it to a textbox. But I want to select the product ID by cascading comboBox texts as SQL query parameters.

I have tried cascading the comboBoxes. The cascading works fine and displays what I need.

I have done some research in cascading comboBox. Here are two useful links I have found out.

cascading comboBox in windows form using c#

fill textbox on second combobox selection changed in cascading combobox in windows form using c#

In both links I did not find answer to my question.

Here is a demo table

ProductID Category SubCategory Item
--------- ------------------------------ -------------------- ----------------
1 Pen Ballpoint Matadoor
2 Pencil HB Natraz

I want get ProductID values by selecting Category, SubCategory, Item using cascaded comboBoxes.

Here is some demo code that takes care of the cascading.

1st ComboBox:

public InventroyAddForm()


 InitializeComponent();
 string maincon = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
 string query1 = "select * from Product";
 string query2 = "select * from PartySpecificPrice";
 SqlConnection con = new SqlConnection(maincon);
 SqlCommand com = new SqlCommand(query1, con);
 //con.Open();
 SqlDataAdapter adp = new SqlDataAdapter(com);
 DataTable dt = new DataTable();
 adp.Fill(dt);
 comboBox2.ValueMember = "Category";
 comboBox2.DisplayMember = "Category";
 comboBox2.DataSource = dt;
 //con.Close();
 comboBox3.Enabled = false;
 comboBox4.Enabled = false;

2nd ComboBox:

private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)

 if(comboBox2.SelectedValue.ToString() != null)
 
 string maincon = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
 string query1 = "select * from Product where Category=@Category";
 SqlConnection con = new SqlConnection(maincon);
 SqlCommand com1 = new SqlCommand(query1, con);
 //con.Open();
 com1.Parameters.AddWithValue("@Category",comboBox2.SelectedValue.ToString());
 SqlDataAdapter adp1 = new SqlDataAdapter(com1);
 DataTable dt1 = new DataTable();
 adp1.Fill(dt1);
 comboBox3.ValueMember = "Category";
 comboBox3.DisplayMember = "SubCategory";
 comboBox3.DataSource = dt1;
 comboBox3.Enabled = true;
 comboBox4.Enabled = false;
 //con.Close();
 

3rd ComboBox:

private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)

 if (comboBox3.SelectedValue.ToString() != null)
 
 string maincon = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
 string query2 = "select * from Product where Category=@Category";
 SqlConnection con = new SqlConnection(maincon);
 SqlCommand com2 = new SqlCommand(query2, con);
 //con.Open();
 com2.Parameters.AddWithValue("@Category", comboBox3.SelectedValue.ToString());
 SqlDataAdapter adp2 = new SqlDataAdapter(com2);
 DataTable dt2 = new DataTable();
 adp2.Fill(dt2);
 comboBox4.ValueMember = "Category";
 comboBox4.DisplayMember = "Item";
 comboBox4.DataSource = dt2;
 comboBox3.Enabled = true;
 comboBox4.Enabled = true;
 //con.Close();
 textBox2.Text = Convert.ToString(comboBox3.SelectedIndex);
 

Based on @vikschool comments. I have broken up the tables as follows:

CID Category
----------- --------------------------------------------------
 1 Pen
 2 Pencil


SID CID SubCategory
----------- ----------- --------------------------------------------------
 1 1 BallPoint
 2 2 HB


ID SID CID Item
----------- ----------- ----------- --------------------------------------------------
 1 1 1 Matadoor
 2 2 2 Natraz


Category SubCategory Item
-------------- -------------------------------------------------- -----------------------
Pen BallPoint Matadoor
Pencil HB Natraz

Here is the code that worked for me.

FormLoad:

private void Form1_Load(object sender, EventArgs e)
 
 string query2 = "select * from Category";
 string maincon = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
 SqlCommand com1 = new SqlCommand(query2, con);
 con.Open();
 SqlDataAdapter adp1 = new SqlDataAdapter(com1);
 DataTable dt1 = new DataTable();
 adp1.Fill(dt1);
 comboBox1.ValueMember = "CID";
 comboBox1.DisplayMember = "Category";
 comboBox1.DataSource = dt1;
 comboBox3.Enabled = false;
 comboBox2.Enabled = false;

 

First ComboBox:

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
 
 if (comboBox1.SelectedValue != null)
 

 string query2 = "select * from SubCategory where CID=@CID";
 SqlCommand com1 = new SqlCommand(query2, con);
 com1.Parameters.AddWithValue("@CID", comboBox1.SelectedValue);
 SqlDataAdapter adp1 = new SqlDataAdapter(com1);
 DataTable dt1 = new DataTable();
 adp1.Fill(dt1);
 comboBox2.ValueMember = "SID";
 comboBox2.DisplayMember = "SubCategory";
 comboBox2.DataSource = dt1;
 comboBox3.Enabled = false;
 comboBox2.Enabled = true;

 
 

Second ComboBox:

private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
 
 if (comboBox2.SelectedValue != null)
 
 string query2 = "select * from Item where SID=@ID";
 SqlCommand com1 = new SqlCommand(query2, con);
 com1.Parameters.AddWithValue("@SID", comboBox2.SelectedValue);
 SqlDataAdapter adp1 = new SqlDataAdapter(com1);
 DataTable dt1 = new DataTable();
 adp1.Fill(dt1);
 comboBox3.ValueMember = "ID";
 comboBox3.DisplayMember = "Item";
 comboBox3.DataSource = dt1;
 con.Close();
 comboBox3.Enabled = true;
 comboBox2.Enabled = true;
 
 

Third ComboBox:

private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
 
 textBox1.Text = Convert.ToString(comboBox3.SelectedValue);
 

Note: I have tried this with Access database. Sql should work with above code. Please let me know if anyone find any problem using this piece of code.
Happy Coding.
Thanks to @vikschool.