Gridview ASP.NET Changing values of the second drop down list based on value selected of first drop down in same column

Question

<asp:SqlDataSource  ID="sdsUsers" runat="server"
ConnectionString="<%$ ConnectionStrings:Org Name %>"
SelectCommand="select full_name, user_id from dbo.Users where entity_state_id = 1">
</asp:SqlDataSource>
<asp:SqlDataSource  ID="sdsOrganizations"
runat="server"
ConnectionString="<%$ ConnectionStrings:Org Name %>"
SelectCommand="select organization_name from dbo.Organizations where Organization_Name like '%CDM%'">
</asp:SqlDataSource>      



<asp:TemplateField HeaderText="Status" SortExpression="type">
<ItemTemplate>
<%# Eval("Status")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Assigned To" SortExpression="type">
<ItemTemplate>
<asp:DropDownList ID="ddlOrganization" runat="server" DataSourceID="sdsOrganizations" 

DataTextField="organization_name" AutoPostBack="true" AppendDataBoundItems="true"> <asp:ListItem Text="Not Assigned" Value="0"></asp:ListItem> </asp:DropDownList>

<asp:DropDownList ID="ddlUsers" runat="server" DataSourceID="sdsUsers" DataTextField="full_name" DataValueField="user_id" AutoPostBack="true" AppendDataBoundItems="true"> <asp:ListItem Text="Not Assigned" Value="0"></asp:ListItem> </asp:DropDownList> </asp:TemplateField>

Good afternoon , I need to populate users list from the database based on the organization they select in gridview.

How can i do that?

Thanks

Answer 1

Ok, assuming a grid like this (with two combo box).

In this example, we have some people, and we want them to select city, and then select a hotel from for the given city (so we cascade from City cbo to hotel list for 2nd combo).

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    DataKeyNames="ID" CssClass="table" Width="30%" >
    <Columns>
        <asp:BoundField DataField="Firstname" HeaderText="Firstname" />
        <asp:BoundField DataField="LastName" HeaderText="LastName"  />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:TemplateField HeaderText="Select Hotel City">
            <ItemTemplate>
                <asp:DropDownList ID="cboCity" runat="server" Width="120px"
                    DataTextField = "City"
                    DataValueField = "City"
                    AutoPostback="true"
                    OnSelectedIndexChanged="cboCity_SelectedIndexChanged"  > 
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Select Hotel">
            <ItemTemplate>
                <asp:DropDownList ID="cboHotels" runat="server" Width="210px"
                    DataValueField ="ID"
                    DataTextField ="HotelName"
                    ></asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Our code to load is thus this:

Dim rstCity As New DataTable

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

    If Not IsPostBack Then
        LoadGrid
    End If
End Sub

Sub LoadGrid()

    ' load up City list for combo box
    rstCity = MyRst("SELECT City from City ORDER BY City")

    ' load up the grid
    GridView1.DataSource = MyRst("SELECT * from People ORDER BY FirstName")
    GridView1.DataBind()

End Sub

Public Function MyRst(strSQL As String) As DataTable

    Dim rstData As New DataTable

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function

And our result is this:

Ok, so we have this for our first city cbo box:

<asp:TemplateField HeaderText="Select Hotel City">
            <ItemTemplate>
                <asp:DropDownList ID="cboCity" runat="server" Width="120px"
                    DataTextField = "City"
                    DataValueField = "City"
                    AutoPostback="true"
                    OnSelectedIndexChanged="cboCity_SelectedIndexChanged"  > 
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>

So, on the index changed event for this combo, after we select the city, then we fill the 2nd combo box with hotels for that city.

That code looks like this:

Protected Sub cboCity_SelectedIndexChanged(sender As Object, e As EventArgs)

    Dim cboCity As DropDownList = sender
    ' get current grid row
    Dim gRow As GridViewRow = cboCity.NamingContainer

    ' filter hotels to current city
    Dim strCity As String = cboCity.SelectedItem.Text

    Dim strSQL = "SELECT * from tblHotels WHERE CITY = '" & strCity & "' ORDER BY HotelName"
    Dim cboHotels As DropDownList = gRow.FindControl("cboHotels")
    cboHotels.DataSource = MyRst(strSQL)
    cboHotels.DataBind()
    cboHotels.Items.Insert(0, New ListItem("", ""))

End Sub

So, on cbo change, we get the current row, get the 2nd cbo box, and then simply fill it out with ONLY a city list of hotels based on the first cbo box of city

There is of course some more moving parts we could (should add).

For example, while we save the hotel name (cbo box), we don't save the city value - we don't have to.

However, this means on FIRST grid load, in theory we have to:

Check if the hotel is loaded, and if yes, THEN SET the first combo box based on that selection. (or we could leave all city cbo blank on start up - but in theory, we should check for hotel, and if yes, then get the city for the hotel, and then set the city cbo box. This is actually more work then the above cascading code, since we have to query hotel to get the city, and then set the city cbo box based on that.

But, here is that code anyway - we put it in the row data bind event, since on first gv load, we have to setup the city cbo box (and it is not bound to any data - and note saved in the database - it is only a selector to filter hte hotel selection (which is saved in the database ).

So, that code - a bit messy, but in theory is required.

Protected Sub GridView1_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GridView1.RowDataBound

    If e.Row.RowType = DataControlRowType.DataRow Then

        ' get the data bind row data
        Dim gData As DataRowView = e.Row.DataItem
        ' load the city combo box - full city list
        Dim cboCity As DropDownList = e.Row.FindControl("cboCity")

        cboCity.DataSource = rstCity
        cboCity.DataBind()

        ' add blank row for city
        cboCity.Items.Insert(0, New ListItem("", ""))

        ' now load Hotel combo box (if we have one!!)
        Dim cboHotels As DropDownList = e.Row.FindControl("cboHotels")

        If Not IsDBNull(gData("Hotel_id")) Then

            ' get the one hotel record - we need the city from that hotel
            ' to set the city cbo

            Dim rstOneHotel As DataRow = MyRst("SELECT * From tblHotels where ID = " & gData("Hotel_id")).Rows(0)
            Dim strHotelCity As String = rstOneHotel("City")

            ' set the city filter cbo to correct city
            cboCity.SelectedValue = strHotelCity

            ' load hotel combo box only with current city list
            Dim strSQL As String = "Select ID,HotelName From tblHotels WHERE City = '" &
                                    strHotelCity & "' ORDER BY HotelName"
            cboHotels.DataSource = MyRst(strSQL)
            cboHotels.DataBind()
            ' set hotels combo to current selected
            cboHotels.SelectedValue = gData("Hotel_id")
        End If

    End If

End Sub