In this article I will explain how to edit and update multiple rows in ASP.Net GridView using CheckBoxes i.e. the Rows which are checked will become editable and user can update multiple rows on one single Update button click.
Database and Connection string
For this sample to work you will need to download the Microsoft Northwind database using the following link
Below is the connection string from the Web.Config file
<connectionStrings>
<addname="constring"connectionString="Data Source=.\SQL2005;Initial Catalog=NorthWind;integrated security=true"/>
</connectionStrings>
HTML Markup
In the below HTML Markup I have a simple ASP.Net GridView with 3 columns. First column containing the CheckBox, second column containing a Label and TextBox for display and edit Contact Name of the Customer respectively and the third column containing a Label and DropDownList for display and edit Country of the Customer respectively
Also I have used DataKeyNames property to store the primary key i.e. CustomerId
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" OnRowDataBound = "OnRowDataBound" DataKeyNames = "CustomerId">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID = "chkAll" runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Contact Name" ItemStyle-Width = "150">
<ItemTemplate>
<asp:Label runat="server" Text='<%# Eval("ContactName") %>'></asp:Label>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("ContactName") %>' Visible="false"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" ItemStyle-Width = "150">
<ItemTemplate>
<asp:Label ID = "lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
<asp:DropDownList ID="ddlCountries" runat="server" Visible = "false">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick = "Update" Visible = "false"/>
Namespaces
You will need to import the following namespaces
C#
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Linq
Imports System.Configuration
Imports System.Data.SqlClient
Binding the ASP.Net GridView
Below is the code to bind the ASP.Net GridView control with records from the Customers table of the Northwind database.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
SqlCommand cmd = new SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers");
gvCustomers.DataSource = this.ExecuteQuery(cmd, "SELECT");
gvCustomers.DataBind();
}
private DataTable ExecuteQuery(SqlCommand cmd, string action)
{
string conString = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
cmd.Connection = con;
switch (action)
{
case "SELECT":
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
case "UPDATE":
con.Open();
cmd.ExecuteNonQuery();
con.Close();
break;
}
return null;
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim cmd As New SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers")
gvCustomers.DataSource = Me.ExecuteQuery(cmd, "SELECT")
gvCustomers.DataBind()
End Sub
Private Function ExecuteQuery(cmd As SqlCommand, action As String) As DataTable
Dim conString As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
Using con As New SqlConnection(conString)
cmd.Connection = con
Select Case action
Case "SELECT"
Using sda As New SqlDataAdapter()
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
Case "UPDATE"
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Exit Select
End Select
Return Nothing
End Using
End Function
Populating the Country DropDownList in the ASP.Net GridView Row
In the OnRowDataBoundevent of the ASP.Net GridView I am populating the Country DropDownList which will be displayed when a row is edited.
C#
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
SqlCommand cmd = new SqlCommand("SELECT DISTINCT(Country) FROM Customers");
DropDownList ddlCountries = (e.Row.FindControl("ddlCountries") as DropDownList);
ddlCountries.DataSource = this.ExecuteQuery(cmd, "SELECT");
ddlCountries.DataTextField = "Country";
ddlCountries.DataValueField = "Country";
ddlCountries.DataBind();
string country = (e.Row.FindControl("lblCountry") as Label).Text;
ddlCountries.Items.FindByValue(country).Selected = true;
}
}
VB.Net
Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim cmd As New SqlCommand("SELECT DISTINCT(Country) FROM Customers")
Dim ddlCountries As DropDownList = TryCast(e.Row.FindControl("ddlCountries"), DropDownList)
ddlCountries.DataSource = Me.ExecuteQuery(cmd, "SELECT")
ddlCountries.DataTextField = "Country"
ddlCountries.DataValueField = "Country"
ddlCountries.DataBind()
Dim country As String = TryCast(e.Row.FindControl("lblCountry"), Label).Text
ddlCountries.Items.FindByValue(country).Selected = True
End If
End Sub
Switching the ASP.Net GridView Row to Edit mode on CheckBox checked
The below event handler is executed when the CheckBox is the ASP.Net GridView Row is checked or unchecked and when the Check All CheckBox in the ASP.Net GridView Header Row is checked or unchecked.
Here I am looping through the GridView Rows and checking whether the CheckBox is checked for that Row. If the CheckBox is checked then the Label control in the GridView Cell is hidden and the corresponding TextBox or DropDownList is made visible.
Finally I make sure that the Update button btnUpdate is only visible when at least one checkbox is checked.
C#
protected void OnCheckedChanged(object sender, EventArgs e)
{
bool isUpdateVisible = false;
CheckBox chk = (sender as CheckBox);
if (chk.ID == "chkAll")
{
foreach (GridViewRow row in gvCustomers.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked = chk.Checked;
}
}
}
CheckBox chkAll = (gvCustomers.HeaderRow.FindControl("chkAll") as CheckBox);
chkAll.Checked = true;
foreach (GridViewRow row in gvCustomers.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
for (int i = 1; i < row.Cells.Count; i++)
{
row.Cells[i].Controls.OfType<Label>().FirstOrDefault().Visible = !isChecked;
if (row.Cells[i].Controls.OfType<TextBox>().ToList().Count > 0)
{
row.Cells[i].Controls.OfType<TextBox>().FirstOrDefault().Visible = isChecked;
}
if (row.Cells[i].Controls.OfType<DropDownList>().ToList().Count > 0)
{
row.Cells[i].Controls.OfType<DropDownList>().FirstOrDefault().Visible = isChecked;
}
if (isChecked && !isUpdateVisible)
{
isUpdateVisible = true;
}
if (!isChecked )
{
chkAll.Checked = false;
}
}
}
}
btnUpdate.Visible = isUpdateVisible;
}
VB.Net
Protected Sub OnCheckedChanged(sender As Object, e As EventArgs)
Dim isUpdateVisible As Boolean = False
Dim chk As CheckBox = TryCast(sender, CheckBox)
If chk.ID = "chkAll" Then
For Each row As GridViewRow In gvCustomers.Rows
If row.RowType = DataControlRowType.DataRow Then
row.Cells(0).Controls.OfType(Of CheckBox)().FirstOrDefault().Checked = chk.Checked
End If
Next
End If
Dim chkAll As CheckBox = TryCast(gvCustomers.HeaderRow.FindControl("chkAll"), CheckBox)
chkAll.Checked = True
For Each row As GridViewRow In gvCustomers.Rows
If row.RowType = DataControlRowType.DataRow Then
Dim isChecked As Boolean = row.Cells(0).Controls.OfType(Of CheckBox)().FirstOrDefault().Checked
For i As Integer = 1 To row.Cells.Count - 1
row.Cells(i).Controls.OfType(Of Label)().FirstOrDefault().Visible = Not isChecked
If row.Cells(i).Controls.OfType(Of TextBox)().ToList().Count > 0 Then
row.Cells(i).Controls.OfType(Of TextBox)().FirstOrDefault().Visible = isChecked
End If
If row.Cells(i).Controls.OfType(Of DropDownList)().ToList().Count > 0 Then
row.Cells(i).Controls.OfType(Of DropDownList)().FirstOrDefault().Visible = isChecked
End If
If isChecked AndAlso Not isUpdateVisible Then
isUpdateVisible = True
End If
If Not isChecked Then
chkAll.Checked = False
End If
Next
End If
Next
btnUpdate.Visible = isUpdateVisible
End Sub
Updating the edited records in ASP.Net GridView Row
The below event handler is executed when the Update button btnUpdate is clicked.
Here I am looping through the GridView Rows and checking whether the CheckBox is checked for that Row. If the CheckBox is checked then the edited values from the TextBox and the DropDownList controls are added to the SQL Parameters and the record is updated in the database based on the CustomerId in the DataKeyNames property.
C#
protected void Update(object sender, EventArgs e)
{
foreach (GridViewRow row in gvCustomers.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
if (isChecked)
{
SqlCommand cmd = new SqlCommand("UPDATE Customers SET ContactName = @ContactName, Country = @Country WHERE CustomerId = @CustomerId");
cmd.Parameters.AddWithValue("@ContactName", row.Cells[1].Controls.OfType<TextBox>().FirstOrDefault().Text);
cmd.Parameters.AddWithValue("@Country", row.Cells[2].Controls.OfType<DropDownList>().FirstOrDefault().SelectedItem.Value);
cmd.Parameters.AddWithValue("@CustomerId", gvCustomers.DataKeys[row.RowIndex].Value);
this.ExecuteQuery(cmd, "SELECT");
}
}
}
btnUpdate.Visible = false;
this.BindGrid();
}
VB.Net
Protected Sub Update(sender As Object, e As EventArgs)
For Each row As GridViewRow In gvCustomers.Rows
If row.RowType = DataControlRowType.DataRow Then
Dim isChecked As Boolean = row.Cells(0).Controls.OfType(Of CheckBox)().FirstOrDefault().Checked
If isChecked Then
Dim cmd As New SqlCommand("UPDATE Customers SET ContactName = @ContactName, Country = @Country WHERE CustomerId = @CustomerId")
cmd.Parameters.AddWithValue("@ContactName", row.Cells(1).Controls.OfType(Of TextBox)().FirstOrDefault().Text)
cmd.Parameters.AddWithValue("@Country", row.Cells(2).Controls.OfType(Of DropDownList)().FirstOrDefault().SelectedItem.Value)
cmd.Parameters.AddWithValue("@CustomerId", gvCustomers.DataKeys(row.RowIndex).Value)
Me.ExecuteQuery(cmd, "SELECT")
End If
End If
Next
btnUpdate.Visible = False
Me.BindGrid()
End Sub
Screenshot
Demo
Downloads
