Insert, Update and Delete using a Formview

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

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

                          &nbsp;

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

    }

}


X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now