asked
Relating Data From a database View for Cascading DropDown Lists
For my UI, I have to create cascading dropdown lists where When Field 1 is selected then Field 2 displays available data related to Field 1 then Field 3 displays available data related to Field 2 All of the rows are displayed in one view. Is it possible to relate the data in a SQL statement
Are you saying you have one view with a select statement that has all three values on the same row? Like this: SELECT Field1, Field2, Field3 FROM someTable JOIN someOther table ON ... Then you could for the first DROP-down have a SELECT statement like: SELECT DISTINCT Field1 FROM theView Next would be: SELECT DISTINCT Field2 FROM theView WHERE Field1 = @Field1 For that SqlCommand object in
ADO.NET you'd add parameter @Field1 to be the selected value from dropdown 1. Third would be similar: SELECT Field3 FROM theView WHERE Field2 = @Field2 If that's not what you're looking for, please post the CREATE-statement for your view, and some sample data.
If you have a view that contains all the information, then yes. And you could even do it without making round trips to the database server by caching a recordset and filtering it as the combo boxes change. For example, I had a system where we had Master Categories and Sub Categories. These were linked, something like this: CREATE TABLE MasterCat (mcID, mcTitle, mcDescription) CREATE TABLE SubCat (scID, mcID, scTitle, scDescription) So the SubCat table has a link to the MasterCat table. Using these, a view / query such as the following could be used: SELECT mcTitle, scTitle FROM MasterCat mc LEFT JOIN SubCat sc on mc.mcID = sc.mcID That'll return a recordset which contains the master category title and the SubCategory title. You can loop through this to populate the first combo. When the user selects a value from the first combobox, you can filter the recordset on the selected value in the first combo, and populate the second combobox accordingly.
+1, I agree with @ThomasRushton here. Some further comments: If one would do a solution without database server roundtrips for each load, I'd create a DataSet, with one DataTable (containing the whole view) and define two relations in the DataSet for that DataTable, so that it's possible to use the relation objects to filter out DropDownList 2 and 3. For someone not too familiar with
ADO.NET, I'd consider this a little too advanced, but I can post some sample code if your're interested.
