Insert Update Delete using a Formview without using sqldatasource

Satyapriyanayak
Posted by Satyapriyanayak under ASP.NET category on | Points: 40 | Views : 1226
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<head runat="server"></head><body><form id="form1" runat="server">
<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><tdcolspan="2<asp:LinkButtonID="EditButton"Text="Edit"CommandName="Edit"RunAt="server"/>&nbsp;<asp:LinkButton ID="NewButton"Text="New"CommandName="New" RunAt="server"/>&nbsp;<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"/>&nbsp;<asp:LinkButtonID="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"/> &nbsp;<asp:LinkButtonID="CancelInsertButton"Text="Cancel"CommandName="Cancel"RunAt="server"/</td</tr</table</InsertItemTemplate</asp:FormView></form></body></html>
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 succesfully");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 succesfully");
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 succesfully");
bindgrid();conn.Close();
}}

Comments or Responses

Login to post response