Hi David,

Try this...

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)=''

)

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 lets 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">

<title>Untitled Pagetitle>

head>

<body>

<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">

<asp:TemplateField HeaderText="Empid">

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

ItemTemplate>

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

FooterTemplate>

asp:TemplateField>

<asp:TemplateField HeaderText="Name">

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

ItemTemplate>

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

EditItemTemplate>

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

FooterTemplate>

asp:TemplateField>

<asp:TemplateField HeaderText="Address">

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

ItemTemplate>

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

EditItemTemplate>

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

FooterTemplate>

asp:TemplateField>

<asp:TemplateField HeaderText="Designation">

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

ItemTemplate>

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

EditItemTemplate>

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

FooterTemplate>

asp:TemplateField>

<asp:TemplateField HeaderText="Create Time">

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

ItemTemplate>

asp:TemplateField>

<asp:TemplateField HeaderText="Edit">

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

<br />

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

ItemTemplate>

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

<br />

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

EditItemTemplate>

<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();

}

Thanks
If this post helps you mark it as answer

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak14y ago
Hi David,

Try this...

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 lets 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 Pagetitle>

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 />

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

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();

        }

    }

}

Thanks
If this post helps you mark it as answer
14y ago
ComandType defines what type of input to expect for the DBServer Server.

Here is a demo on U/D/E in grid View. http://www.dotnetfunda.com/articles/article29.aspx
Then run the StoreProcedure to update the called database.

StoredProcedure with GridView

Stored Procedure

StoredProcedure.aspx

StoredProcedure.aspx.cs