DropDownList populates based on First DropDown Metro Station Colors-CodePudding

This is pretty straightforward. I know how to do this pragmatically with using a OnSelectedIndexChange and a event on the first dropdown. I'm trying to figure out the best way to do this architecturally since in my case I'm dealing with Metro stations where a particular station will have more than one color.

e.g. First Dropdown List with Color (i.e. Red, Green, Purple)

2nd Dropdown List with Stations "1st Street - Red and Green" "Bloom Ave - Green" "Titan Station - Red Purple"

So if the user selects Red on the FIRST dropdown, the 2nd dropdown should populate with "1st Street" as well as Titan Station". If the user selects Green it should just display "Bloom Ave".

There are many stations with color overlap (more than one color).

The proper way in my mind to do this is create a table with station names and a column for each color with the datatype BIT. If a station has more than one color, those columns would be set to 1 or True. I would have a stored procedure that would fire, the procedure would perform a selection based on color and then show all applicable stations which would bind to the 2nd dropdown.

Is there another way to do this entirely pragmatically, by just the dropdowns itself. Example, you write this logic in the DropDownList control and it knows what's associated with what?

CodePudding user response：

Well, you certainly don't want to start or introduce into your project some kind of bit row wise processing for what amounts to a rather simple and "class" relational model in database land for your solution.

I mean, if you have a customer, and they are to have many invoices, then we don't introduce some bit-wise processing here. (because sql server don't work that way).

So, if you have some customers, then if you need "many" invoices, then you create a invoice table.

If you have some Stations, and they can have 1 or 10 different colors, then you make a table called MyStationColors. This is how all data base operations work.

In fact, the only real question is do you want to store the color (as text name), or do we store the PK id of the color (from our table of colors).

I tend be it a customer, a color, or whatever? I store the PK id, and we are free to display some nice user friendly color description as we please.

so, we need:

Colors - a simple table of all the colors we are going to use
Station - a simple table of all the stations
MyStationColors  - a table that lists out all the colors for a given station.

So, once we have the above, then this all becomes a rather simple SQL and database example.

So, we will have 3 tables. Say like this:

Now, I have not introduced any relationships between the color table. After all, it really is just a pick list, or our main list of colors. but, we can add a relationship to that table if we want (it would prevent us from deleting colors are are currently in use by a given station. But, lets keep this simple.

So, our first combo box will be driven (filled out) by a simple combo box, and we feed it the colors table.

And as noted, we went with "id", but a combo box is able to have TWO columns, usually the PK (for code behind), and 2nd color for display to your users.

So, drop in a combo box, say like this:

        <asp:DropDownList ID="DropDownList1"
            runat="server"
            DataValueField="ID"  
            DataTextField="Color" Width="141px"
            AutoPostBack="true" >

        </asp:DropDownList>

(don't forget the auto post back in above!!!)

Note the "two" columns for the drop down (Value is hidden, Text is display).

And code to fill combo is this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadCombo()
    End If

End Sub

Sub LoadCombo()

    Dim cmdSQL As New SqlCommand("SELECT ID, Color FROM Colors ORDER BY Color")

    DropDownList1.DataSource = MyrstP(cmdSQL)
    DropDownList1.DataBind()
    DropDownList1.Items.Insert(0, (New ListItem("Select Color", "0")))

End Sub

And now we see/get this:

And dropping the box, we see this:

So, now all we have to do is wire up the selected index change event.

but, before we do that, and I am VERY lazy, lets use sql studio, and create a view (a query) that will display the stations and their colors. We use that for the "display" of the results.

So, new view in sql server, and we get to do this via drag/drop and GUI (after all, who's going to write that sql by hand, right???).

So, we have this:

And when we run above, we get this:

As we can see, some stations have more then one color, so they show up more then one time. But, we let the query builder in SSMS build that sql above for us - all drag and drop so far.

Ok, save that view (Vstations).

Now, we need to display the results of our choice. So, lets drop in a grid view like this:

        <asp:DropDownList ID="DropDownList1"
            runat="server"
            DataValueField="ID"  
            DataTextField="Color" Width="141px" >

        </asp:DropDownList>

        <asp:GridView ID="GridView1" runat="server">


        </asp:GridView>

And now in property sheet for dropdown, we add the selected index event (double click here:)

And now the code for selecting the combo is this:

Protected Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList1.SelectedIndexChanged

    Dim strSQL As String =
        "SELECT * FROM StationsV WHERE COLOR_ID = @COLOR"
    Dim cmdSQL = New SqlCommand(strSQL)
    cmdSQL.Parameters.Add("@COLOR", SqlDbType.Int).Value = DropDownList1.SelectedItem.Value

    GridView1.DataSource = MyrstP(cmdSQL)
    GridView1.DataBind()

End Sub

So, now when we select a color, all the stations with a given color will show like this:

Now, of course we should clean up the grid, remove the "ID" and also remove color_id from that grid. But that's just nuts and bolts.

and of course a extra bonus would be to say show all the colors for a given station in above. And we can do that. But, as you can see, these problems are best approached by using a correct data model to reflect the real world case of a station having many colors. And we don't thus mess or introduce some kind of complex bit wise processing. We most certainly did have to introduce a extra table (to keep track of colors for a given station), but other then this bit of extra work, once done, then we are free to add 1 or 5 colors to a station, and we don't have to change the design, or even say assume or add some bit color for a given color.

Be it customers and invoices, or a station with some colors - the relational data model approach tends to make such problems rather easy.

Last but not least, I do get tired of typing connection and loading of a data table over and over, so I also used and had this handy routine MyRstP to return a datatable.

Public Function MyrstP(sqlCmd As SqlCommand) As DataTable

    Dim rstData As New DataTable

    Using sqlCmd
        sqlCmd.Connection = New SqlConnection(My.Settings.TEST5)
        sqlCmd.Connection.Open()
        rstData.Load(sqlCmd.ExecuteReader)
    End Using

    Return rstData

End Function