Hello All, We are going to start new batch from next week. message/call or mail us for more details.

Insert Edit Update in Gridview Using Single Stored Procedure


Introduction

In this article we will learn how to insert, edit, update and cancel the records in a GridView using a single stored procedure. Here we use a Template Field inside a grid view control. Inside theTemplate Field we place an Item Template for displaying records from the database at runtime. Other templates used here are EditItemTemplate and Footer Template. Here, paging facilities are also provided.

Table Structure

GrdStrPr1.jpg

Stored Procedure
CREATE PROCEDURE [dbo].[employee_pro]
(
@empid as int=0,
@name as varchar(50)='',
@address as varchar(50)='',
@designation as varchar(50)='',
@status as varchar(50)=''
)
AS
BEGIN

SET NOCOUNT ON;
if(@status = 'Display')
begin
Select from employee
end
else if(@status = 'Add')
begin
Insert into employee(name,address,designation,createtime) values(@name,@address,@designation,getdate())

end
else if(@status = 'Update')
begin
Update employee set name=@name, address=@address,designation=@designation where empid=@empid

end
else if(@status = 'Delete')
begin
Delete from employee where empid=@empid
end
END

Now let's move to the coding part.

StoredProcedure.aspx

 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="StoredProcedure.aspx.cs"Inherits="Insert_update_delete_Stored_Pro.StoredProcedure" %>

<!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>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="true" ShowFooter="true"
            PageSize="5" AutoGenerateColumns="false"
            onpageindexchanging="GridView1_PageIndexChanging"
            onrowcancelingedit="GridView1_RowCancelingEdit"
            onrowcommand="GridView1_RowCommand" onrowdeleting="GridView1_RowDeleting"
            onrowediting="GridView1_RowEditing" onrowupdating="GridView1_RowUpdating"HeaderStyle-BackColor="Red"
     HeaderStyle-ForeColor="White" BackColor="#FFCC66">
<AlternatingRowStyle BackColor="#FFFFCC" />
<Columns>
   
<asp:TemplateField HeaderText="Empid">

<ItemTemplate>

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

</ItemTemplate>

<FooterTemplate>

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

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name">

<ItemTemplate>

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

</ItemTemplate>

<EditItemTemplate>

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

</EditItemTemplate>

<FooterTemplate>

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

</FooterTemplate>

</asp:TemplateField>

  <asp:TemplateField HeaderText="Address">

<ItemTemplate>

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

</ItemTemplate>

<EditItemTemplate>

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

</EditItemTemplate>

<FooterTemplate>

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

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Designation">

<ItemTemplate>

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

</ItemTemplate>

<EditItemTemplate>

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

</EditItemTemplate>

<FooterTemplate>

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

</FooterTemplate>

</asp:TemplateField>

  <asp:TemplateField HeaderText="Create Time">

<ItemTemplate>

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

</ItemTemplate>

</asp:TemplateField>

  <asp:TemplateField HeaderText="Edit">

<ItemTemplate>

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

<br />

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

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

 </span>

</ItemTemplate>

<EditItemTemplate>

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

<br />

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

</EditItemTemplate>

<FooterTemplate>

  <asp:Button ID="btnAddRecord" runat="server" Text="Add" CommandName="Add"></asp:Button>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>
    </div>
    </form>
</body>
</html>
StoredProcedure.aspx.cs

 
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace Insert_update_delete_Stored_Pro
{
    public partial class StoredProcedure : System.Web.UI.Page
    {
        string strConnString =ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlCommand com;
        SqlDataAdapter sqlda;
        DataSet ds;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }
        protected void BindGrid()
        {
            SqlConnection con = new SqlConnection(strConnString);
            com = new SqlCommand();
            con.Open();
            com.Connection = con;
            com.CommandText = "employee_pro";
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add(new SqlParameter("@status"SqlDbType.VarChar, 50));
            com.Parameters["@status"].Value = "Display";
            sqlda = new SqlDataAdapter(com);
            ds = new DataSet();
            sqlda.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            con.Close();
        }

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

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

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName.Equals("Add"))
            {
                TextBox txtname = (TextBox)GridView1.FooterRow.FindControl("txtAddname");
                TextBox txtaddress = (TextBox)GridView1.FooterRow.FindControl("txtAddaddress");
                TextBox txtdesignation = (TextBox)GridView1.FooterRow.FindControl("txtAdddesignation");
                string name, address, designation;
                name = txtname.Text;
                address = txtaddress.Text;
                designation = txtdesignation.Text;
                Addemployee(name, address, designation);
                GridView1.EditIndex = -1;
                BindGrid();
            }
        }
        protected void Addemployee(string name, string address, string designation)
        {
            SqlConnection con = new SqlConnection(strConnString);
            con.Open();
            com = new SqlCommand();
            com.CommandText = "employee_pro";
            com.CommandType = CommandType.StoredProcedure;
            com.Connection = con;
            com.Parameters.Add(new SqlParameter("@status"SqlDbType.VarChar, 50));
            com.Parameters.Add(new SqlParameter("@name"SqlDbType.VarChar, 50));
            com.Parameters.Add(new SqlParameter("@address"SqlDbType.VarChar, 50));
            com.Parameters.Add(new SqlParameter("@designation"SqlDbType.VarChar, 50));
            com.Parameters["@status"].Value = "Add";
            com.Parameters["@name"].Value = name;
            com.Parameters["@address"].Value = address;
            com.Parameters["@designation"].Value = designation;
            sqlda = new SqlDataAdapter(com);
            ds = new DataSet();
            sqlda.Fill(ds);
            con.Close();
        }
        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            Label empid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblempid");
            string eid = empid.Text;
            Deleteemployee(eid);
            GridView1.EditIndex = -1;
            BindGrid();
        }
        protected void Deleteemployee(string empid)
        {
            SqlConnection con = new SqlConnection(strConnString);
            com = new SqlCommand();
            com.CommandText = "employee_pro";
            com.CommandType = CommandType.StoredProcedure;
            com.Connection = con;
            com.Parameters.Add(new SqlParameter("@status"SqlDbType.VarChar, 50));
            com.Parameters.Add(new SqlParameter("@empid"SqlDbType.Int));
            com.Parameters["@status"].Value = "Delete";
            com.Parameters["@empid"].Value = empid;
            sqlda = new SqlDataAdapter(com);
            ds = new DataSet();
            sqlda.Fill(ds);
            con.Close();
        }
        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
            BindGrid();
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            Label empid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblempid");
            TextBox name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtname");
            TextBox address = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtaddress");
            TextBox designation = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtdesignation");
            string eid = empid.Text;
            string ename = name.Text;
            string eaddress = address.Text;
            string edesignation = designation.Text;
            Updateemployee(eid, ename, eaddress, edesignation);
            GridView1.EditIndex = -1;
            BindGrid();
        }

        protected void Updateemployee(string empid, string name, string address, string designation)
        {
            SqlConnection con = new SqlConnection(strConnString);
            com = new SqlCommand();
            con.Open();
            com.Connection = con;
            com.CommandText = "employee_pro";
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add(new SqlParameter("@empid"SqlDbType.Int));
            com.Parameters.Add(new SqlParameter("@status"SqlDbType.VarChar, 50));
            com.Parameters.Add(new SqlParameter("@name"SqlDbType.VarChar, 50));
            com.Parameters.Add(new SqlParameter("@address"SqlDbType.VarChar, 50));
            com.Parameters.Add(new SqlParameter("@designation"SqlDbType.VarChar, 50));
            com.Parameters["@empid"].Value = Convert.ToInt32(empid.ToString());
            com.Parameters["@status"].Value = "Update";
            com.Parameters["@name"].Value = name;
            com.Parameters["@address"].Value = address;
            com.Parameters["@designation"].Value = designation;
            sqlda = new SqlDataAdapter(com);
            ds = new DataSet();
            sqlda.Fill(ds);
            con.Close();
        }
    }
}

Web Config 

<connectionStrings>
          <add name="ConnectionString" connectionString="data source=LENOVO-PC\MSSQLSERVER8;Initial Catalog=DotNetBrother; integrated security = true"/>

     </connectionStrings>

Output




Conclusion

So here we learned how to insert, edit, update and cancel the records in a grid view using a single stored procedure.

23 comments:

  1. Hi Pankaj, Thanks for sharing this code but in o/p i am getting all the field twice .grid is binding 2 time so please help me out for the same

    ReplyDelete
  2. hi pankaj,
    in the line
    com.CommandText = "employee_pro";
    what is "employee_pro"?
    Could u tell me please

    ReplyDelete
    Replies
    1. Its a Store Procedure Name that is written above

      Delete



  3. hi i am getting this error



    Parser Error
    Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

    Parser Error Message: Could not load type 'Insert_update_delete_Stored_Pro.StoredProcedure'.

    Source Error:


    Line 1: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="StoredProcedure.aspx.cs" Inherits="Insert_update_delete_Stored_Pro.StoredProcedure" %>
    Line 2:
    Line 3:

    ReplyDelete
  4. for Parser Error, give space before HeaderStyle-BackColor="Red"

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. to recover Source Error, copy from asp:GridView to /asp:GridView and paste it in between div and /div. i think it works

      Delete
  6. It is really a great work and the way in which u r sharing the knowledge is excellent.
    Thanks for helping me to understand basic concepts. As a beginner in Dot Net programming your post help me a lot.Thanks for your informative article. dot net training institute in velachery | top10 dot net training institutes in chennai

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. This is very nice blog,and it is helps for student's.Thanks for info
    Dot Net Online course

    ReplyDelete
  9. Thanks for posting useful information.You have provided a nice article, Thank you very much for this one. And I hope this will be useful for many people.. and I am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read.
    Selenium Training in HRBR Layout
    Selenium Training in Kalyan Nagar
    Best Selenium Training Institute in Kalyan Nagar Bangalore

    ReplyDelete
  10. I am glad to read this. Thank you for this beautiful content, Keep it up. Techavera is the best Citrix training course in Noida. Visit us For Quality Learning.Thank you

    ReplyDelete
  11. Amazing post thanks for sharing the post.Your blogs are admirable and full of knowledge salesforce Online Training

    ReplyDelete
  12. Great post, Thanks for sharing with us, This is very helpful for me.

    Software Testing Training in Chennai

    ReplyDelete
  13. Great Post, Thanks For sharing It's very informative and i prefers Dot Net Certification in Coimbatore

    ReplyDelete
  14. Training in Coimbatore provides SEO training, Web Designing Training with real-time working professional which will help students and trainees to get trained in practical real-time .
    Job Placement Training With 100% Assitance Support to be an expert at all subjects. Web Design Training Institute

    ReplyDelete
  15. Hiii...Thanks for sharing amazing post...its very informative...Keep move on...
    Salesforce Training in Hyderabad

    ReplyDelete
  16. It’s really Nice and Meaningful. It’s really cool Blog. You have really helped lots of people who visit Blog and provide them Useful Information. Thanks for Sharing.Dot Net Training in Bangalore

    ReplyDelete

  17. Incredible points. Great arguments. Keep up the good
    spirit.
    www.hulu.com/activate

    ReplyDelete

  18. Hi
    I visited your blog you have shared amazing information, i really like the information provided by you, You have done a great work. I hope you will share some more information regarding full movies online. I appreciate your work.
    Thanks
    Citrix training & certifications course

    ReplyDelete