Insert, Edit, Update, Delete Gridview in ASP.Net

– In this Post we will learn how to Display Data in Gridview and Edit,Update and Delete from Gridview in ASP.Net.

– First we have to make Connection between SQL-Server and Our .Net website.

STEP 1-

– Create Database in SQL-Server DatabaseName: – Gridview
– Create Table in Database TabelName :- User_Mst

Create SQL Table — Create Table SQL-Server

– Create Stored_Procedure for SELECT, INSERT, UPDATE and DELETE in SQL-Server

Create Stored Procedure for SELECT Data

CREATE PROCEDURE [dbo].[USER_MST_SELECT]
AS
BEGIN
SELECT * FROM USER_MST-
END

Create Stored Procedure for INSERT Data

CREATE PROCEDURE [dbo].[USER_MST_INSERT]
@NAME AS NVARCHAR(256),
@SURNAME AS NVARCHAR(256),
@ADD AS NVARCHAR(256),
@CITY AS NVARCHAR(256),
@PIN AS INT,
@UNAME AS NVARCHAR(256),
@UPASSWORD AS NVARCHAR(256)
AS
BEGIN
INSERT INTO USER_MST VALUES (@NAME,@SURNAME,@ADD,@CITY,@PIN,@UNAME,@UPASSWORD)
END

Create Stored Procedure for UPDATE Data

CREATE PROCEDURE [dbo].[USER_MST_update]
@name as nvarchar(256),
@surname as nvarchar(256),
@city as nvarchar(256),
@id as int
AS
BEGIN
update USER_MST set NAME=@name,SURNAME=@surname,CITY=@city where ID=@id
END

Create Stored Procedure for DELETE Data

CREATE PROCEDURE [dbo].[USER_MST_Delete]
@ID as int
AS
BEGIN
delete from USER_MST WHERE ID=@ID
END

STEP 2 –

Now, Bind all Stored Procedure with .net application as we learn ago post, connection between asp.net and SQL-server.

Then, write below code in BODY tag for Design Page:-

<body>
<form id=”form1″ runat=”server”>
<div>
<table id=”TABLE1″ language=”javascript” onclick=”return TABLE1_onclick()” style=”z-index: 100;
left: 360px; position: absolute; top: 24px”>
<tr>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
</tr>
<tr>
<td style=”width: 100px”>
NAME:</td>
<td style=”width: 100px”>
<asp:TextBox ID=”txtname” runat=”server”></asp:TextBox></td>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
</tr>
<tr>
<td style=”width: 100px”>
SURNAME:</td>
<td style=”width: 100px”>
<asp:TextBox ID=”txtsurname” runat=”server” ></asp:TextBox></td>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
</tr>
<tr>
<td style=”width: 100px”>
ADDRESS:</td>
<td style=”width: 100px”>
<asp:TextBox ID=”txtaddress” runat=”server”></asp:TextBox></td>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
</tr>
<tr>
<td style=”width: 100px”>
CITY:</td>
<td style=”width: 100px”>
<asp:TextBox ID=”txtcity” runat=”server”></asp:TextBox></td>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
</tr>
<tr>
<td style=”width: 100px; height: 26px”>
PIN:</td>
<td style=”width: 100px; height: 26px”>
<asp:TextBox ID=”txtpin” runat=”server” ></asp:TextBox></td>
<td style=”width: 100px; height: 26px”>
</td>
<td style=”width: 100px; height: 26px”>
</td>
</tr>
<tr>
<td style=”width: 100px”>
USERNAME:</td>
<td style=”width: 100px”>
<asp:TextBox ID=”txtusername” runat=”server” ></asp:TextBox></td>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
</tr>
<tr>
<td style=”width: 100px”>
PASSWORD:</td>
<td style=”width: 100px”>
<asp:TextBox ID=”txtpassword” runat=”server” TextMode=”Password” Width=”152px”></asp:TextBox></td>
<td style=”width: 100px”>
</td>
<td style=”width: 100px”>
</td>
</tr>
<tr>
<td style=”width: 100px; height: 34px”>
</td>
<td style=”width: 100px; height: 34px”>
<asp:Button ID=”BTNSUBMIT” runat=”server” Font-Bold=”True” Height=”40px” OnClick=”BTNSUBMIT_Click”
Text=”SUBMIT” Width=”96px” /></td>
<td style=”width: 100px; height: 34px”>
</td>
<td style=”width: 100px; height: 34px”>
</td>
</tr>
<tr>
<td style=”width: 100px; height: 26px”>
</td>
<td style=”width: 100px; height: 26px”>
 <asp:Label ID=”lblmsg” runat=”server” Font-Bold=”True” ForeColor=”#C04000″></asp:Label>
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” AutoGenerateDeleteButton=”True”
AutoGenerateEditButton=”True” AutoGenerateSelectButton=”True” CellPadding=”4″
DataKeyNames=”id” ForeColor=”#333333″ GridLines=”None” OnRowCancelingEdit=”GridView1_RowCancelingEdit”
OnRowDeleting=”GridView1_RowDeleting” OnRowEditing=”GridView1_RowEditing”
OnRowUpdating=”GridView1_RowUpdating” >
<Columns>
<asp:TemplateField HeaderText=”Name”>
<ItemTemplate>
<asp:Label ID=”lblname” runat=”server” Text='<%#Eval(“name”) %>’>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=”txtname” runat=”server” Text='<%#Eval(“name”) %>’>
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”SurName”>
<ItemTemplate>
<asp:Label ID=”lblsurname” runat=”server” Text='<%#Eval(“surname”) %>’>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=”txtsurname” runat=”server” Text='<%#Eval(“surname”) %>’>
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”City”>
<ItemTemplate>
<asp:Label ID=”txtcity” runat=”server” Text='<%#Eval(“City”) %>’>
</asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID=”txtcity” runat=”server” Text='<%#Eval(“City”) %>’>
</asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>

</Columns>
<RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
<FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
<SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
<HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<EditRowStyle BackColor=”#999999″ />
<AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />
</asp:GridView>
</td>
<td style=”width: 100px; height: 26px”>
</td>
<td style=”width: 100px; height: 26px”>
</td>
</tr>
<tr>
<td colspan=”3″>
  
</td>
<td colspan=”1″>
</td>
</tr>
</table>

</div>
</form>
</body>

– Now, write below code in Page_load

DS_Grid.USERMST_SELECTDataTable GridDt = new DS_Grid.USERMST_SELECTDataTable();
DS_GridTableAdapters.USERMST_SELECTTableAdapter GridAdapter = new DS_GridTableAdapters.USERMST_SELECTTableAdapter();

protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false)
{
GridDt = GridAdapter.SelectUser();
GridView1.DataSource = GridDt;
GridView1.DataBind();
}
lblmsg.Text = “”;
}

– Insert Data into Gridview in ASP.Net

– write below code in SUBMIT button Click_Event

protected void BTNSUBMIT_Click(object sender, EventArgs e)
{
int insetdata = GridAdapter.Insert(txtname.Text, txtsurname.Text, txtaddress.Text, txtcity.Text, Convert.ToInt32(txtpin.Text), txtusername.Text, txtpassword.Text);

GridDt = GridAdapter.SelectUser();
GridView1.DataSource = GridDt;
GridView1.DataBind();
lblmsg.Text = “Record Inserted Successfully !”;
}

– Now, Edit Data Gridview in ASP.Net

– protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
GridDt = GridAdapter.SelectUser();
GridView1.DataSource = GridDt;
GridView1.DataBind();

}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridDt = GridAdapter.SelectUser();
GridView1.DataSource = GridDt;
GridView1.DataBind();
}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridView1.EditIndex = -1;
TextBox txtnewname = (TextBox)GridView1.Rows[e.RowIndex].FindControl(“txtname”);
TextBox txtnewsurname = (TextBox)GridView1.Rows[e.RowIndex].FindControl(“txtsurname”);
TextBox txtnewcity = (TextBox)GridView1.Rows[e.RowIndex].FindControl(“txtcity”);
GridAdapter.Update(txtnewname.Text, txtnewsurname.Text,txtnewcity.Text,Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value));

GridDt = GridAdapter.SelectUser();
GridView1.DataSource = GridDt;
GridView1.DataBind();
lblmsg.Text = “Record Updated Successfully !”;
}

– Now Delete Data from Gridview in ASP.Net

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
GridAdapter.Delete(Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value));
GridDt = GridAdapter.SelectUser();
GridView1.DataSource = GridDt;
GridView1.DataBind();
lblmsg.Text = “Record Deleted Successfully !”;

}

We hope this will helpful to all………………..