Home > Articles > ASP.NET Articles > How to perform insert, update, delete and select rows in ASP.NET gridview control using stored proce...

How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure

by Thamil   on Jun 24, 2013   Category: ASP.NET  | Level: Intermediate  |  Views: 193916    |  Points: 100   
Like this article? Bookmark and Share:
In this article, we will explore how to add, update, delete and select a record from sql server database using gridview control in a simple way. I’m using visual studio 2008 and sql server 2008 for this sample.


 

Introduction


In general, gridview control is used to populate the data in tabular format from different datasources and basically it derived from webcontrol class. Actually gridview control is built with lot of features and this control is commonly used for all asp.net applications. And here I’m explaining some basic functions of this gridview control such as insert edit and delete a record.


1 .Data source


First, we need to prepare the database table and stored procedure for gridview operations such as insert, update & delete a records, here I’m using sql server 2008 for all database activities, for this sample I have created the customer table and stored procedure for all mentioned gridview operations. Create the customer table in your own test database using below table script and insert some records initially to load gridview with data.

/****** Object: Table [dbo].[tblCustomer] Script Date: 06/25/2013 00:44:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tblCustomer](

[CustID] [int] IDENTITY(1,1) NOT NULL,

[CustName] [varchar](100) NOT NULL,

[Custaddress1] [varchar](100) NOT NULL,

[Custaddress2] [varchar](100) NOT NULL,

[CustPhone] [varchar](100) NOT NULL,

[CustEmail] [varchar](100) NOT NULL,

[Createdby] [varchar](100) NOT NULL,

[CreatedDt] [datetime] NOT NULL,

[Updatedby] [varchar](100) NULL,

[UpdatedDt] [datetime] NULL,

[Active] [bit] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Insert script [dbo].[tblCustomer]    Script Date: 06/21/2013 14:48:36 ******/

 

INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])

VALUES('Muthu','Delhi','Maujpur, India','2345678912','muthu99@yahoo.com','admin','Jun 21 2013  2:44:06:420PM',NULL,NULL,1);

INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])

VALUES('Ramesh','Vijayapura','bangalore, India','245352454','rrramesh@gmil.com','admin','Jun 21 2013  2:46:12:750PM',NULL,NULL,1);

INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])

VALUES('Sridhar','Ashok nagar','Chennai, India','45656423422','rrramesh@gmil.com','admin','Jun 21 2013  2:46:49:807PM',NULL,NULL,1);

INSERT INTO [tblCustomer] ([CustName],[Custaddress1],[Custaddress2],[CustPhone],[CustEmail],[Createdby],[CreatedDt],[Updatedby],[UpdatedDt],[Active])

VALUES('Kumar','Thrissur','Kochi, India','23456432','rkkum@lmil.com','admin','Jun 21 2013  2:47:56:803PM',NULL,NULL,1);

 

Once you execute the above table and insert scripts, you will get the below records in the table initially to load data into gridview control.

Execute the below SP_Customer stored procedure in sql server database which is used to handle insert, update, select and delete a record into customer table. Here we are not deleting a record permanently but using active flag column, make it inactive the record while doing the delete operation then make it active and get it whenever you want. This is the easy way to handle all the gridview operations in data base side instead of using sql query from front end side. Also here we used @@ERROR for error handling in the stored procedure and returns the number to front end and confirm that the performed action is successful or not.

/****** Object: StoredProcedure [dbo].[SP_Customer] Script Date: 09/18/2013 21:56:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[SP_Customer]

(

@pvchCustId int =0,

@pvchCustName varchar(100)=null,

@pvchustaddress1 varchar(100)=null,

@pvchustaddress2 varchar(100)=null,

@pvchCustPhone varchar(100)=null,

@pvchCustEmail varchar(100)=null,

@pvchCreatedBy varchar(100)=null,

@pvchAction varchar(50)=null,

@pIntErrDescOut int output

)

AS

BEGIN

if(@pvchAction='select')

begin

SELECT CUSTID,CUSTNAME,CUSTNAME,CUSTADDRESS1,CUSTADDRESS2,CUSTPHONE,

CUSTEMAIL,ACTIVE FROM tblCustomer WHERE Active=1;

end

else if(@pvchAction='insert')

begin

if not exists(select * from tblCustomer where CustName=@pvchCustName or CustEmail=@pvchCustEmail)

begin

INSERT INTO tblCustomer(CustName,Custaddress1,Custaddress2,CustPhone,

CustEmail,Createdby,CreatedDt,Active)VALUES(@pvchCustName,@pvchustaddress1,@pvchustaddress2,@pvchCustPhone,

@pvchCustEmail,@pvchCreatedBy,GETDATE(),1);

end

else

begin

set @pIntErrDescOut=2

end

end

else if(@pvchAction='update')

begin

UPDATE tblCustomer SET CustName=@pvchCustName,Custaddress1=@pvchustaddress1,Custaddress2=@pvchustaddress2,

CustPhone=@pvchCustPhone,CustEmail=@pvchCustEmail,Updatedby=@pvchCreatedBy,UpdatedDt=GETDATE()

WHERE CustID=@pvchCustId;

end

else if(@pvchAction='delete')

begin

UPDATE tblCustomer SET Active=0,Updatedby=@pvchCreatedBy,UpdatedDt=GETDATE()

WHERE CustID=@pvchCustId;

end

IF (@@ERROR <> 0)

BEGIN

SET @pIntErrDescOut = 1

END

END

 

2. Create ASP.NET Web application


Now we have prepared the all database related activities for this and moving to front end side to setup aspx page and codebehind file to handle gridview operations, so first create the asp.net web application and add GridviewControl.aspx page as shown below.


3. Connection string (web.config)

Now we have created the asp.net application and open the web.config file to add connection string as shown below. Connection string must be based on your database details.

<connectionStrings>

<add name="ConnectionString"

connectionString="Data Source=...........your connection string........."

providerName="System.Data.SqlClient"/>

</connectionStrings>

 

4. Design Gridview

Open the GridviewControl.aspx page to design the gridview. Drag the gridview control from tool box and placed into the page and make it Autogeneratecolumns as false, so manually we will create the gridview columns to display.  Copy the below code and paste into aspx page as shown below. In the below gridview I used templatefields to display data and perform gridview operations.

1. Templatefield:  Actually we use this templatefield to display any asp.net control such as check box, label, drop down etc in the gridview control to provide additional functionality and this templatefield supports many templates, here we are using Itemtemplate, EdititemTemplate and FooterTemplate.
2. ItemTemplate : This template used to display the content every row rendered by the gridview control. Here we using label in this template and displaying the data.
3. EditItemTemplate: When we select or edit a specific row, the contents of this templates are displayed in the gridview. Mainly used when we edit a particular row in the gridview control. Here we use textbox control to display the data, while editing a row in the gridview.
4. FooterTemplete: We use this template when wants to display the contents in the footer. Here we included textbox control in this templete to add new record in the footer area.

Also applied the required field validator wherever required and added JavaScript confirm message while performing the delete functionality. Delete confirmation message will be displayed when we click on the delete link on the girdview. So that we can get confirmation from user before delete a record (making in active) from table.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridviewControl.aspx.cs"

Inherits="GridviewSample.GridviewControl" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Demo Editable gridview</title>

</head>

<body>

<form id="form1" runat="server">

<div style="font-size:20px;font-family:Verdana">

Sample - Editable Gridview Control

<br />

</div>

<div>

<asp:GridView ID="GridViewSample" runat="server" AutoGenerateColumns="False" Font-Names="Verdana"

AllowPaging="true" ShowFooter="true" PageSize="5" Width="75%" OnPageIndexChanging="GridViewSample_PageIndexChanging"

OnRowCancelingEdit="GridViewSample_RowCancelingEdit" OnRowEditing="GridViewSample_RowEditing"

OnRowUpdating="GridViewSample_RowUpdating"

OnRowDeleting="GridViewSample_RowDeleting" OnRowCommand="GridViewSample_RowCommand"

BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px">

<AlternatingRowStyle BackColor="#FFD4BA" />

<FooterStyle BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />

<PagerStyle BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />

<HeaderStyle Height="30px" BackColor="#FF9E66" Font-Size="15px" BorderColor="#CCCCCC"

BorderStyle="Solid" BorderWidth="1px" />

<RowStyle Height="20px" Font-Size="13px" BorderColor="#CCCCCC" BorderStyle="Solid"

BorderWidth="1px" />

<Columns>

<asp:TemplateField HeaderText="CustID" Visible="false">

<ItemTemplate>

<asp:Label ID="lblCustID" runat="server" Text='<%#Eval("CustID") %>'></asp:Label>

</ItemTemplate>

<FooterTemplate>

<asp:Label ID="lbladd" runat="server"></asp:Label>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Customer Name" HeaderStyle-Width="10%">

<ItemTemplate>

<asp:Label ID="lblCustName" runat="server" Text='<%#Eval("CustName") %>'></asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtCustName" runat="server" Text='<%#Eval("CustName") %>'></asp:TextBox>

</EditItemTemplate>

<FooterTemplate>

<asp:TextBox ID="txtAddCustName" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="reqName" ValidationGroup="ValgrpCust" ControlToValidate="txtAddCustName" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>

</FooterTemplate>

<HeaderStyle Width="15%"></HeaderStyle>

</asp:TemplateField>

<asp:TemplateField HeaderText="Address 1" HeaderStyle-Width="15%">

<ItemTemplate>

<asp:Label ID="lblAddress1" runat="server" Text='<%#Eval("Custaddress1") %>'></asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtCustAddres1" runat="server" Text='<%#Eval("Custaddress1") %>'></asp:TextBox>

</EditItemTemplate>

<FooterTemplate>

<asp:TextBox ID="txtAddCustAdres1" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="reqAdd1" ValidationGroup="ValgrpCust" ControlToValidate="txtAddCustAdres1" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>

</FooterTemplate>

<HeaderStyle Width="15%"></HeaderStyle>

</asp:TemplateField>

<asp:TemplateField HeaderText="Address 2" HeaderStyle-Width="15%">

<ItemTemplate>

<asp:Label ID="lblAddress2" runat="server" Text='<%#Eval("Custaddress2") %>'></asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtCustAddres2" runat="server" Text='<%#Eval("Custaddress2") %>'></asp:TextBox>

</EditItemTemplate>

<FooterTemplate>

<asp:TextBox ID="txtAddCustAdres2" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="reqAdd2" ValidationGroup="ValgrpCust" ControlToValidate="txtAddCustAdres2" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>

</FooterTemplate>

<HeaderStyle Width="15%"></HeaderStyle>

</asp:TemplateField>

<asp:TemplateField HeaderText="Phone" HeaderStyle-Width="10%">

<ItemTemplate>

<asp:Label ID="lblPhone" runat="server" Text='<%#Eval("CustPhone") %>'></asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtCustPhone" runat="server" Text='<%#Eval("CustPhone") %>'></asp:TextBox>

</EditItemTemplate>

<FooterTemplate>

<asp:TextBox ID="txtAddCustPhone" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="reqPhone" ValidationGroup="ValgrpCust" ControlToValidate="txtAddCustPhone" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>

</FooterTemplate>

<HeaderStyle Width="10%"></HeaderStyle>

</asp:TemplateField>

<asp:TemplateField HeaderText="Email" HeaderStyle-Width="15%">

<ItemTemplate>

<asp:Label ID="lblEmail" runat="server" Text='<%#Eval("CustEmail") %>'></asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtEmail" runat="server" Text='<%#Eval("CustEmail") %>'></asp:TextBox>

</EditItemTemplate>

<FooterTemplate>

<asp:TextBox ID="txtAddEmail" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="reqEmail" ValidationGroup="ValgrpCust" ControlToValidate="txtAddEmail" runat="server" ErrorMessage="*"></asp:RequiredFieldValidator>

</FooterTemplate>

<HeaderStyle Width="15%"></HeaderStyle>

</asp:TemplateField>

<asp:TemplateField HeaderText="Edit/Delete" HeaderStyle-Width="15%">

<ItemTemplate>

<asp:LinkButton ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" />

<span onclick="return confirm('Are you sure want to delete?')">

<asp:LinkButton ID="btnDelete" Text="Delete" runat="server" CommandName="Delete" />

</span>

</ItemTemplate>

<EditItemTemplate>

<asp:LinkButton ID="btnUpdate" Text="Update" runat="server" CommandName="Update" />

<asp:LinkButton ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" />

</EditItemTemplate>

<FooterTemplate>

<asp:Button ID="btnInsertRecord" runat="server" Text="Add" ValidationGroup="ValgrpCust" CommandName="Insert" />

</FooterTemplate>

<HeaderStyle Width="15%"></HeaderStyle>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

<div><br />

<asp:Label ID="lblMessage" ForeColor="Green" Font-Bold="true" runat="server" Text=""></asp:Label>

</div>

</form>

</body>

</html>

 

5. Code behind part-Namespace details

 Add below namespaces in code behind file.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Web.UI.WebControls;

using System.Configuration;

 

6. Page load

Display the all records in page load events using below LoadData() function, in this function we are using sql server stored procedure “SP_Customer “ to get the records from database and this function LoadData() will needs to be called after every user actions on gridview operations such as add, update and delete.

string strCon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlDataAdapter SqlAda;

DataSet ds;

 

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

LoadData();

}

}

private void LoadData()

{

using (SqlConnection Sqlcon = new SqlConnection(strCon))

{

using (SqlCommand cmd = new SqlCommand())

{

Sqlcon.Open();

cmd.Connection = Sqlcon;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "SP_Customer";

cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));

cmd.Parameters["@pvchAction"].Value = "select";

cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;

SqlAda = new SqlDataAdapter(cmd);

ds = new DataSet();

SqlAda.Fill(ds);

GridViewSample.DataSource = ds;

GridViewSample.DataBind();

}

}

}

You can run the project with above code and page will display the gridview with loaded data from database as shown below

7. Add record

I used Rowcommand event to insert the records into database. Actually rowcommand is raised when a button or link button is clicked on the gridview control. The commandName property to decide which action needs to be performed. Get the footer row textbox values by using findcontrol method and insert into database using stored procedure as like below. After inserting the rows, load the data again from database by calling LoadData() function to refresh the gridview.

protected void GridViewSample_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName.Equals("Insert"))

{

int retVal=0;

TextBox Name = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustName");

TextBox Address1 = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustAdres1");

TextBox Address2 = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustAdres2");

TextBox Phone = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustPhone");

TextBox Email = (TextBox)GridViewSample.FooterRow.FindControl("txtAddEmail");

using (SqlConnection Sqlcon = new SqlConnection(strCon))

{

using (SqlCommand cmd = new SqlCommand())

{

Sqlcon.Open();

cmd.Connection = Sqlcon;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "SP_Customer";

cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));

cmd.Parameters.Add(new SqlParameter("@pvchCustName", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchustaddress1", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchustaddress2", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchCustPhone", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchCustEmail", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 100));

cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;

cmd.Parameters["@pvchAction"].Value = "insert";

cmd.Parameters["@pvchCustName"].Value = Name.Text.Trim();

cmd.Parameters["@pvchustaddress1"].Value = Address1.Text.Trim();

cmd.Parameters["@pvchustaddress2"].Value = Address2.Text.Trim();

cmd.Parameters["@pvchCustPhone"].Value = Phone.Text.Trim();

cmd.Parameters["@pvchCustEmail"].Value = Email.Text.Trim();

cmd.Parameters["@pvchCreatedBy"].Value = "Admin";

cmd.ExecuteNonQuery();

retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;

}

}

if (retVal == 2)

{

lblMessage.Text = "Customer name or Email ID aready exist!";

}

else

{

GridViewSample.EditIndex = -1;

LoadData();

lblMessage.Text = "Record inserted successfully!";

}

}

}

 

8. Update Record

Here I’m using gridview rowediting and rowupdating events to edit and update the specific row in the gridview. Rowediting event will occur when specific row’s edit button is clicked and enable the control to edit the data. Get the updated values in gridview rowupdating event, actually this will occur when row’s update button is clicked. Place the below code in rowediting and rowupdating event as shown below.

protected void GridViewSample_RowEditing(object sender, GridViewEditEventArgs e)

{

GridViewSample.EditIndex = e.NewEditIndex;

LoadData();

}

protected void GridViewSample_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

Label Custid = (Label)GridViewSample.Rows[e.RowIndex].FindControl("lblCustID");

TextBox Name = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtCustName");

TextBox Address1 = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtCustAddres1");

TextBox Address2 = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtCustAddres2");

TextBox Phone = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtCustPhone");

TextBox Email = (TextBox)GridViewSample.Rows[e.RowIndex].FindControl("txtEmail");

using (SqlConnection Sqlcon = new SqlConnection(strCon))

{

using (SqlCommand cmd = new SqlCommand())

{

Sqlcon.Open();

cmd.Connection = Sqlcon;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "SP_Customer";

cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));

cmd.Parameters.Add(new SqlParameter("@pvchCustId", SqlDbType.Int));

cmd.Parameters.Add(new SqlParameter("@pvchCustName", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchustaddress1", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchustaddress2", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchCustPhone", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchCustEmail", SqlDbType.VarChar, 100));

cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 100));

cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;

cmd.Parameters["@pvchAction"].Value = "update";

cmd.Parameters["@pvchCustId"].Value = Convert.ToInt32(Custid.Text.ToString());

cmd.Parameters["@pvchCustName"].Value = Name.Text.Trim();

cmd.Parameters["@pvchustaddress1"].Value = Address1.Text.Trim();

cmd.Parameters["@pvchustaddress2"].Value = Address2.Text.Trim();

cmd.Parameters["@pvchCustPhone"].Value = Phone.Text.Trim();

cmd.Parameters["@pvchCustEmail"].Value = Email.Text.Trim();

cmd.Parameters["@pvchCreatedBy"].Value = "Admin";

cmd.ExecuteNonQuery();

int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;

}

}

GridViewSample.EditIndex = -1;

LoadData();

lblMessage.Text = "Record updated successfully!";

}

 

 

9. Cancel Editing

if you want to cancel the edit while gridview in edit mode then use the below RowCancelEdit event to cancel the edit. This event occurs when cancel button of row in edit mode is clicked.

protected void GridViewSample_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridViewSample.EditIndex = -1;

LoadData();

}

 

10. Gridview Pagination

Pageindexchanging event is used to handle paging in gridview control. Set allowpaging property to true and also set the pagesize property to display the number of records in each page and this Pageindexchanging event occures when any paging link/button is clicked. Place the below code in Pageindexchanging as shown below.

protected void GridViewSample_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

GridViewSample.PageIndex = e.NewPageIndex;

LoadData();

}

 

11. Delete Record

Below code shows how to delete a record in gridview using  RowDeleting, this event will occurs when a row’s delete button is clicked. Get the custid value which is invisible column in the gridview and passing as a parameter for sql stored procudere and make it inactive. Also displayed the delete confirmation javascript message to the user before doing this opreation.

protected void GridViewSample_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

Label CustID = (Label)GridViewSample.Rows[e.RowIndex].FindControl("lblCustID");

using (SqlConnection Sqlcon = new SqlConnection(strCon))

{

using (SqlCommand cmd = new SqlCommand())

{

Sqlcon.Open();

cmd.Connection = Sqlcon;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "SP_Customer";

cmd.Parameters.Add(new SqlParameter("@pvchCustId", SqlDbType.Int));

cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));

cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 50));

cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;

cmd.Parameters["@pvchAction"].Value = "delete";

cmd.Parameters["@pvchCustId"].Value = Convert.ToInt32(CustID.Text.ToString());

cmd.Parameters["@pvchCreatedBy"].Value = "Admin";

cmd.ExecuteNonQuery();

int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;

}

}

GridViewSample.EditIndex = -1;

LoadData();

lblMessage.Text = "Record deleted successfully!";

}

 

See the below screenshot for displaying the delete confirmation before delete a record.

12. Attachments

I have attached sample project here, download and you can test it from your side. There is two file in the attachment and follow the below steps to see the above sample.

1. SQL script – First execute this script in your test database
2. Sample project - Open the project and change the connection string in web.config
3. Run the project.

I hope this article will help you to understand the basic functionalities of gridview control and you can download the attached sample project and see how it works.
Thanks for reading this article and please provide your feedback and suggestions.


 

.Parameters["@pvchCustName"].Value = Name.Text.Trim();

cmd.Parameters["@pvchustaddress1"].Value = Address1.Text.Trim();

cmd.Parameters["@pvchustaddress2"].Value = Address2.Text.Trim();

cmd.Parameters["@pvchCustPhone"].Value = Phone.Text.Trim();

cmd.Parameters["@pvchCustEmail"].Value = Email.Text.Trim();

cmd.Parameters["@pvchCreatedBy"].Value = "Admin";

cmd.ExecuteNonQuery();

int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;

}

}

GridViewSample.EditIndex = -1;

LoadData();

lblMessage.Text = "Record updated successfully!";

}

 

 

9. Cancel Editing

if you want to cancel the edit while gridview in edit mode then use the below RowCancelEdit event to cancel the edit. This event occurs when cancel button of row in edit mode is clicked.

protected void GridViewSample_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridViewSample.EditIndex = -1;

LoadData();

}

 

10. Gridview Pagination

Pageindexchanging event is used to handle paging in gridview control. Set allowpaging property to true and also set the pagesize property to display the number of records in each page and this Pageindexchanging event occures when any paging link/button is clicked. Place the below code in Pageindexchanging as shown below.

protected void GridViewSample_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

GridViewSample.PageIndex = e.NewPageIndex;

LoadData();

}

 

11. Delete Record

Below code shows how to delete a record in gridview using  RowDeleting, this event will occurs when a row’s delete button is clicked. Get the custid value which is invisible column in the gridview and passing as a parameter for sql stored procudere and make it inactive. Also displayed the delete confirmation javascript message to the user before doing this opreation.

protected void GridViewSample_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

Label CustID = (Label)GridViewSample.Rows[e.RowIndex].FindControl("lblCustID");

using (SqlConnection Sqlcon = new SqlConnection(strCon))

{

using (SqlCommand cmd = new SqlCommand())

{

Sqlcon.Open();

cmd.Connection = Sqlcon;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "SP_Customer";

cmd.Parameters.Add(new SqlParameter("@pvchCustId", SqlDbType.Int));

cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));

cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 50));

cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;

cmd.Parameters["@pvchAction"].Value = "delete";

cmd.Parameters["@pvchCustId"].Value = Convert.ToInt32(CustID.Text.ToString());

cmd.Parameters["@pvchCreatedBy"].Value = "Admin";

cmd.ExecuteNonQuery();

int retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;

}

}

GridViewSample.EditIndex = -1;

LoadData();

lblMessage.Text = "Record deleted successfully!";

}

 

See the below screenshot for displaying the delete confirmation before delete a record.

12. Attachments

I have attached sample project here, download and you can test it from your side. There is two file in the attachment and follow the below steps to see the above sample.

1. SQL script – First execute this script in your test database
2. Sample project - Open the project and change the connection string in web.config
3. Run the project.

I hope this article will help you to understand the basic functionalities of gridview control and you can download the attached sample project and see how it works.
Thanks for reading this article and please provide your feedback and suggestions.


 



Like this article? Bookmark and Share:

Most viewed Articles

User Comments


  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by Dave
on 9/18/2013 3:31:21 AM
Points : 10

Hi,
I found this article very very useful.
My query is that if a user enters Duplicate value like same Customer Name or same email ID then how to show a error message to the user saying "Duplicate record",how to modify the store procedure in this case.
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by Thamil
on 9/18/2013 9:48:08 AM
Points : 10

Thank you Dev,

I have updated stored proc SP_Customer & GridViewSample_RowCommand event as per your query, please check the above code.
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by Dave
on 9/19/2013 8:02:38 PM
Points : 10

Hi Thamilselvanj,
Thank you for modifying the query,its very useful.
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by suprakash
on 3/14/2014 5:27:04 AM
Points : 10

hello Thamilselvanj, i tried this article upto page_load(), but it shows error in GridViewSample.DataBind();
The error is: "both datasource and datasourceid are defined on 'GridViewSample'. Remove one definition"
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by manly pullock
on 3/16/2014 10:40:28 PM
Points : 10

Find here a detailed Asp.Net GridView tutorial

http://asp.net-informations.com/gridview/asp-gridview.htm

manly
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by v mohan
on 4/4/2014 8:58:47 AM
Points : 10

Hi Tamilselvanj
Thank you for the wonderful articles! I am following the example to insert, update, delete and select rows in ASP.NET gridview control using stored procedure. I am very new to c# ,and would you please help me how to show empty gridview if there is any data in the table?

thanks
mohan
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by Vignesh Naik
on 8/6/2014 7:17:19 AM
Points : 10

Hi,I am getting error on this line while inserting ,updating and deleting.
retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by Thamil
on 8/7/2014 2:03:17 AM
Points : 10

@Vignesh Naik, Please provide the error details.
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by Nathaniel Beveljard
on 10/24/2014 4:22:01 PM
Points : 10

Thanks for the article. Would you happen to know if the gridview must have autogeneratecolumns="false" in order to have an edit and delete button in it?
I have some gridviews, currently set to autogeneratecolumns="true", which al need delete buttons. So I was hoping there was a way to achieve this without changing that. Obviously it's not a huge deal and I'll change it if I need to, but since I'm now looking at this I'd genuinely like to know what the answer is to this for future knowledge.
Thank you.
  Re :How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure   
Posted by Thamil
on 10/27/2014 1:27:30 AM
Points : 10

Hi Nathaniel Beveljard,

Yes, its possible to add TemplateField for edit/update even autogeneratecolumns="true". if autogeneratecolumns=false then we need to add the list of column details in the gridview by using BoundField. So there is no issue to add the templatefield when autogeneratecolumns=true or false.

Submit feedback about this article

Please sign in to post feedback

Latest Posts