Insert, Update and Delete using a Formview

Satyapriya Nayak
Oct 23, 2011

96.4k
0
4
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Bookmark

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

<title>Untitled Page</title>

</head>

<body>

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

<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>Address:</b></td> <td><%# Eval("Address")%></td></tr>

<tr><td align="right"><b>Designation:</b></td> <td><%# Eval("Designation")%></td></tr>

<tr>

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

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

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

<asp:LinkButton ID="UpdateButton"

Text="Update"

CommandName="Update"

RunAt="server"/>

<asp:LinkButton ID="CancelUpdateButton"

Text="Cancel"

CommandName="Cancel"

RunAt="server"/>

</td>

</tr>

</table>

</EditItemTemplate>

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

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

<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();

}