.NET Tutorials avatar

Insert,Update,Delete Record From DataBase Using GridView Control In Asp.Net C#

virajtakke09

Published: 02 May 2019 › Updated: 02 May 2019Insert,Update,Delete Record From DataBase Using GridView Control In Asp.Net C#

Insert,Update,Delete Record From DataBase Using GridView Control In Asp.Net C#

DATABASE

create database tension

create table viewer

(

vid int NOT NULL PRIMARY KEY,

vname varchar(50) NULL,

vlocation varchar(50) NULL,

vgender varchar(50) NULL,

);

CREATE PROCEDURE pro

vid@vid int=null,

vname@vname varchar(50)=null,

vlocation@vlocation varchar(50)=null,

vgender@vgender varchar(50)=null,

status@status varchar(50)=null

AS 

BEGIN 

SET NOCOUNT ON;

---INSERT NEW RECORDS

IF status@status='INSERT'

BEGIN

INSERT INTO viewer(vid,vname,vlocation,vgender)VALUES(vid@vid,vname@vname,vlocation@vlocation,vgender@vgender)

END

---SELECT RECORDS IN TABLE

IF status@status='SELECT'

BEGIN

SELECT vid,vname,vlocation,vgender FROM viewer

END

---UPDATE RECORDS IN TABLE

IF status@status='UPDATE'

BEGIN

UPDATE viewer SET vname=vname@vname,vlocation=vlocation@vlocation,vgender=vgender@vgender WHERE vid=vid@vid

END

---DELETE RECORD FROM TABLE

IF status@status='DELETE'

BEGIN

DELETE FROM viewer where vid=vid@vid

END

SET NOCOUNT OFF

END

insert into viewer values(1,'VIRAJ','MUMBAI','MALE')

%@ Page Language="C#" AutoEventWireup="true" CodeFile="vid.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html>

<head runat="server">

    <title></title>

</head>

<body>

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

    <div>

    

        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" ShowFooter="True" DataKeyNames="vid" OnRowDeleting="delete" OnRowUpdating="update" OnRowCancelingEdit="canceledit" OnRowEditing="edit" OnPageIndexChanging="Pages">

            <Columns>

                <asp:CommandField ShowSelectButton="True" />

                <asp:CommandField ShowEditButton="True" />

                <asp:CommandField ShowDeleteButton="True" />

                <asp:TemplateField HeaderText="vid">

                    <EditItemTemplate>

                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("vid") %>'></asp:TextBox>

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:Button ID="Button1" runat="server" Text="INSERT" OnClick="Button1_Click" />

                        &nbsp;&nbsp;

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

                    </FooterTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("vid") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="vname">

                    <EditItemTemplate>

                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("vname") %>'></asp:TextBox>

                    </EditItemTemplate>

                    <FooterTemplate>

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

                    </FooterTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("vname") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="vlocation">

                    <EditItemTemplate>

                        <asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%# Bind("vlocation") %>'>

                            <asp:ListItem>--SELECT LOCATION--</asp:ListItem>

                            <asp:ListItem>MUMBAI</asp:ListItem>

                            <asp:ListItem>NAVI MUMBAI</asp:ListItem>

                            <asp:ListItem>THANE</asp:ListItem>

                        </asp:DropDownList>

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:DropDownList ID="DropDownList2" runat="server">

                            <asp:ListItem>--SELECT LOCATION--</asp:ListItem>

                            <asp:ListItem>MUMBAI</asp:ListItem>

                            <asp:ListItem>NAVI MUMBAI</asp:ListItem>

                            <asp:ListItem>THANE</asp:ListItem>

                        </asp:DropDownList>

                    </FooterTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("vlocation") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="vgender">

                    <EditItemTemplate>

                        <asp:DropDownList ID="DropDownList3" runat="server" SelectedValue='<%# Bind("vgender") %>'>

                            <asp:ListItem>MALE</asp:ListItem>

                            <asp:ListItem>FEMALE</asp:ListItem>

                        </asp:DropDownList>

                    </EditItemTemplate>

                    <FooterTemplate>

                        <asp:DropDownList ID="DropDownList4" runat="server">

                            <asp:ListItem>MALE</asp:ListItem>

                            <asp:ListItem>FEMALE</asp:ListItem>

                        </asp:DropDownList>

                    </FooterTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label4" runat="server" Text='<%# Bind("vgender") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />

            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />

            <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />

            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />

            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />

            <SortedAscendingCellStyle BackColor="#FFF1D4" />

            <SortedAscendingHeaderStyle BackColor="#B95C30" />

            <SortedDescendingCellStyle BackColor="#F1E5CE" />

            <SortedDescendingHeaderStyle BackColor="#93451F" />

        </asp:GridView>

    

    </div>

    </form>

</body>

</html>

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

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

{

    SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

    protected void Page_Load(object sender, EventArgs e)

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        if(!Page.IsPostBack)

        {

            filldata();

        }

     

    }

    public void filldata()

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        SqlDataAdapter da = new SqlDataAdapter("select *from viewer",con);

        DataSet ds = new DataSet();

        da.Fill(ds);

        GridView1.DataSource = ds.Tables[0];

        GridView1.DataBind();

        GridView1.SelectedIndex = 0;

    }

    protected void delete(object sender,GridViewDeleteEventArgs e)

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        SqlCommand cmd = new SqlCommand("delete from viewer where vid=vid@vid",con);

        cmd.Parameters.Add(new SqlParameter("vid@vid",GridView1.DataKeys[e.RowIndex].Values[0]));

        con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        filldata();

    }

    protected void update(object sender,GridViewUpdateEventArgs e)

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        TextBox vid = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox1");

        TextBox vname = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TextBox2");

        DropDownList vlocation = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("DropDownList1");

        DropDownList vgender = (DropDownList)GridView1.Rows[e.RowIndex].FindControl("DropDownList3");

        SqlCommand cmd = new SqlCommand("update viewer set vname=vname@vname,vlocation=vlocation@vlocation,vgender=vgender@vgender where vid=vid@vid",con);

        cmd.Parameters.Add(new SqlParameter("vid@vid",vid.Text));

        cmd.Parameters.Add(new SqlParameter("vname@vname", vname.Text));

        cmd.Parameters.Add(new SqlParameter("vlocation@vlocation", vlocation.SelectedItem.Text));

        cmd.Parameters.Add(new SqlParameter("vgender@vgender", vgender.SelectedItem.Text));

        con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        GridView1.EditIndex = -1;

        filldata();

    }

    protected void canceledit(object sender, GridViewCancelEditEventArgs e)

    {

        GridView1.EditIndex = -1;

        filldata();

    }

    protected void edit(object sender, GridViewEditEventArgs e)

    {

        GridView1.EditIndex = e.NewEditIndex;

        filldata();

    }

    protected void Pages(object sender,GridViewPageEventArgs e)

    {

        GridView1.EditIndex = e.NewPageIndex;

        filldata();

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

        SqlConnection con = new SqlConnection("server=.;database=tension;uid=sa;pwd=user@123");

        TextBox vid = (TextBox)GridView1.FooterRow.FindControl("TextBox3");

        TextBox vname = (TextBox)GridView1.FooterRow.FindControl("TextBox4");

        DropDownList vlocation = (DropDownList)GridView1.FooterRow.FindControl("DropDownList2");

        DropDownList vgender = (DropDownList)GridView1.FooterRow.FindControl("DropDownList4");

        SqlCommand cmd = new SqlCommand("insert into viewer values(vid@vid,vname@vname,vlocation@vlocation,vgender@vgender)",con);

        cmd.Parameters.Add(new SqlParameter("vid@vid", vid.Text));

        cmd.Parameters.Add(new SqlParameter("vname@vname", vname.Text));

        cmd.Parameters.Add(new SqlParameter("vlocation@vlocation", vlocation.SelectedItem.Text));

        cmd.Parameters.Add(new SqlParameter("vgender@vgender", vgender.SelectedItem.Text));

        con.Open();

        cmd.ExecuteNonQuery();

        con.Close();

        filldata();

    }

}

Leave Insert,Update,Delete Record From DataBase Using GridView Control In Asp.Net C# to:

Written by

.NET Tutorials

Read more #programming posts

Best Posts From .NET Tutorials

We have not curated any of virajtakke09's posts yet. But you can encourage our curation team to review posts by visiting them regularly and by referring other readers. Because we give priority to frequently read content.

More Posts From .NET Tutorials