In this blog we will know how to insert,
edit, update and delete using a form view control without using sqldatasource.
Here we can display the values of a single record from the database using
templates. Paging facilities are also provided in this control.
Default.aspx code
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!DOCTYPE html
PUBLIC "-//W3C//DTD
XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
</div>
<asp:FormView ID="EmployeeFormView"
DataKeyNames="EmployeeID"
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">
<HeaderStyle backcolor="Navy"
forecolor="#999966"/>
<RowStyle backcolor="White"
ForeColor="#CC3300"/>
<EditRowStyle backcolor="#66FF99" ForeColor="#FF9966"/>
<ItemTemplate>
<table>
<tr><td align="right"><b>Employee ID:</b></td><td><%# Eval("EmployeeID")
%></td></tr>
<tr><td align="right"><b>First Name:</b></td> <td><%# Eval("FirstName") %></td></tr>
<tr><td align="right"><b>Last Name:</b></td> <td><%# Eval("LastName")
%></td></tr>
<tr><td align="right"><b>Address:</b></td> <td><%# Eval("Address")%></td></tr>
<tr><td align="right"><b>Designation:</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="right"><b>Employee ID:</b></td>
<td><asp:TextBox ID="txtEmployeeID2"
Text='<%#
Bind("EmployeeID") %>'
RunAt="Server" /></td></tr>
<tr><td align="right"><b>First Name:</b></td>
<td><asp:TextBox ID="txtFirstName2"
Text='<%#
Bind("FirstName") %>'
RunAt="Server" /></td></tr>
<tr><td align="right"><b>Last Name:</b></td>
<td><asp:TextBox ID="txtLastName2"
Text='<%#
Bind("LastName") %>'
RunAt="Server" /></td></tr>
<tr><td align="right"><b>Address:</b></td>
<td><asp:TextBox ID="txtAddress2"
Text='<%#
Bind("Address") %>'
RunAt="Server" /></td></tr>
<tr><td align="right"><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("EmployeeID") %>'
RunAt="Server" /></td></tr>
<tr><td align="right"><b>First Name:</b></td>
<td><asp:TextBox ID="txtFirstName1"
Text='<%#
Bind("FirstName") %>'
RunAt="Server" /></td></tr>
<tr><td align="right"><b>Last Name:</b></td>
<td><asp:TextBox ID="txtLastName1"
Text='<%#
Bind("LastName") %>'
RunAt="Server" /></td></tr>
<tr><td align="right"><b>Address:</b></td>
<td><asp:TextBox ID="txtAddress1"
Text='<%#
Bind("Address") %>'
RunAt="Server" /></td></tr>
<tr><td align="right"><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>
</form>
</body>
</html>
Default.aspx.cs 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 _Default
: System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].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 Employees";
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)
{
DataKey key =
EmployeeFormView.DataKey;
SqlConnection conn = new SqlConnection(connStr);
com.Connection = conn;
com.CommandText = "DELETE
FROM Employees WHERE EmployeeID='" + key.Value.ToString() + "'";
conn.Open();
com.ExecuteNonQuery();
conn.Close();
Response.Write( "Record
deleted successfully");
bindgrid();
}
protected void EmployeeFormView_ItemUpdating(object sender, FormViewUpdateEventArgs
e)
{
DataKey key =
EmployeeFormView.DataKey;
TextBox txtFirstName = (TextBox)EmployeeFormView.FindControl("txtFirstName2");
TextBox txtLastName = (TextBox)EmployeeFormView.FindControl("txtLastName2");
TextBox txtAddress = (TextBox)EmployeeFormView.FindControl("txtAddress2");
TextBox txtDesignation =
(TextBox)EmployeeFormView.FindControl("txtDesignation2");
SqlConnection conn = new SqlConnection(connStr);
com.Connection = conn;
com.CommandText = "UPDATE
Employees SET FirstName ='" + txtFirstName.Text + "',LastName ='" + txtLastName.Text + "',Address ='" + txtAddress.Text + "',Designation ='" + txtDesignation.Text
+ "'
WHERE EmployeeID='" + key.Value.ToString() + "'";
conn.Open();
com.ExecuteNonQuery();
Response.Write("Record
updated successfully");
bindgrid();
conn.Close();
}
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 txtEmployeeID =
(TextBox)EmployeeFormView.FindControl("txtEmployeeID1");
TextBox txtFirstName = (TextBox)EmployeeFormView.FindControl("txtFirstName1");
TextBox txtLastName = (TextBox)EmployeeFormView.FindControl("txtLastName1");
TextBox txtAddress = (TextBox)EmployeeFormView.FindControl("txtAddress1");
TextBox txtDesignation =
(TextBox)EmployeeFormView.FindControl("txtDesignation1");
SqlConnection conn = new SqlConnection(connStr);
com.Connection = conn;
com.CommandText = "INSERT
INTO Employees Values('" + txtEmployeeID.Text + "','" + txtFirstName.Text + "', '" + txtLastName.Text + "', '" + txtAddress.Text + "', '" + txtDesignation.Text + "')";
conn.Open();
com.ExecuteNonQuery();
Response.Write("Record
inserted successfully");
bindgrid();
conn.Close();
}
}