Bind,Save,Edit,Update,Cancel,Delete and paging example in DetailsView in asp.net(C#,VB.Net) ~ Asp.Net,C#.Net,VB.Net,MVC,jQuery,JavaScipt,AJAX,WCF,Sql Server example

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.

Description: DetailsView 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

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)

)

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)

)

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

BEGIN

DELETE FROM Emp_Tb WHERE Emp_Id=@EmpId

END

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

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

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

No Data<br />

<asp:LinkButton ID="lnlAddNew" runat="server" CommandName="new">Add New</asp:LinkButton>

<br />

</EmptyDataTemplate>

<asp:TemplateField HeaderText="Id">

<asp:Label ID="lblId" runat="server" Text='<%# Eval("Emp_Id") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Emp Name">

<asp:Label ID="lblName" runat="server" Text='<%# Eval("EmpName") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtEditName" runat="server" Text='<%# Eval("EmpName") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtName" runat="server"></asp:TextBox>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Age">

<asp:Label ID="lblAge" runat="server" Text='<%# Eval("Age") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtEditAge" runat="server" Text='<%# Eval("Age") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtAge" runat="server"></asp:TextBox>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Address">

<asp:Label ID="lblAddress" runat="server" Text='<%# Eval("Address") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtEditAddress" runat="server" Text='<%# Eval("Address") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Salary">

<asp:Label ID="lblSalry" runat="server" Text='<%# Eval("Salary") %>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtEditSalary" runat="server" Text='<%# Eval("Salary") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>

</InsertItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

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

<asp:LinkButton ID="lkEditUpdate" runat="server" CommandName="update">Update</asp:LinkButton>

<asp:LinkButton ID="lkEditCancel" runat="server" CommandName="cancel">Cancel</asp:LinkButton>

</EditItemTemplate>

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

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

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.

Lalit Raghuvanshi

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

Anonymous

October 01, 2013 ×

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

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

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.

November 29, 2013 ×

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

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

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

Daily Blogger

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

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

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

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

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

December 21, 2013 ×

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

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

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

Related Post

14 comments