Introduction: In this article i
will explain How to delete multiple selected records/items from GridView based
on Checkbox selection while paging is enabled in asp.net with both the C# and
VB.Net language and using stored procedure and Sql Server as a back end database.
 |
| Click on the image to view enlarged demo |
Description: Basically you will
learn the following through this article.
- How to bind GridView from sql server database table using stored procedure
- How to implement Check/Uncheck All checkbox feature using javascript to delete all the records from the gridview
- How to show/implement checkbox in gridview rows to delete the selected records.
- How to preserve checkbox state on paging using viewstate
- How to maintain checkbox selections when paging is enabled using viewstate.
In previous article i explained How to Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and How to Bind,save,edit,update,delete records from DataList and Send email to multiple users based on CheckBox selection inside GridView and Validate asp.net CheckBoxList using jQuery and Validate CheckBoxList using JavaScript .
Implementation: Let's create an
asp.net application to see the work in action.
- First of all create a database in Sql server e.g. Emp_DB and create a table with the columns and data type as shown below and name it "Emp_Tb"
|
Column
Name
|
Data
Type
|
|
Emp_Id
|
Int(Primary Key so set Is Identity=True)
|
|
EmpName
|
varchar(100)
|
|
Age
|
Int
|
|
Salary
|
decimal(10, 2)
|
|
City
|
varchar(100)
|
|
Address
|
varchar(500)
|
- Create a stored procedure to fetch the employee records and bind the Grid View
CREATE PROCEDURE BindEmpGrid_Sp
AS
BEGIN
SELECT * FROM Emp_Tb
END
- Create a stored procedure to delete the employee record.
CREATE PROCEDURE DeleteEmpRecord_Sp
@EmpId INT
AS
BEGIN
DELETE FROM Emp_Tb WHERE
Emp_Id=@EmpId
END
- In the web.config file create the connection string to connect the asp.net application with the Sql server database.
<connectionStrings>
<add name="con" connectionString="Data Source=Lalit;Initial Catalog=Emp_DB;Integrated
Security=True"/>
</connectionStrings>
Note: Replace the Data Source and
Initial catalog (i.e. Database name) as per your application.
- In the <Head> tag of the design page e.g. default.aspx, create or copy paste the following java script function to implement the check all/uncheck all checkbox on Gridview header.
<script type = "text/javascript">
function grdHeaderCheckBox(objRef) {
var grd =
objRef.parentNode.parentNode.parentNode;
var inputList = grd.getElementsByTagName("input");
for (var
i = 0; i < inputList.length; i++) {
var row =
inputList[i].parentNode.parentNode;
if (inputList[i].type == "checkbox" && objRef !=
inputList[i]) {
if
(objRef.checked) {
inputList[i].checked = true;
}
else
{
inputList[i].checked = false;
}
}
}
}
</script>
- In the <Body> tag of the default.aspx ,design the page as:
Source Code:
<fieldset style="width:380px;">
<legend>Delete multiple records from gridview example</legend>
<asp:GridView ID="grdEmp" runat="server"
AllowSorting="True"
ShowFooter="true"
EmptyDataText="No
records found"
DataKeyNames="Emp_Id" CssClass="rowHover" RowStyle-CssClass="rowHover" ShowHeader="true"
AutoGenerateColumns="False"
AllowPaging="True"
onpageindexchanging="grdEmp_PageIndexChanging"
PageSize="5"
CellPadding="4"
ForeColor="#333333"
GridLines="None">
<AlternatingRowStyle
BackColor="White"
ForeColor="#284775"
/>
<Columns>
<asp:TemplateField HeaderStyle-HorizontalAlign="Center"
ItemStyle-HorizontalAlign="Center">
<HeaderTemplate>
<asp:CheckBox ID="chkAll" runat="server"
Text="All"
onclick =
"grdHeaderCheckBox(this);" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkRow" runat="server"
/>
</ItemTemplate>
<FooterTemplate>
<asp:Button ID="btnDelete"
runat="server"
Text="Multiple
Delete"
OnClientClick="return
confirm('Are you sure you want to delete selected records?')"
onclick="btnDelete_Click"
/>
</FooterTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="Emp
Name" DataField="EmpName" />
<asp:BoundField HeaderText="Age"
DataField="Age"
/>
<asp:BoundField HeaderText="Salary"
DataField="Salary"
/>
<asp:BoundField HeaderText="Address"
DataField="Address"
/>
</Columns>
<EditRowStyle
BackColor="#999999"
/>
<FooterStyle
BackColor="#ffffff"
Font-Bold="True"
ForeColor="White"
/>
<HeaderStyle
BackColor="#5D7B9D"
Font-Bold="True"
ForeColor="White"
/>
<PagerStyle
BackColor="#284775"
ForeColor="White"
HorizontalAlign="Center"
/>
<RowStyle CssClass="rowHover"
BackColor="#F7F6F3"
ForeColor="#333333"></RowStyle>
<SelectedRowStyle
BackColor="#E2DED6"
Font-Bold="True"
ForeColor="#333333"
/>
<SortedAscendingCellStyle
BackColor="#E9E7E2"
/>
<SortedAscendingHeaderStyle
BackColor="#506C8C"
/>
<SortedDescendingCellStyle
BackColor="#FFFDF8"
/>
<SortedDescendingHeaderStyle
BackColor="#6F8DAE"
/>
<EmptyDataRowStyle
Width = "550px" ForeColor="Red" Font-Bold="true"
HorizontalAlign = "Center"/>
</asp:GridView>
</fieldset>
Note:
In the very first line of the default.aspx page set the EnableEventValidation="false"
So
that it look like <%@ Page Language="C#"
AutoEventWireup="true"
EnableEventValidation="false" CodeFile="Default.aspx.cs" Inherits="_Default" %>
How to maintain the state of the
checkbox while paging is enabled?
Suppose paging is enabled in the
gridview and there are 10 records per page and when you select some records
from first page and got to next page and select some more records. Now when you
come back on first page then the selected checkbox are unchecked i.e. the state of the checkbox are lost. This is due
to fact that checkbox does not maintain state on navigation if paging is
enabled. So to save the selected checkbox of all the pages i have created two
functions:
- SaveSelectedData: This function is to retrieve the selected records i.e. the records that are checked by the checkbox, adds them to an ArrayList and then saves the ArrayList to ViewState.
- SetSelectedData: This function is to restore the saved state of the checkboxes from the ViewState.
C#.Net code to Delete multiple records from asp.net grid view with check box selection
- In the page behind file (default.aspx.cs) write the code as:
First of all include the required
namespaces and write the code as:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
protected void
Page_Load(object sender, EventArgs e)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (Page.IsPostBack)
{
SaveSelectedData();
}
BindEmpGrid();
}
#region
"Bind Employee records in GridView"
private void
BindEmpGrid()
{
SqlDataAdapter adp = new
SqlDataAdapter();
DataTable dt = new
DataTable();
try
{
adp = new SqlDataAdapter("BindEmpGrid_Sp", con);
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
grdEmp.DataSource = dt;
grdEmp.DataBind();
}
else
{
grdEmp.DataSource = null;
grdEmp.DataBind();
}
}
catch (Exception
ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(),
"Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
con.Close();
adp.Dispose();
}
}
#endregion
#region
"GridView Paging"
protected void
grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdEmp.PageIndex = e.NewPageIndex;
grdEmp.DataBind();
SetSelectedData();
}
#endregion
private void
SaveSelectedData()
{
ArrayList arrLst;
//Check the ViewState. If it has data then add to arraylist
if (ViewState["CheckedRecords"]
!= null)
arrLst = (ArrayList)ViewState["CheckedRecords"];
else
arrLst = new ArrayList();
//Find the "All" checkbox from the GridView
Header
CheckBox chkAll = (CheckBox)grdEmp.HeaderRow.Cells[0].FindControl("chkAll");
for (int i = 0; i
< grdEmp.Rows.Count; i++)
{
Int32 Emp_Id;
//check if the "All" checkbox is
checked or not
if (chkAll.Checked)
{
// If
checked, then if the arraylist doesn't contain the Emp_Id then add it to the
arraylist.
Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
if (!arrLst.Contains(Emp_Id))
{
arrLst.Add(Emp_Id);
}
}
// When "All" checkbox not checked
else
{
//Find the checkbox from the GridView rows
CheckBox chkRow = (CheckBox)grdEmp.Rows[i].Cells[0].FindControl("chkRow");
//check if the checkbox inside the gridview
rows is checked or not
if (chkRow.Checked)
{
//
If checked, then if the arraylist doesn't contain the Emp_Id then add it to the
arraylist.
Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
if
(!arrLst.Contains(Emp_Id))
{
//add Emp_id in Array list
arrLst.Add(Emp_Id);
}
}
// When checkbox inside gridview rows is not
checked
else
{
Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
// if
array list contains the Emp_Id
if
(arrLst.Contains(Emp_Id))
{
//Remove Emp_id from Array list
arrLst.Remove(Emp_Id);
}
}
}
}
//Storing the contents of array list i.e. Emp_id into
ViewState.
ViewState["CheckedRecords"]
= arrLst;
}
private void
SetSelectedData()
{
// int currentCount
= 0;
//Find the "All" checkbox from the GridView
Header
CheckBox chkAll = (CheckBox)grdEmp.HeaderRow.Cells[0].FindControl("chkAll");
chkAll.Checked = true;
// adding Checked records from ViewState to Arraylist
ArrayList arrLst = (ArrayList)ViewState["CheckedRecords"];
for (int i = 0; i
< grdEmp.Rows.Count; i++)
{
//Find the checkbox from the GridView Rows
CheckBox chkRow = (CheckBox)grdEmp.Rows[i].Cells[0].FindControl("chkRow");
if
(chkRow != null)
{
Int32 Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
chkRow.Checked = arrLst.Contains(Emp_Id);
if (!chkRow.Checked)
{
chkAll.Checked = false;
}
}
}
}
#region
"Delete multiple selected records"
protected void
btnDelete_Click(object sender, EventArgs e)
{
SqlCommand cmd = new
SqlCommand();
try
{
SetSelectedData();
grdEmp.AllowPaging = false;
grdEmp.DataBind();
ArrayList arrLst = (ArrayList)ViewState["CheckedRecords"];
for (int
i = 0; i < grdEmp.Rows.Count; i++)
{
Int32 Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
if (arrLst.Contains(Emp_Id))
{
cmd = new SqlCommand("DeleteEmpRecord_Sp", con);
cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value
= Emp_Id;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
arrLst.Remove(Emp_Id);
con.Close();
}
}
ViewState["CheckedRecords"]
= arrLst;
grdEmp.AllowPaging = true;
ScriptManager.RegisterClientScriptBlock(Page,
Page.GetType(), Guid.NewGuid().ToString(), "alert('Selected Records has been deleted
successfully');", true);
BindEmpGrid();
}
catch (Exception
ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(),
"Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
cmd.Dispose();
}
}
#endregion
VB.Net Code to
- In the code behind file (default.aspx.vb) write the code as:
First import the following
required namespaces and write the code as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
Protected Sub Page_Load(sender
As Object, e As System.EventArgs)
Handles Me.Load
If con.State = ConnectionState.Closed
Then
con.Open()
End If
If Page.IsPostBack Then
SaveSelectedData()
End If
BindEmpGrid()
End Sub
#Region "Bind Employee
records in GridView"
Private Sub
BindEmpGrid()
Dim adp As New SqlDataAdapter()
Dim dt As New DataTable()
Try
adp = New SqlDataAdapter("BindEmpGrid_Sp", con)
adp.Fill(dt)
If dt.Rows.Count > 0 Then
grdEmp.DataSource = dt
grdEmp.DataBind()
Else
grdEmp.DataSource = Nothing
grdEmp.DataBind()
End If
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error
occured : " + ex.Message.ToString() + "');",
True)
Finally
con.Close()
adp.Dispose()
End Try
End Sub
#End Region
#Region "paging in
GridView"
Protected Sub
grdEmp_PageIndexChanging(sender As Object, e As
System.Web.UI.WebControls.GridViewPageEventArgs)
Handles grdEmp.PageIndexChanging
grdEmp.PageIndex = e.NewPageIndex
grdEmp.DataBind()
SetSelectedData()
End Sub
#End Region
Private Sub
SaveSelectedData()
Dim arrLst As ArrayList
'Check the ViewState. If it has data then add to arraylist
If ViewState("CheckedRecords")
IsNot Nothing Then
arrLst = DirectCast(ViewState("CheckedRecords"), ArrayList)
Else
arrLst = New ArrayList()
End If
'Find the "All" checkbox from the GridView Header
Dim chkAll As CheckBox = DirectCast(grdEmp.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
For i As Integer = 0 To
grdEmp.Rows.Count - 1
Dim Emp_Id As
Int32
'check if the "All" checkbox is
checked or not
If chkAll.Checked Then
' If checked, then if the arraylist doesn't
contain the Emp_Id then add it to the arraylist.
Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
If Not
arrLst.Contains(Emp_Id) Then
arrLst.Add(Emp_Id)
End If
Else
' When "All" checkbox not checked
'Find the checkbox from the GridView rows
Dim chkRow As
CheckBox = DirectCast(grdEmp.Rows(i).Cells(0).FindControl("chkRow"), CheckBox)
'check if the checkbox inside the gridview
rows is checked or not
If chkRow.Checked Then
'
If checked, then if the arraylist doesn't contain the Emp_Id then add it to the
arraylist.
Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
If
Not arrLst.Contains(Emp_Id) Then
'add Emp_id in Array list
arrLst.Add(Emp_Id)
End
If
Else
'
When checkbox inside gridview rows is not checked
Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
'
if array list contains the Emp_Id
If
arrLst.Contains(Emp_Id) Then
'Remove Emp_id from Array list
arrLst.Remove(Emp_Id)
End
If
End If
End If
Next
'Storing the contents of array list i.e. Emp_id into
ViewState.
ViewState("CheckedRecords")
= arrLst
End Sub
Private Sub
SetSelectedData()
' int currentCount =
0;
'Find the "All" checkbox from the GridView Header
Dim chkAll As CheckBox = DirectCast(grdEmp.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
chkAll.Checked = True
' adding Checked records from ViewState to Arraylist
Dim arrLst As ArrayList = DirectCast(ViewState("CheckedRecords"), ArrayList)
For i As Integer = 0 To
grdEmp.Rows.Count - 1
'Find the checkbox from the GridView Rows
Dim chkRow As
CheckBox = DirectCast(grdEmp.Rows(i).Cells(0).FindControl("chkRow"), CheckBox)
If chkRow IsNot
Nothing Then
Dim Emp_Id As
Int32 = Convert.ToInt32(grdEmp.DataKeys(i).Value)
chkRow.Checked = arrLst.Contains(Emp_Id)
If Not
chkRow.Checked Then
chkAll.Checked = False
End If
End If
Next
End Sub
Protected Sub
btnDelete_Click(sender As Object, e As System.EventArgs)
Dim cmd As New SqlCommand()
Try
SetSelectedData()
grdEmp.AllowPaging = False
grdEmp.DataBind()
Dim arrLst As
ArrayList = DirectCast(ViewState("CheckedRecords"), ArrayList)
For i As
Integer = 0 To
grdEmp.Rows.Count - 1
Dim Emp_Id As
Int32 = Convert.ToInt32(grdEmp.DataKeys(i).Value)
If arrLst.Contains(Emp_Id) Then
cmd = New SqlCommand("DeleteEmpRecord_Sp", con)
cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value
= Emp_Id
cmd.CommandType = CommandType.StoredProcedure
con.Open()
cmd.ExecuteNonQuery()
arrLst.Remove(Emp_Id)
con.Close()
End If
Next
ViewState("CheckedRecords")
= arrLst
grdEmp.AllowPaging = True
ScriptManager.RegisterClientScriptBlock(Page,
Page.[GetType](), Guid.NewGuid().ToString(),
"alert('Selected Records has been deleted
successfully');", True)
BindEmpGrid()
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error
occured : " + ex.Message.ToString() + "');",
True)
Finally
cmd.Dispose()
End Try
End Sub
Now over to you:
"If you like my work; you can appreciate by leaving your comments,
hitting Facebook like button, following on Google+, Twitter, Linked in and
Pinterest, stumbling my posts on stumble upon and subscribing for receiving
free updates directly to your inbox . Stay tuned and stay connected for more
technical updates."
8 comments
Click here for commentsvery nice article..
Replycan you please upload some snippets regarding various WCF hosting like IIS,WS,WAS hosting methods and on WCF RIA,REST services
Hello asif khan..thanks for appreciating this article..read the following article as per your requirement:
ReplyHow to create and consume WCF Services in asp.net ?
http://www.webcodeexpert.com/2013/04/how-to-create-and-consume-wcf-services.html
WCF Service to bind,insert,edit,update,delete from sql server database in asp.net C#
http://www.webcodeexpert.com/2013/08/wcf-service-to-bindinserteditupdatedele.html
Solution sis great but you get error message
ReplyInvalid postback or callback argument. Event validation is enabled using in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation.
Set the following in the page directive
EnableEventValidation="false"
Thus in effect disabling security to prevent code injection , etc.
Do you have an option in VB whith code validation = true?
hello sir i m fresher and i just started learning .net
Replyi want to ask you that how to move from one page to another page in asp.net
hello sir m new to asp.net
Replyi want to ask you that how to move from one page to another page in asp.net using C#
Hello Aniket ..you can use Response.Redirect("Anotherpage.aspx"); or Server.Transfer("Anotherpage.aspx"); for that purpose.
ReplyRead the article to know more about them:
Difference between Response.Redirect and Server.Transfer in asp.net
http://www.webcodeexpert.com/2013/03/difference-between-responseredirect-and.html
Dear sir,
Replyi want to create Dyanmic menu with sub menu.plz help me .
Thank u
use a hyperlink tool and give the href="page.aspx "
ReplyIf you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..