• Use DropDownList in ASP.NET GridView Control

    Posted on April 5, 2012 by in ASP.NET, C#, Dotnet

     
    In this article, I am going to demonstrate how to bind drop down list to a grid view control in edit mode.  I am not going to cover any basics in this article. If you are new to using grid view or if you would like know more about grid view, I would recommend you to reach following tutorials: 

    Using GridView in ASP.NET & C# —PART 1

    Using GridView in ASP.NET & C# —PART 2

    How to Insert, Update & Delete rows in ASP.NET GridView Control

    Overview:

    I am using Adventure Works as the data source. When page is initially loaded, We fetch products from Production.Product table and bind them to the grid. We display some product attributes such as Product ID, Product Number, Product Name, Product Subcategory Name and List Price. When user clicks on edit, grid enters into edit mode. We mark Product Name, Product Sub Category & List price as editable fields in the design. In case of Product Sub Category, we load the list of all product categories available in the Production.ProductionSubcategory table into a drop down list and are available for user selection.

    Database Connection

    Create a new ASP.NET Web Application project. 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"/> 

    Page Design

    Create a new ASP.NET web page. Rename Default.aspx to EditProductsView.aspx and update any references. We don’t want a fancy grid, so just applying some basic formatting. As you can see we are tapping into four events of grid view to satisfy this requirement. I will discuss about these events later.

    <asp:gridview id="gvProducts"          
    autogeneratecolumns="False"
    BorderColor="White" BorderStyle="Ridge"
    CellSpacing="1" CellPadding="3" GridLines="None" 
    BackColor="White" BorderWidth="2px"
    emptydatatext="No data available."               
    runat="server" DataKeyNames="ProductID"
    OnRowEditing="gvProducts_RowEditing"
    OnRowCancelingEdit="gvProducts_RowCancelingEdit"         
    onrowupdating="gvProducts_RowUpdating"
    onrowdatabound="gvProducts_RowDataBound">

    We are not going to allow user to edit ProductID & Product Number, so we use BoundColumns to display them.

    <Columns>
    <asp:BoundField DataField="ProductID" HeaderText="Product ID" 
                      ReadOnly="true">
        </asp:BoundField>
    <asp:BoundField DataField="ProductNumber" HeaderText="Product Number" 
                        ReadOnly="true">
    </asp:BoundField>     
    ……………
    …………..

    User is allowed to update Product Name, Product Number, Price and the 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 change the mapping. We load sub categories in a drop down list and user can select an item from it.

    ….
    ……………….
    <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"/>                                             
        </EditItemTemplate>                    
    </asp:TemplateField>     
    <asp:TemplateField headertext="Sub Category">
        <HeaderStyle HorizontalAlign="Left" Width="150px" />
        <ItemStyle HorizontalAlign="Left" />
        <ItemTemplate> <%#Eval("SubCategoryName")%></ItemTemplate>
        <EditItemTemplate>
         <asp:DropDownList ID="ddlSubCategories" runat="server" >                
            </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>       
    </Columns>

    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: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>      
    </asp:gridview>

    Implementation:

    When page is first requested, Page_Load event is one of the events that get fired on the server side. If the load is not because of any web control event, we fetch products from database and will save it in the Viewstate for subsequent page refreshes.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            BindData();
    }
    private void BindData()
    {
        //Bind the grid view
        gvProducts.DataSource = RetrieveProducts();
        gvProducts.DataBind();
    }
    private DataSet RetrieveProducts()
    {
        if (ViewState["Products"] != null)
            return (DataSet)ViewState["Products"];
        //fetch the connection string from web.config
        string connString = 
          ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        //SQL statement to fetch entries from products
        string sql = @"Select top 10  P.ProductID, P.Name as ProductName, 
                        P.ProductNumber, ListPrice, PS.ProductSubCategoryID, 
                        ps.Name as SubCategoryName  
                        from Production.Product P
                        INNER JOIN Production.ProductSubcategory PS 
                        ON P.ProductSubcategoryID = PS.ProductSubcategoryID";
        DataSet dsProducts = new DataSet();
        //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(dsProducts);
            }
        }
        ViewState["Products"] = dsProducts;
        return dsProducts;
    }

    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. We load subcategories into a dropdownlist. We get the list of available sub categories from Production.ProductSubcategory and will bind it to drop down list.

    protected void gvProducts_RowEditing(object sender,
                                            GridViewEditEventArgs e)
    {
        gvProducts.EditIndex = e.NewEditIndex;
        BindData();
    }
    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)
            {
                DropDownList ddlSubCategories = 
                          (DropDownList)e.Row.FindControl("ddlSubCategories");
                //Bind subcategories data to dropdownlist 
                ddlSubCategories.DataTextField = "SubCategoryName";
                ddlSubCategories.DataValueField = "ProductSubcategoryID";
                ddlSubCategories.DataSource = RetrieveSubCategories();                    
                ddlSubCategories.DataBind();
                DataRowView dr = e.Row.DataItem as DataRowView;
                ddlSubCategories.SelectedValue = 
                             dr["ProductSubCategoryID"].ToString();
            }
        }
    }
    private DataTable RetrieveSubCategories()
    {
       //fetch the connection string from web.config
        string connString = 
                ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        //SQL statement to fetch entries from products
        string sql = @"Select ProductSubcategoryID, Name as SubCategoryName 
                    from Production.ProductSubcategory";
        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;
    }

    Change sub category to “Chains” & price to $50. Click on “Update” link button and gvProducts_RowUpdating event is called. We get the values entered/changed by the user and will persist them into 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 txtProductNumber = (TextBox)row.FindControl("txtProductNumber");
        TextBox txtProductName = (TextBox)row.FindControl("txtProductName");
        DropDownList ddlSubCategories = 
                          (DropDownList)row.FindControl("ddlSubCategories");
        TextBox txtListPrice = (TextBox)row.FindControl("txtListPrice");
        //update the product 
        UpdateProduct(productID, txtProductName.Text,
                                    ddlSubCategories.SelectedValue,
                                    txtListPrice.Text);
        gvProducts.EditIndex = -1;
        ViewState["Products"] = null;
        BindData();
    }
    Private void UpdateProduct(string productID,
                    string productName, string subCategoryID, string listPrice)
    {
    //fetch the connection string from web.config
    string connString =
           ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    //SQL statement to update a product
    string sql = String.Format(@"Update Production.Product set Name='{0}'                                    
                            ,ProductSubcategoryID={1}
                            ,ListPrice ={2} 
                            where ProductID = {3}",
                                    productName,
                                    subCategoryID,
                                    listPrice, productID);
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        //Initialize command object
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }

    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]

    3 Responsesso far.

    1. christoan says:

      Cool, I like your work. Please I need your help. I want to retrieve a row from a gridview when a user enters his role number in a textfield. How can I achieve this? I’ll gladly appreciate tour help. Ciao

    2. Alan says:

      Thanks, man. This info really helped me.

    3. TanThiLuong says:

      Thank. This really need.

    Leave a Reply