Question Load data in to dataGridView based on combo box selection

[andrewmanuja]

Hi,
I got a combo box and a datagridview and my requirement is, based on the user's selection of an item in the combo box (Category Name), the respective product details should populate in to the dataGrid View.

I got two tables in SQL Server named, tbl_Product (ProductID, ProductName and CategoryID) and tbl_Category (CategoryID, CategoryName).

My form load event looks like below;

dataGridViewProducts.Rows.Clear();
            dataGridViewProducts.Rows.Clear();
            string query = "SELECT CategoryID, CategoryName FROM tbl_Category";
            comboBoxCategory.DataSource = getData(query);
            comboBoxCategory.DisplayMember = "CategoryName";
            comboBoxCategory.ValueMember = "CategoryID";

            comboBoxCategory_SelectedIndexChanged(null, null);

The combo box selectedindexchange event looks like below;

             int val;          
            Int32.TryParse(comboBoxCategory.SelectedValue.ToString(), out val);                      
            string query = "SELECT ProductID, ProductName,CategoryID  FROM tbl_Product WHERE CategoryID ="+val ;
            dataGridViewProducts.DataSource = getData(query);

Finally, I got a function named, "GetData" to call the sql command

 cmd = new SqlCommand(query,con);
            sda = new SqlDataAdapter(cmd);
            dt = new DataTable();
            sda.Fill(dt);
            return dt;

Despite the application runs without errors, I only get the results related to the "CategoryID =1" , though I change the "CategoryName" value from the combo box, the dataGridView data does not change accordingly.

Appreciate a lot your valuable help to resolve this problem.

Kind regards,

Andrew

 

[Skydiver]

First of all. Always use parameterized queries. It maybe tempting in this case to just build up the string yourself because you are in full control of the inputs for the string that you are building, but what if in the future somebody decides to make your dropdown combobox into just a vanilla combobox or textbox that lets the user type in free form text? Or if enough refactoring happens that you have no idea where the input is coming from? (e.g. data access code should not have direct access to the UI, but yet you do so above -- this should be refactored away.)

Anyway do you know that the TryParse() is always succeeding? What do the failing/empty queries look like? What does the succeeding look like?

 

[andrewmanuja]

Hi,

Yes, the said resulted query looks like, "SELECT ProductID, ProductName, CategoryID FROM tbl_Product WHERE CategoryID=1".

Meaning, is it only picking a single row of data/first row despite there are 3 rows of CategoryIDs in the table?

Regards,

andrew

 

[jmcilhinney]

Unless you have a very large amount of data, I would suggest that you just get it all upfront and then filter locally. Get the entire tbl_Product table into a DataTable, bind that to a BindingSource and bind that to the DataGridView. When the user makes a selection, set the Filter of the BindingSource accordingly.

By the way, get rid of the direct call to comboBoxCategory_SelectedIndexChanged. You should never be calling an event handler directly. Set the DisplayMember and ValueMember first and the DataSource last, the the SelectedIndex event will be raised anyway, so your event handler is already going to be executed.

 

[andrewmanuja]

Thank you very much.

I'll do accordingly.

Regards,

Andrew