Binding DropDownList in GridView

Northwind Person

Image credit: https://northwinddatabase.codeplex.com

[You can download the NorthWind database having the table Person from the above link]

Binding DropDownList in GridView can be easily done at the GridView’s RowDataBound event. This event is raised when the row inside the GridView is bounded to the data.
This event gives us the flexibility to bind the data or the data container (DropDownList in our case) of our choice to the row(s) of the GridView to enhance its functionality.

We have a GridView , which is bounded with the Products table of NorthWind database. This GridView has a DropdownList which contains the QuantityPerUnit column values from the same table. Here’s how we will bind as well as access the values of the DropDownList and Edit the QuantityPerUnit column:

web.config file

<?xml version=”1.0″?>

<configuration>
<connectionStrings>
<add name=”MyConn” connectionString=”Data Source=ADEEL-PC;Initial Catalog=NORTHWND;Integrated Security=True” providerName=”System.Data.SqlClient”/>
</connectionStrings>
<system.web>
<compilation debug=”true” targetFramework=”4.5″/>
<httpRuntime targetFramework=”4.5″/>
</system.web>
</configuration>

Default.aspx Markup

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>

<!DOCTYPE html>

<html xmlns=”http://www.w3.org/1999/xhtml”>
<head runat=”server”>
<title></title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:GridView ID=”GridView1″ runat=”server” AllowPaging=”True” AutoGenerateColumns=”False” CellPadding=”4″ ForeColor=”#333333″ GridLines=”None” HorizontalAlign=”Center” PageSize=”5″ DataKeyNames=”ProductID” OnRowDataBound=”GridView1_RowDataBound” OnRowCancelingEdit=”GridView1_RowCancelingEdit” OnRowEditing=”GridView1_RowEditing” OnRowUpdating=”GridView1_RowUpdating” OnSelectedIndexChanged=”GridView1_SelectedIndexChanged” OnRowUpdated=”GridView1_RowUpdated” OnPageIndexChanging=”GridView1_PageIndexChanging”>
<AlternatingRowStyle BackColor=”White” />
<Columns>
<asp:CommandField ShowEditButton=”True” />
<asp:BoundField ReadOnly=”true” DataField=”ProductID” HeaderText=”ProductID” SortExpression=”ProductID” />
<asp:BoundField ReadOnly=”true” DataField=”ProductName” HeaderText=”ProductName” SortExpression=”ProductName” />
<asp:BoundField ReadOnly=”true” DataField=”SupplierID” HeaderText=”SupplierID” SortExpression=”SupplierID” />
<asp:BoundField ReadOnly=”true” DataField=”CategoryID” HeaderText=”CategoryID” SortExpression=”CategoryID” />
<asp:BoundField DataField=”UnitPrice” HeaderText=”UnitPrice” SortExpression=”UnitPrice” ReadOnly=”true”/>
<asp:TemplateField HeaderText=”QuantityPerUnit” SortExpression=”QuantityPerUnit”>
<EditItemTemplate>
<asp:DropDownList ID=”DropDownList1″ runat=”server”></asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”lblQuantityPerUnit” runat=”server” Text='<% # Bind(“QuantityPerUnit”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>

</Columns>
<FooterStyle BackColor=”#990000″ Font-Bold=”True” ForeColor=”White” />
<HeaderStyle BackColor=”#990000″ Font-Bold=”True” ForeColor=”White” />
<PagerStyle BackColor=”#FFCC66″ ForeColor=”#333333″ HorizontalAlign=”Center” />
<RowStyle BackColor=”#FFFBD6″ ForeColor=”#333333″ />
<SelectedRowStyle BackColor=”#FFCC66″ Font-Bold=”True” ForeColor=”Navy” />
<SortedAscendingCellStyle BackColor=”#FDF5AC” />
<SortedAscendingHeaderStyle BackColor=”#4D0000″ />
<SortedDescendingCellStyle BackColor=”#FCF6C0″ />
<SortedDescendingHeaderStyle BackColor=”#820000″ />
</asp:GridView>
<br />
</div>
</form>
</body>
</html>

Default.aspx.cs Code-behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“MyConn”].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();

}
}

protected void BindGridView()
{
SqlDataAdapter da = new SqlDataAdapter(“SELECT [ProductName], [SupplierID], [CategoryID], [ProductID]”+
” , [QuantityPerUnit], [UnitPrice] FROM Products”,con);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{

DropDownList ddl = (DropDownList)e.Row.FindControl(“DropDownList1”);
if(ddl!=null)
{

if (e.Row.RowType == DataControlRowType.DataRow)
{

try
{

con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = “Select ProductID,QuantityPerUnit FROM Products”;
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

if (dr.Read())
{

ddl.DataSource = dr;
ddl.DataTextField = “QuantityPerUnit”;
ddl.DataValueField = “ProductID”;
ddl.DataBind();

}

dr.Close();
con.Close();

}

catch (SqlException ex)
{

}

}
}

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGridView();

}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGridView();

}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{

GridViewRow row = GridView1.Rows[e.RowIndex];
DropDownList ddlEdit = (DropDownList)row.Cells[6].FindControl(“DropDownList1”);

con.Open();
SqlCommand cmdUpdate = new SqlCommand();
cmdUpdate.Connection = con;
cmdUpdate.CommandType = CommandType.Text;
cmdUpdate.CommandText = “Update Products Set [email protected] Where [email protected]”;
cmdUpdate.Parameters.AddWithValue(“@ProductID”,int.Parse(row.Cells[1].Text.Trim()) );
cmdUpdate.Parameters.AddWithValue(“@QuantityPerUnit”, ddlEdit.SelectedItem.Text);

cmdUpdate.ExecuteNonQuery();
con.Close();
GridView1.EditIndex = -1;
BindGridView();

}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
BindGridView();
}
}

Here is the link to download the Northwind Database

ListView Control

If you want more control over the design aspects in asp.net data bound controls then ListView is the best resort

http://www.codeproject.com/Articles/24570/Complete-ListView-in-ASP-NET

Syed Adeel Ahmed
Syed Adeel Ahmed
Analyst, Programmer, Educationist and Blogger at Technofranchise
Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.

Published by

Syed Adeel Ahmed

Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.