Insert,Update,Delete Record From DataBase Using GridView Control In...

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

Hive account@vid int=null,

Hive account@vname varchar(50)=null,

Hive account@vlocation varchar(50)=null,

Hive account@vgender varchar(50)=null,

Hive account@status varchar(50)=null

BEGIN

SET NOCOUNT ON;

---INSERT NEW RECORDS

IF Hive account@status='INSERT'

BEGIN

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

END

---SELECT RECORDS IN TABLE

IF Hive account@status='SELECT'

BEGIN

SELECT vid,vname,vlocation,vgender FROM viewer

END

---UPDATE RECORDS IN TABLE

IF Hive account@status='UPDATE'

BEGIN

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

END

---DELETE RECORD FROM TABLE

IF Hive account@status='DELETE'

BEGIN

DELETE FROM viewer where vid=Hive account@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>

<body>

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

<asp:CommandField ShowSelectButton="True" />

<asp:CommandField ShowEditButton="True" />

<asp:CommandField ShowDeleteButton="True" />

<asp:TemplateField HeaderText="vid">

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

</EditItemTemplate>

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

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

</FooterTemplate>

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="vname">

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

</EditItemTemplate>

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

</FooterTemplate>

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="vlocation">

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

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

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

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="vgender">

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

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

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

</asp:DropDownList>

</EditItemTemplate>

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

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

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

</asp:DropDownList>

</FooterTemplate>

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

</ItemTemplate>

</asp:TemplateField>

</Columns>

</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=Hive account@vid",con);

cmd.Parameters.Add(new SqlParameter("Hive account@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=Hive account@vname,vlocation=Hive account@vlocation,vgender=Hive account@vgender where vid=Hive account@vid",con);

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

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

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

cmd.Parameters.Add(new SqlParameter("Hive account@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(Hive account@vid,Hive account@vname,Hive account@vlocation,Hive account@vgender)",con);

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

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

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

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

con.Open();

cmd.ExecuteNonQuery();

con.Close();

filldata();

}

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