Cascading ComboBoxes

[tbpowers]

I have a combobox bound to a stored procedure, which writes the Selected Value to a table in my database. Works great! Now I want to populate another combobox based on the selection in the first combobox. In order to do so, it will have to filter based off a sub_category_id related to the Selected Value in my first combobox. I have the logic worked out in SQL, just need help making it work in VB.Net. I do not really want to code in a pass through query. I would rather somehow pass the sub_category_id to a stored procedure as its paramenter. Any ideas on how I can make this work?

Example Table:
[TABLE="width: 600"]
[TR]
[TD]CATEGORY ID[/TD]
[TD]SUB CATEGORY ID[/TD]
[TD]VALUE[/TD]
[TD]LABEL[/TD]
[/TR]
[TR]
[TD]PCAT[/TD]
[TD]CENF[/TD]
[TD]CENTRIFUGAL_FAN[/TD]
[TD]Centrifugal Fan[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]COND[/TD]
[TD]CONDENSER[/TD]
[TD]Condenser[/TD]
[/TR]
[/TABLE]


SELECT LOOKUP_CATEGORY, VALUE, LABEL
FROM REPAIR_LOOKUP
WHERE LOOKUP_CATEGORY = (SELECT SUB_CATEGORY_ID
FROM REPAIR_LOOKUP
WHERE VALUE = ComboBox1.SelectedValue)
AND ACTIVE = 'Y'
ORDER BY LABEL ASC

 

[Menthos]

Unless I'm missing something, it looks like you're over-complicating things.

Would it not be simpler to pull your REPAIR_LOOKUP table query into a datatable, then use the .Select() function to filter your dropdown items?

 

[tbpowers]

I have been know to do that and this is my first stab at a vb.net app. Everything I have read tells me to be using stored procedures for security purposes. If I pull in the table can you give me some help with the syntax? Also, the way we have this modeled it will depend on what is chosen in the first combobox as to what shows up in the next combobox. I appreciate the reply!

 

[Menthos]

Using stored procedures does add a level of security, and there's no reason not to use one here to populate your datatable. The main security hole to avoid is building query strings with concatenation - look up using parametized queries.

You can handle the selection changes in your first combobox to then drive the population of the second easily.

 

[tbpowers]

Not sure how I can populate the second combobox without using a bunch of "if" statements. There are 17 items to select from in the first combobox, so the logic on the second combobox would be something like this:

If SUB_CATEGORY_ID = 'CENF' THEN 'SELECT LABEL FROM REPAIR_LOOKUP WHERE CATEGORY_ID = 'CENF'
ELSE IF
SUB_CATEGORY_ID = 'COND' THEN 'SELECT LABEL FROM REPAIR_LOOKUP WHERE CATEGORY_ID = 'COND'
ELSE IF...

Does that make sense? Also, I will need to show the LABEL but write back the VALUE to the database.

I need to think about this some more.

 

[Menthos]

Nope, much easier than that - you're still thinking too much along the SQL lines.

Just use mydatatable.Select("CATEGORY_ID = " & mycombobox.SelectedValue().ToString()) which will return you a row collection that you can use as your datasource for the second dropdown.

Take a look at the datatable.Select info.

 

[tbpowers]

The actual "SelectedValue" in the first combobox is not the "CATEGORY_ID". It is the data I am writing back to my database, which is the items in the "VALUE" column from my example table above.

 

[Menthos]

Ok, not following how your data relates - it's late and I need coffee - but that's not what I'm helping you with here. Your second combobox is relationally linked to your first, so use the first to filter the items to be shown in the second.

It's likely you could also do something with a couple of datatables within a dataset with a parent/child relationship, but that may over complicate matters.

 

[tbpowers]

I understand the need for coffee. Let me chew on this for awhile and I'll get back to you with my results. I appreciate the help very much!

 

[tbpowers]

It is hard to explain, but I need to accomplish this with a stored procedure. Plus I would really like to learn how to do this. Any idea how I can pass the Selected Value from Combobox1 to the following stored procedure?

CREATE PROCEDURE STP_PRIMARY_FAILURE
@SUB_CATEGORY_ID VARCHAR(15)
AS
SELECT REP_LOOKUP_ID, LOOKUP_CATEGORY, SUB_CATEGORY_ID, VALUE, LABEL
FROM REPAIR_LOOKUP
WHERE SUB_CATEGORY_ID = (SELECT SUB_CATEGORY_ID
FROM REPAIR_LOOKUP
WHERE VALUE = @SUB_CATEGORY_ID
AND SUB_CATEGORY_ID IS NOT NULL)
AND ACTIVE = 'Y'
ORDER BY LABEL ASC
GO

 

[tbpowers]

My main problem is that one VALUE may be associated to more than one CATEGORY_ID in my lookup table. In order to get the correct record(s) it has to include the sub-query in the stored procedure above.

 

[tbpowers]

I renamed the procedure to make it easier to understand.

CREATE PROCEDURE STP_PRIMARY_FAILURE
@SELECTED_VALUE VARCHAR(15)
AS
SELECT REP_LOOKUP_ID, LOOKUP_CATEGORY, SUB_CATEGORY_ID, VALUE, LABEL
FROM REPAIR_LOOKUP
WHERE SUB_CATEGORY_ID = (SELECT SUB_CATEGORY_ID
FROM REPAIR_LOOKUP
WHERE VALUE = @SELECTED_VALUE
AND SUB_CATEGORY_ID IS NOT NULL)
AND ACTIVE = 'Y'
ORDER BY LABEL ASC
GO

 

[cjard]

Follow the relevant DW link in my sig for your .net version (I can't see your version in the tapatalk iPod app) and read the tutorial Creating a Simple Data App
It features a master/detail relation like you have here - the same logic can be applied to a child combo rather than child grid. Much easier than even Mentos method

 

[tbpowers]

I am making some head way. I ended up spliting the master table up inot multiple tables and creating relationships between them. Following the "Creating a Simple Data App" tutorial I now have my child combobox pulling in the correct data, based off the parent combobox. However, I still have an issue. It does not show the correct data for existing records. I have bound the combobox correctly to my table column, so I am not sure what the deal is. The parent combobox works fine. Any ideas?

 

[tbpowers]

I got it!! It was the bindings. Thanks for all the help. It is greatly appreciated!!!