Use DropDownList in ASP.NET GridView Control

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();

}

}

 

Source: technico.qnownow.com