FIROZ DOT NET: Q. Update ,Insert,Delete Operation with FormView Using asp.net?

Step 1 . Create table name as Employee.

CREATE TABLE [dbo].[Employee](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](20) NOT NULL,
[FirstName] [nvarchar](10) NOT NULL,
[Address] [nvarchar](60) NULL,
[Country] [nvarchar](15) NULL,
[Designation] [nvarchar](30) NULL,
PRIMARY KEY CLUSTERED
(
[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Step 2. Insert Some record manual like below

SET IDENTITY_INSERT [dbo].[Employee] ON

INSERT [dbo].[Employee] ([EmpID], [LastName], [FirstName], [Address], [Country], [Designation]) VALUES (1, N'Firoz', N'Ansari', N'Hyderabad', N'India', N'Developer')
INSERT [dbo].[Employee] ([EmpID], [LastName], [FirstName], [Address], [Country], [Designation]) VALUES (2, N'Ansari', N'Tamanna', N'Hyderabad', N'India', N'Developer')
INSERT [dbo].[Employee] ([EmpID], [LastName], [FirstName], [Address], [Country], [Designation]) VALUES (3, N'Singh', N'MK', N'Hyderabad', N'India', N'Developer')
SET IDENTITY_INSERT [dbo].[Employee] OFF

Step 3. Write Connection String Code inside web.config

<connectionStrings>
<add name="constr" connectionString="Data Source=INDIA;Persist Security Info=False;Integrated Security=SSPI; Initial Catalog=Northwind;User Id=sa;Password=123;
Connect Timeout=30;"/>

</connectionStrings>

Step 4. Now open visual studio and create aspx page

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap4.min.css" />+
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js" type="text/javascript"></script>
</head>
<body>
<form id="form1" runat="server">
<div class="container py-3">
<h2 class="text-center text-uppercase">Form View</h2>
<div class="card">
<asp:FormView ID="EmployeeFormView" DataKeyNames="EmpID" GridLines="Both" AllowPaging="true"
runat="server" OnItemDeleting="EmployeeFormView_ItemDeleting"
OnPageIndexChanging="EmployeeFormView_PageIndexChanging"
OnItemUpdating="EmployeeFormView_ItemUpdating"
OnItemInserted="EmployeeFormView_ItemInserted"
OnItemInserting="EmployeeFormView_ItemInserting"
OnItemUpdated="EmployeeFormView_ItemUpdated"
OnModeChanging="EmployeeFormView_ModeChanging" BackColor="#FFFF66"
BorderColor="#FF8080" EmptyDataText="Data not available">
<HeaderStyle BackColor="Navy" ForeColor="#999966" />
<RowStyle BackColor="White" ForeColor="#CC3300" />
<EditRowStyle BackColor="#66FF99" ForeColor="#FF9966" />
<ItemTemplate>
<table>
<tr>
<td align="left"><b>Employee ID:-&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp</b></td>
<td><%# Eval("EmpID") %></td>
</tr>
<tr>
<td align="left"><b>First Name:-&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp</b></td>
<td><%# Eval("FirstName") %></td>
</tr>
<tr>
<td align="left"><b>Last Name:-&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp</b></td>
<td><%# Eval("LastName") %></td>
</tr>
<tr>
<td align="left"><b>Address:-&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp</b></td>
<td><%# Eval("Address")%></td>
</tr>

<tr>
<td align="left"><b>Designation:-&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp</b></td>
<td><%# Eval("Country")%></td>
</tr>
<tr>
<td align="left"><b>Designation:-&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp</b></td>
<td><%# Eval("Designation")%></td>
</tr>
<tr>
<td colspan="2">
<asp:LinkButton ID="EditButton" Text="Edit" CommandName="Edit" runat="server" />
 
<asp:LinkButton ID="NewButton" Text="New" CommandName="New" runat="server" />
 
<asp:LinkButton ID="DeleteButton" Text="Delete" CommandName="Delete" runat="server" />
</td>
</tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table>
<tr>
<td align="left"><b>Employee ID:</b></td>
<td>
<asp:TextBox ID="txtEmployeeID2" Text='<%# Bind("EmpID") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>First Name:</b></td>
<td>
<asp:TextBox ID="txtFirstName2" Text='<%# Bind("FirstName") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>Last Name:</b></td>
<td>
<asp:TextBox ID="txtLastName2" Text='<%# Bind("LastName") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>Address:</b></td>
<td>
<asp:TextBox ID="txtAddress2" Text='<%# Bind("Address") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>Country:</b></td>
<td>
<asp:TextBox ID="txtCountry2" Text='<%# Bind("Country") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>Designation:</b></td>
<td>
<asp:TextBox ID="txtDesignation2" Text='<%# Bind("Designation") %>' runat="Server" /></td>
</tr>
<tr>
<td colspan="2">
<asp:LinkButton ID="UpdateButton" Text="Update" CommandName="Update" runat="server" />
 
<asp:LinkButton ID="CancelUpdateButton" Text="Cancel" CommandName="Cancel" runat="server" />
</td>
</tr>
</table>
</EditItemTemplate>
<InsertItemTemplate>
<table>
<%--<tr>
<td align="right"><b>Employee ID:</b></td>
<td>
<asp:TextBox ID="txtEmployeeID1" Text='<%# Bind("EmpID") %>' runat="Server" /></td>
</tr>--%>

<tr>
<td align="left"><b>First Name:</b></td>

<td>
<asp:TextBox ID="txtFirstName1" Text='<%# Bind("FirstName") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>Last Name:</b></td>

<td>
<asp:TextBox ID="txtLastName1" Text='<%# Bind("LastName") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>Address:</b></td>
<td>
<asp:TextBox ID="txtAddress1" Text='<%# Bind("Address") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>Country:</b></td>
<td>
<asp:TextBox ID="txtCountry1" Text='<%# Bind("Country") %>' runat="Server" /></td>
</tr>
<tr>
<td align="left"><b>Designation:</b></td>
<td>
<asp:TextBox ID="txtDesignation1" Text='<%# Bind("Designation") %>' runat="Server" /></td>
</tr>
<tr>
<td colspan="2">
<asp:LinkButton ID="InsertButton" Text="Insert" CommandName="Insert" runat="server" />
 
<asp:LinkButton ID="CancelInsertButton" Text="Cancel" CommandName="Cancel" runat="server" />

</td>

</tr>

</table>

</InsertItemTemplate>
</asp:FormView>
</div>
</div>
</form>
</body>
</html>

Step 5. Now goto aspx.cs file and wrire the following code.

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

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

{

string connStr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

SqlDataAdapter sqlda = new SqlDataAdapter();

SqlCommand com = new SqlCommand();

DataTable dt;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

bindgrid();

}

private void bindgrid()

{

SqlConnection conn = new SqlConnection(connStr);

dt = new DataTable();

com.Connection = conn;

com.CommandText = "SELECT * FROM Employee";

sqlda = new SqlDataAdapter(com);

sqlda.Fill(dt);

EmployeeFormView.DataSource = dt;

EmployeeFormView.DataBind();

}

protected void EmployeeFormView_PageIndexChanging(object sender, FormViewPageEventArgs e)

{

EmployeeFormView.PageIndex = e.NewPageIndex;

bindgrid();

}

protected void EmployeeFormView_ItemDeleting(object sender, FormViewDeleteEventArgs e)

{

//string key = EmployeeFormView.DataKey["EmpID"].ToString();

DataKey key = EmployeeFormView.DataKey;

if (key.Value!=null)

{

SqlConnection conn = new SqlConnection(connStr);

com.Connection = conn;

com.CommandText = "DELETE FROM Employee WHERE EmpID='" + key+ "'";

conn.Open();

com.ExecuteNonQuery();

conn.Close();

Response.Write("Record deleted successfully");

bindgrid();

}

else

{

Response.Write("Record not deleted");

}

protected void EmployeeFormView_ItemUpdating(object sender, FormViewUpdateEventArgs e)

{

DataKey key = EmployeeFormView.DataKey;

if (key.Value != null)

{

TextBox txtFirstName = (TextBox)EmployeeFormView.FindControl("txtFirstName2");

TextBox txtLastName = (TextBox)EmployeeFormView.FindControl("txtLastName2");

TextBox txtAddress = (TextBox)EmployeeFormView.FindControl("txtAddress2");

TextBox txtCountry = (TextBox)EmployeeFormView.FindControl("txtCountry2");

TextBox txtDesignation = (TextBox)EmployeeFormView.FindControl("txtDesignation2");

SqlConnection conn = new SqlConnection(connStr);

com.Connection = conn;

com.CommandText = "UPDATE Employee SET FirstName ='" + txtFirstName.Text + "',LastName ='" + txtLastName.Text + "',Address ='" + txtAddress.Text + "',Country='" + txtCountry.Text + "',Designation='" + txtDesignation.Text + "' WHERE EmpID='" + key.Value.ToString() + "'";

conn.Open();

com.ExecuteNonQuery();

Response.Write("Record updated successfully");

bindgrid();

conn.Close();

txtFirstName.Text = "";

txtLastName.Text = "";

txtAddress.Text = "";

txtCountry.Text = "";

txtDesignation.Text = "";

}

else

{

Response.Write("Record not Updated");

}

protected void EmployeeFormView_ModeChanging(object sender, FormViewModeEventArgs e)

{

EmployeeFormView.ChangeMode(e.NewMode);

bindgrid();

if (e.NewMode == FormViewMode.Edit)

{

EmployeeFormView.AllowPaging = false;

}

else

{

EmployeeFormView.AllowPaging = true;

}

protected void EmployeeFormView_ItemInserted(object sender, FormViewInsertedEventArgs e)

{

EmployeeFormView.ChangeMode(FormViewMode.ReadOnly);

}

protected void EmployeeFormView_ItemUpdated(object sender, FormViewUpdatedEventArgs e)

{

EmployeeFormView.ChangeMode(FormViewMode.ReadOnly);

}

protected void EmployeeFormView_ItemInserting(object sender, FormViewInsertEventArgs e)

{

TextBox txtFirstName = (TextBox)EmployeeFormView.FindControl("txtFirstName1");

TextBox txtLastName = (TextBox)EmployeeFormView.FindControl("txtLastName1");

TextBox txtAddress = (TextBox)EmployeeFormView.FindControl("txtAddress1");

TextBox txtCountry = (TextBox)EmployeeFormView.FindControl("txtCountry1");

TextBox txtDesignation = (TextBox)EmployeeFormView.FindControl("txtDesignation1");

SqlConnection conn = new SqlConnection(connStr);

com.Connection = conn;

com.CommandText = "INSERT INTO Employee(FirstName,LastName,Address,Country,Designation) Values('" + txtFirstName.Text + "', '" + txtLastName.Text + "', '" + txtAddress.Text + "','" + txtCountry.Text+ "', '" + txtDesignation.Text + "')";

conn.Open();

com.ExecuteNonQuery();

Response.Write("Record inserted successfully");

bindgrid();

conn.Close();

txtFirstName.Text = "";

txtLastName.Text = "";

txtAddress.Text = "";

txtCountry.Text = "";

txtDesignation.Text = "";

}

Step 6.Now run the application the following page will appear.

Step 7.Whenever we will click on New link button then following page will come.

Note:- Form here you can enter some record and click on insert link button .

Step 8. Whenever click on Edit button then bellow page will apears.

FIROZ DOT NET

ASP DOT NET

Monday, August 20, 2018

Q. Update ,Insert,Delete Operation with FormView Using asp.net?

No comments:

Post a Comment

How to to select duplicate rows from sql server?

Search This Blog