Tuesday, 25 March 2014

How to add edit delete update records in Grid view ASP.NET



by: Saravanan Arumugam
Date: 21/04/2013
Page views:
16916

Hide demo
Description: http://www.dotnetfox.com/Document/1003/1003Demo1.gif
            In this article I’m going to explain how to insert delete edit and update records in ASP.NET GridView using C#.
If we want to insert delete edit and update records in gridview we should use following gridview events, 
  1. onrowcommand
  2. onrowdeleting
  3. onrowupdating
  4. onrowcancelingedit
  5. onrowediting 
         Also we should use following template field in GridView. 
1.ItemTemplate :
         ItemTemplate is used to display records in grid view
2.EditItemTemplate
         EditItemTemplate is used to Edit/Update records in grid view
3.FooterTemplate
         FooterTemplate is used to insert new records in grid view

Table Design:
Column Name
Data Type
empid
varchar(50)
name
varchar(100)
designation
varchar(50)
city
varchar(50)
country
varchar(50)
Create table script:
CREATE TABLE [dbo].[EmployeeDetails](
          [empid] [varchar](50) NULL,
          [name] [varchar](100) NULL,
          [designation] [varchar](100) NULL,
          [city] [varchar](50) NULL,
          [country] [varchar](50) NULL
) ON [PRIMARY]

Designer Source Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <br />
    <br />
        <table width="800px" align="center">
            <tr>
                <td colspan="2" align="center"><b>Employee Details</b></td>
            </tr>         
            <tr>
            <td colspan="2">
            <asp:GridView ID="gvEmployeeDetails" runat="server" Width="100%"
                    AutoGenerateColumns="false" ShowFooter="true"
                    onrowcommand="gvEmployeeDetails_RowCommand"
                    onrowdeleting="gvEmployeeDetails_RowDeleting"
                    onrowupdating="gvEmployeeDetails_RowUpdating"
                    onrowcancelingedit="gvEmployeeDetails_RowCancelingEdit"
                    onrowediting="gvEmployeeDetails_RowEditing">
                <Columns>           
                    <asp:TemplateField HeaderText="Employee ID">
                        <ItemTemplate>
                            <asp:Label ID="lblEmpID" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "empid") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:Label ID="lblEditEmpID" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "empid") %>'></asp:Label>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddEmpID" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Name">
                        <ItemTemplate>
                            <asp:Label ID="lblName" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "name") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:TextBox ID="txtEditName" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "name") %>'></asp:TextBox>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddName" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Designation">
                        <ItemTemplate>
                            <asp:Label ID="lblDesignation" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "designation") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:TextBox ID="txtEditDesignation" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "designation") %>'></asp:TextBox>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddDesignation" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="City">
                        <ItemTemplate>
                            <asp:Label ID="lblCity" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "city") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:TextBox ID="txtEditCity" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "city") %>'></asp:TextBox>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddCity" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Country">
                        <ItemTemplate>
                            <asp:Label ID="lblCountry" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "country") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:TextBox ID="txtEditCountry" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "country") %>'></asp:TextBox>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddCountry" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Action">
                        <ItemTemplate>
                           <asp:ImageButton ID="imgbtnEdit" runat="server" CommandName="Edit" ImageUrl="~/Images/icon-edit.png" Height="32px" Width="32px"/>
                           <asp:ImageButton ID="imgbtnDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/Delete.png"/>
                        </ItemTemplate>
                        <EditItemTemplate>
                           <asp:ImageButton ID="imgbtnUpdate" runat="server" CommandName="Update" ImageUrl="~/Images/icon-update.png"/>
                           <asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/icon-Cancel.png"/>
                        </EditItemTemplate>
                        <FooterTemplate>
                           <asp:LinkButton ID="lbtnAdd" runat="server" CommandName="ADD" Text="Add" Width="100px"></asp:LinkButton>
                        </FooterTemplate>
                    </asp:TemplateField>                    
                </Columns>           
            </asp:GridView> 
            </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html> 

Code Behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{

    SqlConnection conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }

    protected void BindData()
    {
        DataSet ds = new DataSet();
        DataTable FromTable = new DataTable();
        conn.Open();
        string cmdstr = "Select * from EmployeeDetails";
        SqlCommand cmd = new SqlCommand(cmdstr, conn);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(ds);
        cmd.ExecuteNonQuery();
        FromTable = ds.Tables[0];
        if (FromTable.Rows.Count > 0)
        {
            gvEmployeeDetails.DataSource = FromTable;
            gvEmployeeDetails.DataBind();
        }
        else
        {
            FromTable.Rows.Add(FromTable.NewRow());
            gvEmployeeDetails.DataSource = FromTable;
            gvEmployeeDetails.DataBind();
            int TotalColumns = gvEmployeeDetails.Rows[0].Cells.Count;
            gvEmployeeDetails.Rows[0].Cells.Clear();
            gvEmployeeDetails.Rows[0].Cells.Add(new TableCell());
            gvEmployeeDetails.Rows[0].Cells[0].ColumnSpan = TotalColumns;
            gvEmployeeDetails.Rows[0].Cells[0].Text = "No records Found";
        }
        ds.Dispose();
        conn.Close();
    }

    protected void gvEmployeeDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Label lblEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEmpID");
      
        conn.Open();
        string cmdstr = "delete from EmployeeDetails where empid=@empid";
        SqlCommand cmd = new SqlCommand(cmdstr, conn);
        cmd.Parameters.AddWithValue("@empid", lblEmpID.Text);
        cmd.ExecuteNonQuery();
        conn.Close();
        BindData();

    }
    protected void gvEmployeeDetails_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("ADD"))
        {
            TextBox txtAddEmpID = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddEmpID");
            TextBox txtAddName = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddName");
            TextBox txtAddDesignation = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddDesignation");
            TextBox txtAddCity = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCity");
            TextBox txtAddCountry = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCountry");
           
            conn.Open();
            string cmdstr = "insert into EmployeeDetails(empid,name,designation,city,country) values(@empid,@name,@designation,@city,@country)";
            SqlCommand cmd = new SqlCommand(cmdstr, conn);
            cmd.Parameters.AddWithValue("@empid", txtAddEmpID.Text);
            cmd.Parameters.AddWithValue("@name", txtAddName.Text);
            cmd.Parameters.AddWithValue("@designation", txtAddDesignation.Text);
            cmd.Parameters.AddWithValue("@city", txtAddCity.Text);
            cmd.Parameters.AddWithValue("@country", txtAddCountry.Text);
            cmd.ExecuteNonQuery();
            conn.Close();
            BindData();
        }      
    }
    protected void gvEmployeeDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        Label lblEditEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEditEmpID");
        TextBox txtEditName = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditName");
        TextBox txtEditDesignation = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditDesignation");
        TextBox txtEditCity = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCity");
        TextBox txtEditCountry = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCountry");

        conn.Open();
        string cmdstr = "update EmployeeDetails set name=@name,designation=@designation,city=@city,country=@country where empid=@empid";
        SqlCommand cmd = new SqlCommand(cmdstr, conn);
        cmd.Parameters.AddWithValue("@empid", lblEditEmpID.Text);
        cmd.Parameters.AddWithValue("@name", txtEditName.Text);
        cmd.Parameters.AddWithValue("@designation", txtEditDesignation.Text);
        cmd.Parameters.AddWithValue("@city", txtEditCity.Text);
        cmd.Parameters.AddWithValue("@country", txtEditCountry.Text);
        cmd.ExecuteNonQuery();
        conn.Close();

        gvEmployeeDetails.EditIndex = -1;
        BindData();

    }
    protected void gvEmployeeDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvEmployeeDetails.EditIndex = -1;
        BindData();
    }
    protected void gvEmployeeDetails_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvEmployeeDetails.EditIndex = e.NewEditIndex;
        BindData();
    }
}



                                                           
- See more at: http://www.dotnetfox.com/articles/how-to-add-edit-delete-update-records-in-grid-view-Asp-Net-1003.aspx#sthash.ZxuZWlOl.dpuf


by: Saravanan Arumugam
Page views:
16916


Hide demo Download
            In this article I’m going to explain how to insert delete edit and update records in ASP.NET GridView using C#.
If we want to insert delete edit and update records in gridview we should use following gridview events, 
  1. onrowcommand
  2. onrowdeleting
  3. onrowupdating
  4. onrowcancelingedit
  5. onrowediting 
         Also we should use following template field in GridView. 
1.ItemTemplate :
         ItemTemplate is used to display records in grid view
2.EditItemTemplate
         EditItemTemplate is used to Edit/Update records in grid view
3.FooterTemplate
         FooterTemplate is used to insert new records in grid view

Table Design:
Column Name
Data Type
empid
varchar(50)
name
varchar(100)
designation
varchar(50)
city
varchar(50)
country
varchar(50)
Create table script:
CREATE TABLE [dbo].[EmployeeDetails](
          [empid] [varchar](50) NULL,
          [name] [varchar](100) NULL,
          [designation] [varchar](100) NULL,
          [city] [varchar](50) NULL,
          [country] [varchar](50) NULL
) ON [PRIMARY]

Designer Source Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <br />
    <br />
        <table width="800px" align="center">
            <tr>
                <td colspan="2" align="center"><b>Employee Details</b></td>
            </tr>         
            <tr>
            <td colspan="2">
            <asp:GridView ID="gvEmployeeDetails" runat="server" Width="100%"
                    AutoGenerateColumns="false" ShowFooter="true"
                    onrowcommand="gvEmployeeDetails_RowCommand"
                    onrowdeleting="gvEmployeeDetails_RowDeleting"
                    onrowupdating="gvEmployeeDetails_RowUpdating"
                    onrowcancelingedit="gvEmployeeDetails_RowCancelingEdit"
                    onrowediting="gvEmployeeDetails_RowEditing">
                <Columns>           
                    <asp:TemplateField HeaderText="Employee ID">
                        <ItemTemplate>
                            <asp:Label ID="lblEmpID" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "empid") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:Label ID="lblEditEmpID" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "empid") %>'></asp:Label>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddEmpID" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Name">
                        <ItemTemplate>
                            <asp:Label ID="lblName" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "name") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:TextBox ID="txtEditName" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "name") %>'></asp:TextBox>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddName" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Designation">
                        <ItemTemplate>
                            <asp:Label ID="lblDesignation" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "designation") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:TextBox ID="txtEditDesignation" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "designation") %>'></asp:TextBox>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddDesignation" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="City">
                        <ItemTemplate>
                            <asp:Label ID="lblCity" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "city") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:TextBox ID="txtEditCity" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "city") %>'></asp:TextBox>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddCity" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Country">
                        <ItemTemplate>
                            <asp:Label ID="lblCountry" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "country") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>           
                            <asp:TextBox ID="txtEditCountry" runat="server" Text='<%#DataBinder.Eval(Container.DataItem, "country") %>'></asp:TextBox>           
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:TextBox ID="txtAddCountry" runat="server" ></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Action">
                        <ItemTemplate>
                           <asp:ImageButton ID="imgbtnEdit" runat="server" CommandName="Edit" ImageUrl="~/Images/icon-edit.png" Height="32px" Width="32px"/>
                           <asp:ImageButton ID="imgbtnDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/Delete.png"/>
                        </ItemTemplate>
                        <EditItemTemplate>
                           <asp:ImageButton ID="imgbtnUpdate" runat="server" CommandName="Update" ImageUrl="~/Images/icon-update.png"/>
                           <asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/icon-Cancel.png"/>
                        </EditItemTemplate>
                        <FooterTemplate>
                           <asp:LinkButton ID="lbtnAdd" runat="server" CommandName="ADD" Text="Add" Width="100px"></asp:LinkButton>
                        </FooterTemplate>
                    </asp:TemplateField>                    
                </Columns>           
            </asp:GridView> 
            </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html> 

Code Behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{

    SqlConnection conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }

    protected void BindData()
    {
        DataSet ds = new DataSet();
        DataTable FromTable = new DataTable();
        conn.Open();
        string cmdstr = "Select * from EmployeeDetails";
        SqlCommand cmd = new SqlCommand(cmdstr, conn);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        adp.Fill(ds);
        cmd.ExecuteNonQuery();
        FromTable = ds.Tables[0];
        if (FromTable.Rows.Count > 0)
        {
            gvEmployeeDetails.DataSource = FromTable;
            gvEmployeeDetails.DataBind();
        }
        else
        {
            FromTable.Rows.Add(FromTable.NewRow());
            gvEmployeeDetails.DataSource = FromTable;
            gvEmployeeDetails.DataBind();
            int TotalColumns = gvEmployeeDetails.Rows[0].Cells.Count;
            gvEmployeeDetails.Rows[0].Cells.Clear();
            gvEmployeeDetails.Rows[0].Cells.Add(new TableCell());
            gvEmployeeDetails.Rows[0].Cells[0].ColumnSpan = TotalColumns;
            gvEmployeeDetails.Rows[0].Cells[0].Text = "No records Found";
        }
        ds.Dispose();
        conn.Close();
    }

    protected void gvEmployeeDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Label lblEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEmpID");
      
        conn.Open();
        string cmdstr = "delete from EmployeeDetails where empid=@empid";
        SqlCommand cmd = new SqlCommand(cmdstr, conn);
        cmd.Parameters.AddWithValue("@empid", lblEmpID.Text);
        cmd.ExecuteNonQuery();
        conn.Close();
        BindData();

    }
    protected void gvEmployeeDetails_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("ADD"))
        {
            TextBox txtAddEmpID = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddEmpID");
            TextBox txtAddName = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddName");
            TextBox txtAddDesignation = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddDesignation");
            TextBox txtAddCity = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCity");
            TextBox txtAddCountry = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCountry");
           
            conn.Open();
            string cmdstr = "insert into EmployeeDetails(empid,name,designation,city,country) values(@empid,@name,@designation,@city,@country)";
            SqlCommand cmd = new SqlCommand(cmdstr, conn);
            cmd.Parameters.AddWithValue("@empid", txtAddEmpID.Text);
            cmd.Parameters.AddWithValue("@name", txtAddName.Text);
            cmd.Parameters.AddWithValue("@designation", txtAddDesignation.Text);
            cmd.Parameters.AddWithValue("@city", txtAddCity.Text);
            cmd.Parameters.AddWithValue("@country", txtAddCountry.Text);
            cmd.ExecuteNonQuery();
            conn.Close();
            BindData();
        }      
    }
    protected void gvEmployeeDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        Label lblEditEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEditEmpID");
        TextBox txtEditName = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditName");
        TextBox txtEditDesignation = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditDesignation");
        TextBox txtEditCity = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCity");
        TextBox txtEditCountry = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCountry");

        conn.Open();
        string cmdstr = "update EmployeeDetails set name=@name,designation=@designation,city=@city,country=@country where empid=@empid";
        SqlCommand cmd = new SqlCommand(cmdstr, conn);
        cmd.Parameters.AddWithValue("@empid", lblEditEmpID.Text);
        cmd.Parameters.AddWithValue("@name", txtEditName.Text);
        cmd.Parameters.AddWithValue("@designation", txtEditDesignation.Text);
        cmd.Parameters.AddWithValue("@city", txtEditCity.Text);
        cmd.Parameters.AddWithValue("@country", txtEditCountry.Text);
        cmd.ExecuteNonQuery();
        conn.Close();

        gvEmployeeDetails.EditIndex = -1;
        BindData();

    }
    protected void gvEmployeeDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvEmployeeDetails.EditIndex = -1;
        BindData();
    }
    protected void gvEmployeeDetails_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvEmployeeDetails.EditIndex = e.NewEditIndex;
        BindData();
    }
}



                                                           
- See more at: http://www.dotnetfox.com/articles/how-to-add-edit-delete-update-records-in-grid-view-Asp-Net-1003.aspx#sthash.ZxuZWlOl.dpuf

No comments:

Post a Comment