Bind,Save,Edit,Update,Cancel,Delete and paging example in DetailsView in asp.net(C#,VB.Net)

Introduction: In this article i am going to explain with example How to perform Bind, Insert, Edit, Update, Cancel , Delete and paging operation in DetailsView in asp.net using both C# and Vb.Net language and using Stored procedure and Sql Server as a back end database.

DescriptionDetailsView is a data control that displays the values of a single record from a data source in a table, where each data row represents a field of the record. It allows us to perform the operations like insert, edit, update and delete records.

In previous articles i explained How to Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and Bind,save,edit,update,delete records from DataList and Bind, edit, update and delete data in Repeater and Custom paging in Repeater and WCF Service to bind,insert,edit,update,delete from sql server database and Searching records in gridview .

Bind,save,edit,update,cancel,delete and paging example in detailsview in asp.net

Implementation: Let's create an asp.net sample website to see the DetailsView operations in action.

  • First of all we need to create database in sql server e.g. "dbEmp" and in this database create a table with the Columns and Data type as shown in image below and name it "Emp_Tb"

Column Name
Data Type
Emp_Id
Int(Primary Key. So set Is Identity=True)
EmpName
varchar(100)
Age
int
Salary
decimal(10, 2)
City
varchar(100)
Address
varchar(500)

  • Create a Stored procedure to Bind data in DetailsView as:

CREATE PROCEDURE BindEmpGrid_Sp            
AS
BEGIN
                SELECT * FROM Emp_Tb
END
  • Create a stored procedure to Insert employee data

CREATE PROCEDURE InsertEmpRecord_Sp
                (
                                @ename                  varchar(100),
                                @age                     int,
                                @addr                  varchar                (500),
                                @sal                      decimal(10,2)
                )             
AS
                INSERT INTO Emp_Tb(EmpName,Age,Address,Salary)VALUES(@ename,@age,@addr,@sal)

  • Create a stored procedure to update employee data

CREATE PROCEDURE UpdateEmpRecord_Sp
                (              @empId                              int,
                                @ename                  varchar(100),
                                @age                     int,
                                @addr                  varchar                (500),
                                @sal                      decimal(10,2)
                )             
AS
                UPDATE Emp_Tb SET EmpName=@ename,Age=@age,Address=@addr,Salary=@sal WHERE Emp_Id=@empId

  • Create a stored procedure to delete employee data

CREATE PROCEDURE DeleteEmpRecord_Sp
                @EmpId INT
AS
BEGIN
                DELETE FROM Emp_Tb WHERE Emp_Id=@EmpId
END

  • Now In the web.config file create the connection string as:

<connectionStrings>
                                <add name="conStr" connectionString="Data Source=Lalit;Initial Catalog=dbEmp;Integrated Security=True"/>
                </connectionStrings>

Note: Replace the Data Source and Initial Catalog (i.e. Database name) as per your application.
In the design page (.aspx) place a DetailsView data control from the visual studio's toolbox and configure it as shown below:

Source Code:

<fieldset style="width:250px";>
    <legend>DetailsView Example in Asp.net</legend>     
        <asp:DetailsView ID="EmpDetailsView" runat="server" AllowPaging="True"
            AutoGenerateRows="False" Height="50px" Width="270px" CellPadding="4"
            onmodechanging="EmpDetailsView_ModeChanging"
            onpageindexchanging="EmpDetailsView_PageIndexChanging"
            onitemdeleting="EmpDetailsView_ItemDeleting"
            oniteminserting="EmpDetailsView_ItemInserting"
            onitemupdating="EmpDetailsView_ItemUpdating" DataKeyNames="Emp_Id"
            ForeColor="#333333" GridLines="None">
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />
            <EditRowStyle BackColor="#999999" />
            <EmptyDataTemplate>
                No Data<br />
                <asp:LinkButton ID="lnlAddNew" runat="server" CommandName="new">Add New</asp:LinkButton>
                <br />
                <br />
            </EmptyDataTemplate>
            <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />
            <Fields>               
                <asp:TemplateField HeaderText="Id">
                    <ItemTemplate>
                        <asp:Label ID="lblId" runat="server" Text='<%# Eval("Emp_Id") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Emp Name">
                <ItemTemplate>
                        <asp:Label ID="lblName" runat="server" Text='<%# Eval("EmpName") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEditName" runat="server" Text='<%# Eval("EmpName") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                    </InsertItemTemplate>                   
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Age">
                    <ItemTemplate>
                        <asp:Label ID="lblAge" runat="server" Text='<%# Eval("Age") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEditAge" runat="server" Text='<%# Eval("Age") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
                    </InsertItemTemplate>                   
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Address">
                    <ItemTemplate>
                        <asp:Label ID="lblAddress" runat="server" Text='<%# Eval("Address") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEditAddress" runat="server" Text='<%# Eval("Address") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
                    </InsertItemTemplate>                   
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Salary">
                    <ItemTemplate>
                        <asp:Label ID="lblSalry" runat="server" Text='<%# Eval("Salary") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="txtEditSalary" runat="server" Text='<%# Eval("Salary") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
                    </InsertItemTemplate>                   
                </asp:TemplateField>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:LinkButton ID="lkNew" runat="server" CommandName="new">New</asp:LinkButton>
                        <asp:LinkButton ID="lkEdit" runat="server" CommandName="edit">Edit</asp:LinkButton>
                        <asp:LinkButton ID="lkDelete" runat="server" CommandName="delete">Delete</asp:LinkButton>
                     </ItemTemplate>
                    <EditItemTemplate>
                        <asp:LinkButton ID="lkEditUpdate" runat="server" CommandName="update">Update</asp:LinkButton>
                        <asp:LinkButton ID="lkEditCancel" runat="server" CommandName="cancel">Cancel</asp:LinkButton>
                    </EditItemTemplate>
                    <InsertItemTemplate>
                        <asp:LinkButton ID="lkInsert" runat="server" CommandName="insert">Insert</asp:LinkButton>
                        <asp:LinkButton ID="lkCancel" runat="server" CommandName="cancel">Cancel</asp:LinkButton>
                    </InsertItemTemplate>                   
                </asp:TemplateField>
            </Fields>
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        </asp:DetailsView>
    </fieldset>


C#.Net Code to Bind, Insert, Edit, Update, Cancel and Delete from DetailsView
  • In the code behind file (.aspx.cs) write the code as:

First of all include the following required namespaces and write the code:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {
            BindDetailView();
        }
    }

    private void BindDetailView()
    {
        SqlDataAdapter adp = new SqlDataAdapter();
        DataSet ds = new DataSet();
        try
        {
            adp = new SqlDataAdapter("BindEmpGrid_Sp", con);
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;
            adp.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                EmpDetailsView.DataSource = ds;
                EmpDetailsView.DataBind();
            }
            else
            {
                EmpDetailsView.DataSource = null;
                EmpDetailsView.DataBind();
            }       
        }
        catch(Exception ex)
        {
            Response.Write("Oops!! Error occured: " + ex.Message.ToString());
        }
        finally
        {
            con.Close();
            ds.Dispose();
            adp.Dispose();
        }
    }     

    protected void EmpDetailsView_ModeChanging(object sender, DetailsViewModeEventArgs e)
    {
        EmpDetailsView.ChangeMode(e.NewMode);
        BindDetailView();
    }
    protected void EmpDetailsView_ItemInserting(object sender, DetailsViewInsertEventArgs e)
    {
        SqlCommand cmd = new SqlCommand();
        Int32 age;
        string ename = string.Empty;
        string addr = string.Empty;
        double sal;
        try
        {
            ename = ((TextBox)EmpDetailsView.Rows[1].FindControl("txtName")).Text;
            age = Convert.ToInt32(((TextBox)EmpDetailsView.Rows[2].FindControl("txtAge")).Text);
            addr = ((TextBox)EmpDetailsView.Rows[3].FindControl("txtAddress")).Text;
            sal = Convert.ToDouble(((TextBox)EmpDetailsView.Rows[4].FindControl("txtSalary")).Text);

            cmd = new SqlCommand("InsertEmpRecord_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ename", ename);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Parameters.AddWithValue("@addr", addr);
            cmd.Parameters.AddWithValue("@sal", sal);
            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            EmpDetailsView.ChangeMode(DetailsViewMode.ReadOnly);
            BindDetailView();
        }
        catch (Exception ex)
        {
            Response.Write("Oops !! Error Occured: " + ex.Message.ToString());
        }
        finally
        {
            cmd.Dispose();
            con.Close();
            ename = string.Empty;
            addr = string.Empty;
        }
    }
    protected void EmpDetailsView_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
    {
        SqlCommand cmd = new SqlCommand();
        Int32 age;
        Int32 empId;
        string ename = string.Empty;
        string addr = string.Empty;
        double sal;
        try
        {
            ename = ((TextBox)EmpDetailsView.Rows[1].FindControl("txtEditName")).Text;
            age = Convert.ToInt32(((TextBox)EmpDetailsView.Rows[2].FindControl("txtEditAge")).Text);
            addr = ((TextBox)EmpDetailsView.Rows[3].FindControl("txtEditAddress")).Text;
            sal = Convert.ToDouble(((TextBox)EmpDetailsView.Rows[4].FindControl("txtEditSalary")).Text);

            cmd = new SqlCommand("UpdateEmpRecord_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            //Read the Emp_id from the DataKeynames
            empId = Convert.ToInt32(EmpDetailsView.DataKey["Emp_Id"]);
            cmd.Parameters.AddWithValue("@empId", empId);
            cmd.Parameters.AddWithValue("@ename", ename);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Parameters.AddWithValue("@addr", addr);
            cmd.Parameters.AddWithValue("@sal", sal);
            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            EmpDetailsView.ChangeMode(DetailsViewMode.ReadOnly);
            BindDetailView();
        }
        catch (Exception ex)
        {
            Response.Write("Oops !! Error Occured: " + ex.Message.ToString());
        }
        finally
        {
            cmd.Dispose();
            con.Close();
            ename = string.Empty;
            addr = string.Empty;
        }
    } 
    protected void EmpDetailsView_ItemDeleting(object sender, DetailsViewDeleteEventArgs e)
    {
        SqlCommand cmd=new SqlCommand(); 
        try
        {
            //Read the Emp_id from the DataKeynames
            Int32 empId = Convert.ToInt32(EmpDetailsView.DataKey["Emp_Id"]);
            cmd = new SqlCommand("DeleteEmpRecord_Sp", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;
            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();           
            BindDetailView();
        }
        catch (Exception ex)
        {
            Response.Write("Oops !! Error Occured: " + ex.Message.ToString());
        }
        finally
        {
            con.Close();
            cmd.Dispose();
        }
    }

    protected void EmpDetailsView_PageIndexChanging(object sender, DetailsViewPageEventArgs e)
    {
        EmpDetailsView.PageIndex = e.NewPageIndex;
        BindDetailView();
    }


VB.Net Code to Bind, Insert, Edit, Update, Cancel and Delete from DetailsView
  • Design the page as described in Source Code section above but replace the lines            onmodechanging="EmpDetailsView_ModeChanging"            onpageindexchanging="EmpDetailsView_PageIndexChanging"            onitemdeleting="EmpDetailsView_ItemDeleting"            oniteminserting="EmpDetailsView_ItemInserting"            onitemupdating="EmpDetailsView_ItemUpdating" from the DetailsView source code.
  • In the code behind file(.aspx.vb) write the code as:

First import the following namespaces and write the code as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            BindDetailView()
        End If
    End Sub

    Private Sub BindDetailView()
        Dim adp As New SqlDataAdapter()
        Dim ds As New DataSet()
        Try
            adp = New SqlDataAdapter("BindEmpGrid_Sp", con)
            adp.SelectCommand.CommandType = CommandType.StoredProcedure
            adp.Fill(ds)
            If ds.Tables(0).Rows.Count > 0 Then
                EmpDetailsView.DataSource = ds
                EmpDetailsView.DataBind()
            Else
                EmpDetailsView.DataSource = Nothing
                EmpDetailsView.DataBind()
            End If
        Catch ex As Exception
            Response.Write("Oops!! Error occured: " & ex.Message.ToString())
        Finally
            con.Close()
            ds.Dispose()
            adp.Dispose()
        End Try
    End Sub

    Protected Sub EmpDetailsView_ModeChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewModeEventArgs) Handles EmpDetailsView.ModeChanging
        EmpDetailsView.ChangeMode(e.NewMode)
        BindDetailView()
    End Sub

    Protected Sub EmpDetailsView_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles EmpDetailsView.ItemInserting
        Dim cmd As New SqlCommand()
        Dim age As Int32
        Dim ename As String = String.Empty
        Dim addr As String = String.Empty
        Dim sal As Double
        Try
            ename = DirectCast(EmpDetailsView.Rows(1).FindControl("txtName"), TextBox).Text
            age = Convert.ToInt32(DirectCast(EmpDetailsView.Rows(2).FindControl("txtAge"), TextBox).Text)
            addr = DirectCast(EmpDetailsView.Rows(3).FindControl("txtAddress"), TextBox).Text
            sal = Convert.ToDouble(DirectCast(EmpDetailsView.Rows(4).FindControl("txtSalary"), TextBox).Text)

            cmd = New SqlCommand("InsertEmpRecord_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@ename", ename)
            cmd.Parameters.AddWithValue("@age", age)
            cmd.Parameters.AddWithValue("@addr", addr)
            cmd.Parameters.AddWithValue("@sal", sal)
            con.Open()
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            EmpDetailsView.ChangeMode(DetailsViewMode.[ReadOnly])
            BindDetailView()
        Catch ex As Exception
            Response.Write("Oops !! Error Occured: " & ex.Message.ToString())
        Finally
            cmd.Dispose()
            con.Close()
            ename = String.Empty
            addr = String.Empty
        End Try
    End Sub

    Protected Sub EmpDetailsView_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles EmpDetailsView.ItemUpdating
        Dim cmd As New SqlCommand()
        Dim age As Int32
        Dim empId As Int32
        Dim ename As String = String.Empty
        Dim addr As String = String.Empty
        Dim sal As Double
        Try
            ename = DirectCast(EmpDetailsView.Rows(1).FindControl("txtEditName"), TextBox).Text
            age = Convert.ToInt32(DirectCast(EmpDetailsView.Rows(2).FindControl("txtEditAge"), TextBox).Text)
            addr = DirectCast(EmpDetailsView.Rows(3).FindControl("txtEditAddress"), TextBox).Text
            sal = Convert.ToDouble(DirectCast(EmpDetailsView.Rows(4).FindControl("txtEditSalary"), TextBox).Text)

            cmd = New SqlCommand("UpdateEmpRecord_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            'Read the Emp_id from the DataKeynames
            empId = Convert.ToInt32(EmpDetailsView.DataKey("Emp_Id"))
            cmd.Parameters.AddWithValue("@empId", empId)
            cmd.Parameters.AddWithValue("@ename", ename)
            cmd.Parameters.AddWithValue("@age", age)
            cmd.Parameters.AddWithValue("@addr", addr)
            cmd.Parameters.AddWithValue("@sal", sal)
            con.Open()
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            EmpDetailsView.ChangeMode(DetailsViewMode.[ReadOnly])
            BindDetailView()
        Catch ex As Exception
            Response.Write("Oops !! Error Occured: " & ex.Message.ToString())
        Finally
            cmd.Dispose()
            con.Close()
            ename = String.Empty
            addr = String.Empty
        End Try
    End Sub

    Protected Sub EmpDetailsView_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs) Handles EmpDetailsView.ItemDeleting
        Dim cmd As New SqlCommand()
        Try
            'Read the Emp_id from the DataKeynames
            Dim empId As Int32 = Convert.ToInt32(EmpDetailsView.DataKey("Emp_Id"))
            cmd = New SqlCommand("DeleteEmpRecord_Sp", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId
            con.Open()
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            BindDetailView()
        Catch ex As Exception
            Response.Write("Oops !! Error Occured: " & ex.Message.ToString())
        Finally
            con.Close()
            cmd.Dispose()
        End Try
    End Sub

    Protected Sub EmpDetailsView_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewPageEventArgs) Handles EmpDetailsView.PageIndexChanging
        EmpDetailsView.PageIndex = e.NewPageIndex
        BindDetailView()
    End Sub

 Now over to you:
"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

14 comments

Click here for comments
Unknown
admin
September 26, 2013 ×

Hello Sir,
I am Akshay Sathe and i want to learn LINQ.
Please give some tips and example about same.

Reply
avatar
September 26, 2013 ×

Hello akshay..i will create and post the articles on LINQ next week..so keep reading for more useful updates..

Reply
avatar
Anonymous
admin
October 01, 2013 ×

With this example, how to hide the edit options based on if the user has admin rights?

Reply
avatar
Anonymous
admin
October 09, 2013 ×

Private Sub detailsView1_DataBound(ByVal Sender As Object, ByVal e As System.EventArgs) Handles DetailsView1.DataBound

If Not Session("RoleID") = 1 Or Session("RoleID") = 2 Then
DetailsView1.AutoGenerateEditButton = False
Else
DetailsView1.AutoGenerateEditButton = True
End If
End Sub

Reply
avatar
Anonymous
admin
November 28, 2013 ×

Sir, i just searched such kind of post and got it.thanks
i have practiced and found the problem-

Oops !! Error Occured: Cannot insert the value NULL into column 'Stu_Id', table 'School_Solution.dbo.student'; column does not allow nulls. INSERT fails. The statement has been terminated.

Reply
avatar
November 29, 2013 ×

have you set the 'Stu_Id' as the primary key or not?

Reply
avatar
Anonymous
admin
November 29, 2013 ×

'Stu_Id' is primary key.I am using Visual ,SQL 2012 and 'Is Identity' option not founding. it is causes for error?

Reply
avatar
November 29, 2013 ×

go to article http://www.webcodeexpert.com/2013/06/send-email-to-multiple-users-based-on.html and check the second image..it will help you to sort out your problem..

Reply
avatar
December 03, 2013 ×

Sir I have request Can u please start a section for C# also
There is not such a good blog for c#..
Please sir think about it
I hope you will start a section for c# programs soon :)
Thanks
Azad chouhan

Reply
avatar
December 03, 2013 ×

Hello sir where is the code for insert new record if there is no data in database then how can we add new record on click of linkbutton addnew

Reply
avatar
December 03, 2013 ×

Hello Azad..code to insert new record in database is already written on EmpDetailsView_ItemInserting event..plz check again..

Reply
avatar
December 03, 2013 ×

Hi Azad..thanks for your suggestion..i will create a section on C# as per your suggestion very soon..so keep reading..:)

Reply
avatar
December 04, 2013 ×

sir i mean to say if there is no record in database then how can i add new record I click on add new record link button but nothing is happen please sir clear my this doubt why the textboxes are not display on add new record

Reply
avatar
Unknown
admin
December 21, 2013 ×

hello sir, please sir share code Funeral sub code like g+,facebook, tweet in asp.net

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..