Getting multiple dropdown controls to work with dynamic data is not for the faint of heart. Hopefully this will help give others a head start.
Create a gridex control and add 2 dropdown lists as per janus tutorials.
In the load event of the form, add:
'load the grid classification dropdown
'In this case the dropdown has 2 columns, the classification name and the database row id
grdPeople.DropDowns(0).DataSource = Classification.getDataSet().Tables(0)
'This dropdown has 3 columns, sub class name, database row id, and the classification foreign key
grdPeople.DropDowns(1).DataSource = Classification.getSubClassDataSet().Tables(0)
'There is no selection changed event for the dropdowns.
'You have to use the dropdown hide event of the grid instead.
'It will fire for any dropdown on the grid so you have to check for which one did
AddHandler grdPeople.DropDownHide, AddressOf loadSubClassDDL
'Because the dropdowns are the same instance for each row, you can't reload the dataset for each row
'You need to apply a filter instead. The second dropdown needs to have all possible selection choices
'preloaded into it.
'In this case for the inital load of the form I'm only going to show the sub classes with a foreign key of 1
'apply the default for the empty document
Dim filter As New GridEXFilterCondition
filter.Column = grdPeople.DropDowns(1).Columns("intClassificationIDFK")
filter.ConditionOperator = ConditionOperator.Equal
filter.Value1 = 1 'The default foreign key for an empty document
grdPeople.DropDowns(1).ApplyFilter(filter)
In the loadSubClassDDL function (referenced in the addhandler call above) add:
'Check the column to make sure that its the first column that fired the event
If e.Column.DataMember = "vchrClassificationName" Then
'Each row uses the same dropdown, so changing the dataset isnt practical
'filtering the data instead
Dim filter As New GridEXFilterCondition
filter.Column = grdPeople.DropDowns(1).Columns("intClassificationIDFK")
filter.ConditionOperator = ConditionOperator.Equal
filter.Value1 = grdPeople.DropDowns(0).GetValue(1).ToString()
grdPeople.DropDowns(1).ApplyFilter(filter)
End If
The grids SelectionChanged event will also need to be trapped. Otherwise when someone clicks another row in the
grid it will leave the filtering as it was the last time the first dropdown was changed (in any row).
In this case I'm setting the filter through a lookup table. I did it this way because this event is fired before the id in
the first dropdown list is updated. The text in the cell itself is correct though.
If grdPeople.SelectedItems(0).RowType = RowType.Record Then
'when a user changes which row is selected the filter needs to be changed to
'match the current rows classification
Dim filter As New GridEXFilterCondition
filter.Column = grdPeople.DropDowns(1).Columns("intClassificationIDFK")
filter.ConditionOperator = ConditionOperator.Equal
filter.Value1 = cls.lookupID(grdPeople.GetValue("vchrClassificationName").ToString())
'filter.Value1 = grdPeople.DropDowns(0).GetValue(1).ToString()
grdPeople.DropDowns(1).ApplyFilter(filter)
End If