Related Posts Plugin for WordPress, Blogger...

About

Follow Us

Saturday, 28 March 2015

Bind,Save,Edit,Update,Cancel,Delete and paging example in DetailsView in asp.net

Posted by Unknown on 00:25 with No comments so far
 DetailsView is a data control which is used to display single record from a data  table. It allows us to perform the operations like insert, edit, update and delete records.

In this article i will 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.

Implementation:
Create a database “Test”, Then create one table i.e. Emp_Personal
  
Column Name
Data Type
EmpPer_Id
Int(Primary Key. So set is identity=true)
EmpName
varchar(100)
Age
Int
Address
varchar(500)

Create a stored procedure to save Employee details in sql server database table

create PROCEDURE SaveEmp_SP

                @EmpName  VARCHAR(100),              
                @age   INT,
                @Address VARCHAR(500)

AS
BEGIN
                INSERT INTO dbo.Emp_Personal(EmpName,Age,Address)
                VALUES(@EmpName,@age,@Address)
END

Create a stored procedure to get Employee details in sql server database table

CREATE PROCEDURE GetEmp_SP              
AS
BEGIN
                SELECT * FROM dbo.Emp_Personal
END

Create a stored procedure to delete Employee details in sql server database table

create PROCEDURE DeleteEmp_Sp

@EmpId int
               
AS

BEGIN
              delete from dbo.Emp_Personal where Empper_id=@EmpId
END
Create a stored procedure to update  Employee details in sql server database table

create PROCEDURE [dbo].[UpdateEmp_SP]
 @EmpId int,
                @EmpName                               VARCHAR(100),
              
                @age                                                    INT,
                @Address                                           VARCHAR(500)
AS
BEGIN
              update dbo.Emp_Personal set EmpName=@EmpName,Age=@age,Address=@Address where Empper_id=@empId
END

create connection string  in web config:

<connectionStrings>
                                <add name="Empcon" connectionString="Data Source=localhost;Initial Catalog=test;Integrated Security=True"/>
                </connectionStrings>

Source Code:


  <fieldset style="width:250px";>
    <legend><strong>DetailsView Example in Asp.net</strong></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="EmpPer_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("EmpPer_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>
                    <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
write the code as:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls; using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class DetailView : System.Web.UI.Page
{
  SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Empcon"].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("GetEmp_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;

            cmd = new SqlCommand("SaveEmp_SP", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmpName", ename);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Parameters.AddWithValue("@Address", addr);
            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;

            cmd = new SqlCommand("UpdateEmp_SP", con);
            cmd.CommandType = CommandType.StoredProcedure;
            //Read the Emp_id from the DataKeynames
            empId = Convert.ToInt32(EmpDetailsView.DataKey["EmpPer_Id"]);
            cmd.Parameters.AddWithValue("@empId", empId);
            cmd.Parameters.AddWithValue("@EmpName", ename);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Parameters.AddWithValue("@Address", addr);
            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["EmpPer_Id"]);
            cmd = new SqlCommand("DeleteEmp_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:

write the code as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Partial Public Class DetailView
    Inherits System.Web.UI.Page
    Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Empcon").ConnectionString)
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        If Page.IsPostBack = False Then
            BindDetailView()
        End If
    End Sub

    Private  Bind  Sub New()
    Dim adp As SqlDataAdapter = New SqlDataAdapter()
    Dim ds As DataSet = New DataSet()
        Try
            adp = New SqlDataAdapter("GetEmp_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 DetailsViewModeEventArgs)
        EmpDetailsView.ChangeMode(e.NewMode)
        BindDetailView()
    End Sub
    Protected Sub EmpDetailsView_ItemInserting(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs)
        Dim cmd As SqlCommand = New SqlCommand()
        Dim age As Int32
        Dim ename As String = String.Empty
        Dim addr As String = String.Empty
        Dim sal As Double
        Try
            ename = (CType(EmpDetailsView.Rows(1).FindControl("txtName"), TextBox)).Text
            age = Convert.ToInt32((CType(EmpDetailsView.Rows(2).FindControl("txtAge"), TextBox)).Text)
            addr = (CType(EmpDetailsView.Rows(3).FindControl("txtAddress"), TextBox)).Text

            cmd = New SqlCommand("SaveEmp_SP", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@EmpName", ename)
            cmd.Parameters.AddWithValue("@age", age)
            cmd.Parameters.AddWithValue("@Address", addr)
            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 DetailsViewUpdateEventArgs)
        Dim cmd As SqlCommand = 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 = (CType(EmpDetailsView.Rows(1).FindControl("txtEditName"), TextBox)).Text
            age = Convert.ToInt32((CType(EmpDetailsView.Rows(2).FindControl("txtEditAge"), TextBox)).Text)
            addr = (CType(EmpDetailsView.Rows(3).FindControl("txtEditAddress"), TextBox)).Text

            cmd = New SqlCommand("UpdateEmp_SP", con)
            cmd.CommandType = CommandType.StoredProcedure
            'Read the Emp_id from the DataKeynames
            empId = Convert.ToInt32(EmpDetailsView.DataKey("EmpPer_Id"))
            cmd.Parameters.AddWithValue("@empId", empId)
            cmd.Parameters.AddWithValue("@EmpName", ename)
            cmd.Parameters.AddWithValue("@age", age)
            cmd.Parameters.AddWithValue("@Address", addr)
            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 DetailsViewDeleteEventArgs)
        Dim cmd As SqlCommand = New SqlCommand()
        Try
            'Read the Emp_id from the DataKeynames
            Dim empId As Int32 = Convert.ToInt32(EmpDetailsView.DataKey("EmpPer_Id"))
            cmd = New SqlCommand("DeleteEmp_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 DetailsViewPageEventArgs)
        EmpDetailsView.PageIndex = e.NewPageIndex
        BindDetailView()
    End Sub


End Class
Categories: , ,

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)