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 int=null,
@vname varchar(50)=null,
@vlocation varchar(50)=null,
@vgender varchar(50)=null,
@status varchar(50)=null
AS
BEGIN
SET NOCOUNT ON;
---INSERT NEW RECORDS
IF @status='INSERT'
BEGIN
INSERT INTO viewer(vid,vname,vlocation,vgender)VALUES(@vid,@vname,@vlocation,@vgender)
END
---SELECT RECORDS IN TABLE
IF @status='SELECT'
BEGIN
SELECT vid,vname,vlocation,vgender FROM viewer
END
---UPDATE RECORDS IN TABLE
IF @status='UPDATE'
BEGIN
UPDATE viewer SET vname=@vname,vlocation=@vlocation,vgender=@vgender WHERE vid=@vid
END
---DELETE RECORD FROM TABLE
IF @status='DELETE'
BEGIN
DELETE FROM viewer where 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 xmlns="http://www.w3.org/1999/xhtml">
<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" />
<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",con);
cmd.Parameters.Add(new SqlParameter("@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,vlocation=@vlocation,vgender=@vgender where vid=@vid",con);
cmd.Parameters.Add(new SqlParameter("@vid",vid.Text));
cmd.Parameters.Add(new SqlParameter("@vname", vname.Text));
cmd.Parameters.Add(new SqlParameter("@vlocation", vlocation.SelectedItem.Text));
cmd.Parameters.Add(new SqlParameter("@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,@vname,@vlocation,@vgender)",con);
cmd.Parameters.Add(new SqlParameter("@vid", vid.Text));
cmd.Parameters.Add(new SqlParameter("@vname", vname.Text));
cmd.Parameters.Add(new SqlParameter("@vlocation", vlocation.SelectedItem.Text));
cmd.Parameters.Add(new SqlParameter("@vgender", vgender.SelectedItem.Text));
con.Open();
cmd.ExecuteNonQuery();
con.Close();
filldata();
}
}
Congratulations @virajtakke09! You received a personal award!
You can view your badges on your Steem Board and compare to others on the Steem Ranking
Do not miss the last post from @steemitboard:
Vote for @Steemitboard as a witness to get one more award and increased upvotes!