• How to Populate dropdownlist based on another dropdownlist inside a GridView

    Posted on September 21, 2012 by in ASP.NET, C#, Dotnet

    In this article, I am going to explain how to create conditional dynamic dropdown lists in a GridView row. First dropdown will be loaded based on the second dropdownlist selection. I am going to use AdventureWorks for this example. Each product belongs to a subcategory and each subcategory belongs to a category. When user tries to edit a gridview row, we render available categories and subcategories in dropdownlists.  Since each subcategory belongs to a category, changing a category will cause a post back and corresponding subcategories dropdown will be reloaded .

    Database Connection
    Open web.config and add following entry to connection strings element.

    <add name="Sql" connectionString="Data Source=(local); Initial Catalog= AdventureWorks;User=testuser;Password=testuser;" providerName="System.Data.SqlClient"/> 
    

    DataSource
    I am using SQLDataSource to select & update the products from/to database.  The SelectCommand property specifies the SQL statement to execute to retrieve the data. The UpdateCommand persists changes to the database. I have created some UpdateParameters which would be applied (set) in the Update event handler of the GridView.

    <asp:SqlDataSource ID="sqlDSProducts" runat="server" ConnectionString="<%$ ConnectionStrings:Sql %>"
            SelectCommand="Select top 10  P.ProductID, P.Name as ProductName, P.ProductNumber, ListPrice, 
                    PS.ProductSubCategoryID, PS.Name as SubCategoryName, PC.ProductCategoryID, PC.Name as CategoryName  
                    from Production.Product P
                    INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID
    		INNER JOIN Production.ProductCategory PC on PS.ProductCategoryID = PC.ProductCategoryID"
           UpdateCommand="UPDATE Production.Product SET [email protected],
                        [email protected], [email protected]
                         WHERE [email protected]">
        <UpdateParameters>
            <asp:Parameter Name="ProductName" Type="String" />            
            <asp:Parameter Name="Price"  Type="Decimal" />
            <asp:Parameter Name="ProductSubCategoryID"  Type="String" />
            <asp:Parameter Name="ProductID"  Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource>
    

    Page Design
    We are not going to allow user to edit ProductID & Product Number, so we use BoundColumns to display them. User is allowed to update Product Name, Product Number, Price, and sub category mapping.  When the grid is in view mode, we wrap these attributes in ITEM TEMPLATE.  When user grid is in edit mode, user can actually change the product name & its price, so we render those two items in text fields, however he cannot change the sub category name, he can only choose a sub category from the existing list meaning he can only change the mapping. We load categories and sub categories in  dropdown lists and user can select an item from them.  In order to bind the SQLDataSource created above to the grid, set the “DataSourceID” property of the GridView to “sqlDSProducts” (ID property value of SQLDataSource).

    User has to click on “Edit” button to enter into edit mode. While in edit mode, he can choose to perform two actions. He can change product attributes and click on “Update” to persist his changes to database or he can click on “Cancel” to go back to normal mode.

    <asp:gridview id="gvProducts"autogeneratecolumns="False" emptydatatext="No data available."               
        runat="server" DataKeyNames="ProductID" CssClass="GridViewStyle"
        OnRowEditing="gvProducts_RowEditing" DataSourceID="sqlDSProducts" OnRowCancelingEdit="gvProducts_RowCancelingEdit"         
        onrowupdating="gvProducts_RowUpdating" onrowdatabound="gvProducts_RowDataBound">
        <RowStyle CssClass="RowStyle" />              
        <FooterStyle CssClass="RowStyle" />                        
        <SelectedRowStyle CssClass="SelectedRowStyle" />    
        <HeaderStyle CssClass="HeaderStyle" />              
        <AlternatingRowStyle CssClass="AltRowStyle" />
        <EditRowStyle CssClass="EditRowStyle" />
        <Columns>
            <asp:BoundField DataField="ProductID" HeaderText="Product ID" ReadOnly="true">
                <HeaderStyle HorizontalAlign="Left"  Width="100px"/>
            </asp:BoundField>
            <asp:BoundField DataField="ProductNumber" HeaderText="Product Number" ReadOnly="true">
                <HeaderStyle HorizontalAlign="Left"  Width="150px"/>
            </asp:BoundField>                             
             <asp:TemplateField headertext="Product Name">
                <HeaderStyle HorizontalAlign="Left" Width="200px" />
                <ItemStyle HorizontalAlign="Left" Width="200px"/>
                <ItemTemplate> <%#Eval("ProductName")%></ItemTemplate>
                <EditItemTemplate>
                  <asp:TextBox id="txtProductName" text='<%# Eval("ProductName")%>' runat="server" Width="200px"/>                                             
                </EditItemTemplate>                    
            </asp:TemplateField>     
            <asp:TemplateField headertext="Sub Category">
                <HeaderStyle HorizontalAlign="Left" Width="100px" />
                <ItemStyle HorizontalAlign="Left" />
                <ItemTemplate> <%#Eval("SubCategoryName")%></ItemTemplate>
                <EditItemTemplate>
                 <asp:DropDownList ID="ddlSubCategories" runat="server"></asp:DropDownList>                                       
                </EditItemTemplate>                    
            </asp:TemplateField>
            <asp:TemplateField headertext="Category">
                <HeaderStyle HorizontalAlign="Left" Width="150px" />
                <ItemStyle HorizontalAlign="Left" />
                <ItemTemplate> <%#Eval("CategoryName")%></ItemTemplate>
                <EditItemTemplate>   
                    <asp:DropDownList ID="ddlCategories" runat="server" AutoPostBack="true"                           OnSelectedIndexChanged="ddlCategories_SelectedIndexChanged">                            
                    </asp:DropDownList>                 
                </EditItemTemplate>                    
            </asp:TemplateField>     
             <asp:TemplateField headertext="Price">
                <HeaderStyle HorizontalAlign="Right" Width="80" />
                <ItemStyle HorizontalAlign="Right" Width="80" />
                 <ItemTemplate> <%#Eval("ListPrice","{0:c}")%></ItemTemplate>
                 <EditItemTemplate>
                  <asp:TextBox id="txtListPrice" text='<%#Eval("ListPrice")%>' Width="80" runat="server"/>                                                                         
                </EditItemTemplate>                   
            </asp:TemplateField>                                 
                        <asp:TemplateField>
            <ItemTemplate>
               <asp:LinkButton ID="btnedit" runat="server" CommandName="Edit" Text="Edit"/>			       
            </ItemTemplate>
            <EditItemTemplate>
               <asp:LinkButton ID="btnupdate" runat="server" CommandName="Update" Text="Update" />
               <asp:LinkButton ID="btncancel" runat="server" CommandName="Cancel" Text="Cancel"/>			       
            </EditItemTemplate>              
            </asp:TemplateField>                                          
        </Columns>
    </asp:gridview>
    

    When user clicks on “Edit” button, OnRowEditing event is raised and gvProducts_RowEditing event handler routine is called. In this routine, we set the edit index of the grid to the row you trying to edit. Since it is a post back, we have to re-bind the grid control.  Whenever we try to bind data to the grid RowDataBound fires once for each row as its databound. If you want to handle any data binding related special cases, you do it here.  In the databound event handler, we fetch subcategories and categories from the database and bind them to ddlSubCategories & ddlCategories dropdowns. We default the selected value of the category dropdown to the current selected product’s category.

    protected void gvProducts_RowEditing(object sender,
                                            GridViewEditEventArgs e)
    {
        gvProducts.EditIndex = e.NewEditIndex;
        gvProducts.DataBind()
    }
    protected void gvProducts_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //check if is in edit mode
            if ((e.Row.RowState & DataControlRowState.Edit) > 0)
            {
                DataRowView drv = e.Row.DataItem as DataRowView;
                
                DropDownList ddlSubCategories = (DropDownList)e.Row.FindControl("ddlSubCategories");
                //Get Subcategories list based on selected product's category and bind it to 
                        //subcategory dropdown 
                ddlSubCategories.DataTextField = "SubCategoryName";
                ddlSubCategories.DataValueField = "ProductSubcategoryID";
                ddlSubCategories.DataSource = RetrieveSubCategories(drv["ProductCategoryID"].ToString());
                ddlSubCategories.DataBind();
                ddlSubCategories.SelectedValue = drv["ProductSubCategoryID"].ToString();
    
                DropDownList ddlCategories = (DropDownList)e.Row.FindControl("ddlCategories");
                //Bind categories data to dropdownlist 
                ddlCategories.DataTextField = "CategoryName";
                ddlCategories.DataValueField = "ProductCategoryID";
                ddlCategories.DataSource = RetrieveCategories();
                ddlCategories.DataBind();
    //set the selected value of the dropdown to selected product's category
                ddlCategories.SelectedValue = drv["ProductCategoryID"].ToString();
    
            }
        }
    }
    private DataTable RetrieveSubCategories(string categoryID)
    {
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        //SQL statement to fetch prodyct subcategories
        string sql = @"Select ProductSubcategoryID, Name as SubCategoryName 
            from Production.ProductSubcategory where ProductCategoryID = " + categoryID;
        DataTable dtSubCategories = new DataTable();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dtSubCategories);
            }
        }
        return dtSubCategories;
    
    }
    
    private DataTable RetrieveCategories()
    {
        //fetch the connection string from web.config
        string connString = ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        //SQL statement to fetch product categories
        string sql = @"SELECT PC.ProductCategoryID, PC.Name as CategoryName  FROM Production.ProductCategory PC";
        DataTable dtCategories = new DataTable();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dtCategories);
            }
        }
        return dtCategories;
    }
    

    When user selects an item from the category dropdown, OnSelectedIndexChanged event is raised.  In the ddlCategories_SelectedIndexChanged  eventhandler, we get the category id of the selected dropdown item. We get the list of subcategories belonging to this category and the list is bound to the ddlSubCategories dropdownlist of the row.

    protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e)
    {
        //since this event is raised by the control in the gridview row
        //we could do some reverse engineerign to get reference to the gridview row
        GridViewRow gvrow = (GridViewRow)((DropDownList)sender).NamingContainer;
    
        //get reference to the categories & subcategories dropdownlist
        DropDownList ddlCategories = (DropDownList)gvrow.FindControl("ddlCategories");
        DropDownList ddlSubCategories = (DropDownList)gvrow.FindControl("ddlSubCategories");
    
        //Get subcategories based on category selected and bind the list to subcategories dropdownlist 
        ddlSubCategories.DataTextField = "SubCategoryName";
        ddlSubCategories.DataValueField = "ProductSubcategoryID";
        ddlSubCategories.DataSource = RetrieveSubCategories(ddlCategories.SelectedValue);
        ddlSubCategories.DataBind();
    }
    

    When you click on “Update” button, gvProducts_RowUpdating event is fired. Based on the selected row, get the gridviewrow. Once you get the gridviewrow, you could search for the content controls and get the updated values. Once we have the latest values, we set those values to update parameters of the sqlDsProducts. Finally, we simply need to call Update function of sqlDsProducts  to perisist changes to database.

    protected void gvProducts_RowUpdating(Object sender,
                                            GridViewUpdateEventArgs e)
    {
        // Get the product id of the selected product    
        string productID = gvProducts.DataKeys[e.RowIndex].Value.ToString();
    
        // Get the GridViewRow object that represents the row being edited
        // from the Rows collection of the GridView control.            
        GridViewRow row = gvProducts.Rows[e.RowIndex];
    
        // Get the controls that contain the updated values. In this
        // example, the updated values are contained in the TextBox 
        // controls declared in the edit item templates of each TemplateField 
        // column fields in the GridView control.            
        TextBox txtProductName = (TextBox)row.FindControl("txtProductName");
        DropDownList ddlSubCategories = (DropDownList)row.FindControl("ddlSubCategories");
        TextBox txtListPrice = (TextBox)row.FindControl("txtListPrice");
        //update the product 
    
        sqlDSProducts.UpdateParameters["ProductName"].DefaultValue = txtProductName.Text;            
        sqlDSProducts.UpdateParameters["Price"].DefaultValue = txtListPrice.Text;
        sqlDSProducts.UpdateParameters["ProductSubCategoryID"].DefaultValue = ddlSubCategories.SelectedValue;
        sqlDSProducts.UpdateParameters["ProductID"].DefaultValue = productID.ToString();
        sqlDSProducts.Update();
        
        gvProducts.EditIndex = -1;            
        gvProducts.DataBind();
    }
    

    Run the application and top 10 products are loaded from the database.
    populate dropdownlist based on another dropdownlist 1

    Click on the “Edit” button and gridview enters into edit mode. You will see all available subcategories & categories from database are loaded into.
    populate dropdownlist based on another dropdownlist 2

    Select “Accessories” category and since autopostback is set to true for the dropdownlist, request is sent to the server and all subcategories belonging to “Accessories” are loaded into the subcategories dropdown.
    populate dropdownlist based on another dropdownlist 3

    Select “Fenders” from the subcategory drodown. Change list price to 500 and click on “Update” button and changes will be persisted to the database.
    populate dropdownlist based on another dropdownlist 4

    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply