Insert, Update And Delete in DataGrid in ASP.Net



Insert, Update and Delete in DataGrid in ASP.Net

To do insert, update and delete in ASP.Net we need to add a DataGrid control on the page, by default it is not visible in the data control category; just right-click on the data control category and select "Choose Items..." from it.

DatagridControl.gif


ToolboxItemsdialogbox.gif

A "Choose Toolbox Items" dialog box appears; select DataGrid from it and click on ok. You'll find the DataGrid control in the data category toolbox. Now just drag and drop it on the web form.

Assume we have a table in the database with the name Emp and the fields Empid, EmpName and EmpAdd.

Now set the following properties of the DataGrid.

Set the AutoGenerateColumns to false.

Click on the smart tag of the DataGrid.

DatagridSmartTag.gif

And select the property builder option from it. Click on the columns tab and add 3 bound columns to it and add edit and delete command fields to it, the bound fileds will appear in the selected columns set the header text of the three bound fileds.

DatagridProperties.gif

After adding columns your form will look like this.

DatagridProperties1.gif

The following is the source code:-

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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:DataGrid ID="DataGrid1" runat="server" AutoGenerateColumns="False" DataKeyField="EmpId" OnDeleteCommand="DataGrid1_DeleteCommand">
            <Columns>
                <asp:TemplateColumn HeaderText="Employee ID">
                    <EditItemTemplate>
                        <asp:Label ID="lblid_e" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.EmpId") %>'></asp:Label>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblEmpid" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.EmpId") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Button ID="btnAdd" runat="server" CommandName="AddNew" Text="Insert" />
                    </FooterTemplate>
                </asp:TemplateColumn>
                <asp:TemplateColumn HeaderText="Employee Name">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtname_e" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.EmpName") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblname" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.EmpName") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtname_f" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateColumn>
                <asp:TemplateColumn HeaderText="Employee Address">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtadd_e" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.EmpAdd") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lbladd" runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.EmpAdd") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtadd_f" runat="server"></asp:TextBox>
                    </FooterTemplate>
                </asp:TemplateColumn>
                <asp:EditCommandColumn CancelText="Cancel" EditText="Edit" HeaderText="Edit" UpdateText="Update">
                </asp:EditCommandColumn>
                <asp:ButtonColumn CommandName="Delete" HeaderText="Delete" Text="[Remove]"></asp:ButtonColumn>
            </Columns>
        </asp:DataGrid></div>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Add New Rec" />
    </form>
</body>
</
html>

Specify the DataKeyField of the DataGrid to EmpId which is the primary key of our table.

Now we just need to do the coding part.

using
System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
    string dbcon = ConfigurationManager.ConnectionStrings["AdvWorks"].ConnectionString;
    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter da;
    DataSet ds;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }
    public void BindData()
    {
        con = new SqlConnection(dbcon);
        da = new SqlDataAdapter("Select * from Emp", con);
        ds = new DataSet();
        da.Fill(ds, "Emp");
        DataGrid1.DataSource = ds.Tables["Emp"].DefaultView;
        DataGrid1.DataBind();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (Button1.Text == "Add New Rec")
        {
            Button1.Text = "Cancel";
            DataGrid1.ShowFooter = true;
        }
       
else
        {
            Button1.Text = "Add New Rec";
            DataGrid1.ShowFooter = false;
        }
    }
    protected void DataGrid1_DeleteCommand(object source, DataGridCommandEventArgs e)
    {
        con = new SqlConnection(dbcon);
        cmd = new SqlCommand("Delete from Emp where Empid=" + DataGrid1.DataKeys[e.Item.ItemIndex].ToString(), con);
        con.Open();
        int rows = cmd.ExecuteNonQuery();
        if (rows > 0)
        {
            Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Deleted", "<script>alert('Deleted Successfully')</script>");
            con.Close();
            BindData();
        }
    }
    protected void DataGrid1_EditCommand(object source, DataGridCommandEventArgs e)
    {
        DataGrid1.EditItemIndex = e.Item.ItemIndex;
        BindData();
    }
    protected void DataGrid1_UpdateCommand(object source, DataGridCommandEventArgs e)
    {
        TextBox name = (TextBox)DataGrid1.Items[e.Item.ItemIndex].Cells[1].FindControl("txtname_e");
        TextBox add = (TextBox)DataGrid1.Items[e.Item.ItemIndex].Cells[2].FindControl("txtadd_e");
        con = new SqlConnection(dbcon);
        cmd = new SqlCommand("Update Emp set Empname='" + name.Text + "',EmpAdd='" + add.Text + "' where Empid=" + DataGrid1.DataKeys[e.Item.ItemIndex
.ToString(), con);
        con.Open();
        int rows = cmd.ExecuteNonQuery();
        if (rows > 0)
        {
            Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Updated", "<script>alert('Updated Successfully')</script>");
            con.Close();
            DataGrid1.EditItemIndex = -1;
            BindData();
        }
    }
    protected void DataGrid1_ItemCommand(object source, DataGridCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {
            TextBox name = (TextBox)e.Item.FindControl("txtname_f");
            TextBox add = (TextBox)e.Item.FindControl("txtadd_f");
            con = new SqlConnection(dbcon);
            cmd = new SqlCommand("Insert into Emp values('" + name.Text + "','" + add.Text + "')", con);
            con.Open();
            int rows = cmd.ExecuteNonQuery();
            if (rows > 0)
            {
                Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Inserted", "<script>alert('Inserted Successfully')</script>");
                con.Close();
                BindData();
            }
        }
    }
}

In VB.NET  same code….

Imports System.Data.SqlClient
Imports System.Data
Partial Class Default2
    Inherits System.Web.UI.Page
    Dim ds As DataSet
    Dim da As SqlDataAdapter
    Dim cmd As SqlCommand  
Dim dbcon as string=ConfigurationManager.ConnectionString["AdvWorks"].ConnectionString;
Dim con as SqlConnection
    Public Sub Bind()
Con=new SqlConnection(dbcon)
        da = New SqlDataAdapter("select * from Emp", con)
        ds = New DataSet()
        da.Fill(ds)
        DataGrid1.DataSource = ds
        DataGrid1.DataBind()
    End
Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack
Then
            Bind()
        End
If
    End Sub
     Protected Sub btnadd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnadd.Click
       If btnadd.Text = "Add New Rec"
Then
            btnadd.Text = "Cancel"
            DataGrid1.ShowFooter = True
            Bind()
       
Else
            btnadd.Text = "Add New Rec"
            DataGrid1.ShowFooter = False
            Bind()
        End
If
    End Sub
    Protected Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
        If e.CommandName.Equals("AddNew")
Then
Con=new SqlConnection(dbcon)
            Dim name As TextBox = DirectCast(e.Item.FindControl("txtname_f"), TextBox)
            Dim add As TextBox = DirectCast(e.Item.FindControl("txtadd_f"), TextBox)
           
'Dim name As TextBox = DirectCast(DataGrid1.Items(e.Item.ItemIndex).FindControl("txtname_f"), TextBox)
            'Dim add As TextBox = DirectCast(DataGrid1.Items(e.Item.ItemIndex).FindControl("txtadd_f"), TextBox)
            cmd = New SqlCommand("Insert into Emp values('" & name.Text & "','" & add.Text & "')", con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            Bind()
        End
If
    End Sub
    Protected Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.UpdateCommand
Con=new SqlConnection(dbcon)
        Dim name As TextBox = DirectCast(DataGrid1.Items(e.Item.ItemIndex).FindControl("txtname_e"), TextBox)
        Dim add As TextBox = DirectCast(e.Item.FindControl("txtadd_e"), TextBox)
        cmd = New SqlCommand("Update Emp Set Empname='" & name.Text & "',EmpAdd ='" & add.Text & "'where Empid=" & DataGrid1.DataKeys(e.Item.ItemIndex), con)
        con.Open()
        cmd.ExecuteNonQuery()
        DataGrid1.EditItemIndex = -1
        con.Close()
        Bind()
    End
Sub
    Protected Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.EditCommand
        DataGrid1.EditItemIndex = e.Item.ItemIndex
        Bind()
    End
Sub
 
    Protected Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.DeleteCommand
Con=new SqlConnection(dbcon)
        con.Open()
        cmd = New SqlCommand("delete from Emp where EmpId=" & DataGrid1.DataKeys(e.Item.ItemIndex), con)
        cmd.ExecuteNonQuery()
        con.Close()
        Bind()
    End
Sub
End Class

Hope you liked the example and it might help you in your project.

With Regards,

Vishal Gilbile.


Similar Articles