GridView Add,Edit,Update and Delete in asp.net

StaffAttendance.aspx



<%@ Page Title="" Language="C#" MasterPageFile="~/SuccessPlus/Admin/adminsuccess.master" AutoEventWireup="true" CodeFile="StaffAttendance.aspx.cs" Inherits="SuccessPlus_Admin_Default2" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
 <input id="hdndranch" runat="server" type="hidden" />
 <input id="hdnmanager" runat="server" type="hidden" />
<style type="text/css" >
.Gridview
{
font-family:Verdana;
font-size:10pt;
font-weight:normal;
color:black;

}
</style>
<script type="text/javascript">
    function ConfirmationBox(username) {

        var result = confirm('Are you sure you want to delete ' + username + ' Details?');
        if (result) {

            return true;
        }
        else {
            return false;
        }
    }
</script>
<div>
<asp:GridView ID="gvDetails" DataKeyNames="rid,attid" runat="server" width="100%"
        AutoGenerateColumns="false" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8"
ShowFooter="true" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White"
        onrowcancelingedit="gvDetails_RowCancelingEdit"
        onrowdeleting="gvDetails_RowDeleting" onrowediting="gvDetails_RowEditing"
        onrowupdating="gvDetails_RowUpdating"
        onrowcommand="gvDetails_RowCommand" ondatabinding="gvDetails_DataBinding"
        ondatabound="gvDetails_DataBound" onrowdatabound="gvDetails_RowDataBound1">
   
<Columns>
<asp:TemplateField>
<EditItemTemplate>
<asp:ImageButton ID="imgbtnUpdate" CommandName="Update" runat="server" ImageUrl="~/Images/update.jpg" ToolTip="Update" Height="20px" Width="20px" />
<asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg" ToolTip="Cancel" Height="20px" Width="20px" />

</EditItemTemplate>
<ItemTemplate>
<asp:ImageButton ID="imgbtnEdit" CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg" ToolTip="Edit" Height="20px" Width="20px" />
<asp:ImageButton ID="imgbtnDelete" CommandName="Delete" Text="Edit" runat="server" ImageUrl="~/Images/delete.jpg" ToolTip="Delete" Height="20px" Width="20px" />
</ItemTemplate>
<FooterTemplate>
<asp:ImageButton ID="imgbtnAdd" runat="server" ImageUrl="~/Images/AddNewitem.jpg" CommandName="AddNew" Width="30px" Height="30px" ToolTip="Add new User" ValidationGroup="validaiton" />

</FooterTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="Date">
<EditItemTemplate>
<asp:Label ID="lbleditdate" runat="server" Text='<%#Eval("sdate") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lbldate" runat="server" Text='<%#Eval("sdate") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtdate" runat="server"  />
<br />
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" ControlToValidate="txtdate" runat="server"
ErrorMessage="Enter valid date" ForeColor="Red"
ValidationExpression="^(0[1-9]|[1-9]|[12][0-9]|3[01])-([Jj][Aa][Nn]|[Ff][Ee][Bb]|[Mm][Aa][Rr]|[Aa][Pp][Rr]|[Mm][Aa][Yy]|[Jj][Uu][Nn]|[Jj][Uu][Lj]|[Aa][Uu][Gg]|[Ss][Ee][Pp]|[Oo][Cc][Tt]|[Nn][Oo][Vv]|[Dd][Ee][Cc])-(19|20)\d\d$">
</asp:RegularExpressionValidator>
<asp:CalendarExtender ID="CalendarExtender1" TargetControlID="txtdate" Format="dd-MMM-yyyy" runat="server">
</asp:CalendarExtender>
<asp:RequiredFieldValidator ID="rfvdate" runat="server" ControlToValidate="txtdate" Text="*" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
 <asp:TemplateField HeaderText="StaffID">
 <EditItemTemplate>
 <asp:Label ID="lbleditstaffid" Visible="true" runat="server" Text='<%#Eval("rid") %>'/>
 <asp:DropDownList ID="ddleditstaffid" runat="server" Visible="false" >
 <asp:ListItem Value="0" Text="Select"></asp:ListItem>
</asp:DropDownList>
 </EditItemTemplate>
 <ItemTemplate>
 <asp:Label ID="lblstaffid" runat="server" Text='<%#Eval("rid") %>'/>
 </ItemTemplate>
 <FooterTemplate>
 <asp:DropDownList ID="ddlstaffid" AutoPostBack="true" OnSelectedIndexChanged="ddlstaffid_SelectedIndexChanged" runat="server" >
 <asp:ListItem Value="0" Text="Select"></asp:ListItem>
</asp:DropDownList>
  <asp:RequiredFieldValidator ID="rfvstaffid" runat="server" ControlToValidate="ddlstaffid" InitialValue="0" Text="*" ValidationGroup="validaiton"/>
 </FooterTemplate>
 </asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<EditItemTemplate>
<asp:Label ID="lbleditname" runat="server" Text='<%#Eval("name") %>'/>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="lblname" runat="server" Text='<%#Eval("name") %>'/>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtname" runat="server"/>
<asp:RequiredFieldValidator ID="rfvusername" runat="server" ControlToValidate="txtname" Text="*" ValidationGroup="validaiton"/>
</FooterTemplate>
</asp:TemplateField>
 <asp:TemplateField HeaderText="TimeIn">
 <EditItemTemplate>
 <asp:TextBox ID="txtcheckin" runat="server" Text='<%#Eval("checkin") %>'/>
 </EditItemTemplate>
 <ItemTemplate>
 <asp:Label ID="lblcheckin" runat="server" Text='<%#Eval("checkin") %>'/>
 </ItemTemplate>
 <FooterTemplate>
 <asp:TextBox ID="txtcheckin" runat="server"/>
 <asp:RequiredFieldValidator ID="rfvcheckin" runat="server" ControlToValidate="txtcheckin" Text="*" ValidationGroup="validaiton"/>
 </FooterTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="TimeOut">
 <EditItemTemplate>
 <asp:TextBox ID="txtcheckout" runat="server" Text='<%#Eval("checkout") %>'/>
 </EditItemTemplate>
 <ItemTemplate>
 <asp:Label ID="lblcheckout" runat="server" Text='<%#Eval("checkout") %>'/>
 </ItemTemplate>
 <FooterTemplate>
 <asp:TextBox ID="txtcheckout" runat="server"/>
  <asp:RequiredFieldValidator ID="rfvcheckout" runat="server" ControlToValidate="txtcheckout" Text="*" ValidationGroup="validaiton"/>
 </FooterTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="CheckIn">
 <EditItemTemplate>
 <asp:DropDownList ID="ddleditintime" runat="server" SelectedValue='<%# Eval("intime") %>'>
 <asp:ListItem Value="0" Text="Select"></asp:ListItem>
<asp:ListItem Value="AM" Text="AM"></asp:ListItem>
<asp:ListItem Value="PM" Text="PM"></asp:ListItem>
</asp:DropDownList>
 </EditItemTemplate>
 <ItemTemplate>
 <asp:Label ID="lblintime" runat="server" Text='<%#Eval("intime") %>'/>
 </ItemTemplate>
 <FooterTemplate>
 <asp:DropDownList ID="ddlintime" runat="server" >
 <asp:ListItem Value="0" Text="Select"></asp:ListItem>
<asp:ListItem Value="AM" Text="AM"></asp:ListItem>
<asp:ListItem Value="PM" Text="PM"></asp:ListItem>
</asp:DropDownList>
  <asp:RequiredFieldValidator ID="rfvintime" runat="server" ControlToValidate="ddlintime" InitialValue="0" Text="*" ValidationGroup="validaiton"/>
 </FooterTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="CheckOut">
 <EditItemTemplate>
 <asp:DropDownList ID="ddleditouttime" runat="server" SelectedValue='<%# Eval("outtime") %>'>
 <asp:ListItem Value="0" Text="Select"></asp:ListItem>
<asp:ListItem Value="AM" Text="AM"></asp:ListItem>
<asp:ListItem Value="PM" Text="PM"></asp:ListItem>
</asp:DropDownList>
 </EditItemTemplate>
 <ItemTemplate>
 <asp:Label ID="lblouttime" runat="server" Text='<%#Eval("outtime") %>'/>
 </ItemTemplate>
 <FooterTemplate>
 <asp:DropDownList ID="ddlouttime" runat="server" >
 <asp:ListItem Value="0" Text="Select"></asp:ListItem>
<asp:ListItem Value="AM" Text="AM"></asp:ListItem>
<asp:ListItem Value="PM" Text="PM"></asp:ListItem>
</asp:DropDownList>
  <asp:RequiredFieldValidator ID="rfvouttime" runat="server" ControlToValidate="ddlouttime" InitialValue="0" Text="*" ValidationGroup="validaiton"/>
 </FooterTemplate>
 </asp:TemplateField>
 </Columns>
</asp:GridView>

    </div>
<div>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</asp:Content>

StaffAttendance.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
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;

public partial class SuccessPlus_Admin_Default2 : System.Web.UI.Page
{
    DataLayer dl = new DataLayer();
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Request.QueryString.Count != 0)
            {
                hdndranch.Value = Request.QueryString["branch"].ToString();
                hdnmanager.Value = Request.QueryString["manager"].ToString();
            }
            BindEmployeeDetails();
        }
    }
    protected void BindEmployeeDetails()
    {
     
        SqlCommand cmd = new SqlCommand("select r.id as rid,r.name,s.id as attid,s.attan,s.sdate,s.checkin,s.checkout,s.intime,s.outtime from rip_staff r inner join staff_attendance s on r.id=s.sid");
        SqlDataAdapter da = new SqlDataAdapter(cmd);    
        DataTable dt = new DataTable();
        dt = dl.GetData(cmd);    
     
        if (dt.Rows.Count > 0)
        {
            gvDetails.DataSource = dt;
            gvDetails.DataBind();
        }
        else
        {
         
            dt.Rows.Add(dt.NewRow());
            gvDetails.DataSource = dt;
            gvDetails.DataBind();
            int columncount = gvDetails.Rows[0].Cells.Count;
            gvDetails.Rows[0].Cells.Clear();
            gvDetails.Rows[0].Cells.Add(new TableCell());
            gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
            gvDetails.Rows[0].Cells[0].Text = "No Records Found";
        }

    }

    protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvDetails.EditIndex = e.NewEditIndex;

        BindEmployeeDetails();
    }

    protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
        string attid = gvDetails.DataKeys[e.RowIndex].Values["attid"].ToString();
        Label lbleditname = (Label)gvDetails.Rows[e.RowIndex].FindControl("lbleditname");
        TextBox txtcheckin = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtcheckin");
        TextBox txtcheckout = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtcheckout");
        DropDownList ddleditintime = (DropDownList)gvDetails.Rows[e.RowIndex].FindControl("ddleditintime");
        DropDownList ddleditouttime = (DropDownList)gvDetails.Rows[e.RowIndex].FindControl("ddleditouttime");
     
        SqlCommand cmd = new SqlCommand("update staff_attendance set checkin='" + txtcheckin.Text + "',checkout='" + txtcheckout.Text + "',intime='" + ddleditintime.SelectedValue + "',outtime='" + ddleditouttime.SelectedValue + "' where id=" + attid);
        dl.Execute(cmd);

        lblresult.ForeColor = Color.Green;
        lblresult.Text = lbleditname.Text + " Details Updated successfully";
        gvDetails.EditIndex = -1;
        BindEmployeeDetails();
    }

    protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvDetails.EditIndex = -1;
        BindEmployeeDetails();
    }

    protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["attid"].ToString());
        string attid = gvDetails.DataKeys[e.RowIndex].Values["attid"].ToString();
        Label lblname = (Label)gvDetails.Rows[e.RowIndex].FindControl("lblname");
     
        SqlCommand cmd = new SqlCommand("delete from staff_attendance where id=" + attid);
        int result = dl.Execute(cmd);
   
        if (result == 1)
        {
            BindEmployeeDetails();
            lblresult.ForeColor = Color.Red;
            lblresult.Text = lblname.Text + " details deleted successfully";
        }
   
    }

    protected void gvDetails_RowDataBound(object sender, GridViewRowEventArgs e)
    {


    }

    protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {
            string attan = "";

            TextBox txtname = (TextBox)gvDetails.FooterRow.FindControl("txtname");
            TextBox txtcheckin = (TextBox)gvDetails.FooterRow.FindControl("txtcheckin");
            TextBox txtcheckout = (TextBox)gvDetails.FooterRow.FindControl("txtcheckout");
            DropDownList ddlintime = (DropDownList)gvDetails.FooterRow.FindControl("ddlintime");
            DropDownList ddlouttime = (DropDownList)gvDetails.FooterRow.FindControl("ddlouttime");
            DropDownList ddlstaffid = (DropDownList)gvDetails.FooterRow.FindControl("ddlstaffid");
            TextBox txtdate = (TextBox)gvDetails.FooterRow.FindControl("txtdate");

            if (txtcheckin.Text != "" && txtcheckout.Text != "")
            {
                attan = "1";
            }
            else if (txtcheckin.Text != "" || txtcheckout.Text != "")
            {
                attan = "0.5";
            }
            else if (txtcheckin.Text == "" && txtcheckout.Text == "")
            {
                attan = "0";
            }
         
            SqlCommand cmd =
                new SqlCommand(
                    "insert into staff_attendance (sid,attan,submitDate,checkin,checkout,intime,outtime,sdate) values " +
                    " ('" + ddlstaffid.SelectedValue + "','" + attan + "', " +
                    " '" + DateTime.Now.ToString("dd-MMM-yyyy") + "','" + txtcheckin.Text + "', " +
                    " '" + txtcheckout.Text + "','" + ddlintime.SelectedValue + "','" + ddlouttime.SelectedValue + "', " +
                    " '" + txtdate.Text + "')");
            int result = dl.Execute(cmd);
         
            if (result == 1)
            {
                BindEmployeeDetails();
                lblresult.ForeColor = Color.Green;
                lblresult.Text = txtname.Text + " Details inserted successfully";
            }
            else
            {
                lblresult.ForeColor = Color.Red;
                lblresult.Text = txtname.Text + " Details not inserted";
            }


        }


    }

    protected void gvDetails_DataBinding(object sender, EventArgs e)
    {

    }
    protected void gvDetails_DataBound(object sender, EventArgs e)
    {

    }

    protected void gvDetails_RowDataBound1(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //getting username from particular row
            string username = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "attid"));
            //identifying the control in gridview
            ImageButton lnkbtnresult = (ImageButton)e.Row.FindControl("imgbtnDelete");
            //raising javascript confirmationbox whenver user clicks on link button
            if (lnkbtnresult != null)
            {
                lnkbtnresult.Attributes.Add("onclick", "javascript:return ConfirmationBox('" + username + "')");
            }




        }
        else if (e.Row.RowType == DataControlRowType.Footer)
        {
            DropDownList ddlstaffid = (DropDownList)e.Row.FindControl("ddlstaffid");

            string query = "select id,name from rip_staff order by id asc";
            SqlCommand cmd = new SqlCommand(query);
            //DataTable dt;
            //dt = GetData(cmd);
            //dt.TableName = "dt";
            //DataRow dr;
            //dr = dt.NewRow();
            //dr["id"]= "0";
            //dr["name"] = "Select";
            //dt.Rows.Add(dr);
            //dt.AcceptChanges();
            //DataView dw = new DataView();
            //dw = new DataView();
            //dw.Table = dt;
            //dw.Sort = "id asc";
            //ddlstaffid.DataSource = dw;
            //ddlstaffid.DataTextField = "id";
            //ddlstaffid.DataValueField = "id";
            //ddlstaffid.DataBind();
            //ddlstaffid.DataSource = dw;
            //ddlstaffid.DataTextField = "id";
            //ddlstaffid.DataValueField = "id";
            //ddlstaffid.DataBind();
            //ddlstaffid.SelectedValue = "0";
         
            ddlstaffid.DataSource = dl.GetData(cmd);
            ddlstaffid.DataTextField = "id";
            ddlstaffid.DataValueField = "id";
            ddlstaffid.DataBind();
            ddlstaffid.Items.Add("Select");
            ddlstaffid.SelectedValue = "Select";
        }

        if (e.Row.RowType == DataControlRowType.DataRow && gvDetails.EditIndex == e.Row.RowIndex)
        {

            DropDownList ddleditstaffid = (DropDownList)e.Row.FindControl("ddleditstaffid");

            string query = "select id from rip_staff order by id asc";
            SqlCommand cmd = new SqlCommand(query);
            //DataTable dt;
            //dt = GetData(cmd);
            //dt.TableName = "dt";
            //DataRow dr;
            //dr = dt.NewRow();
            //dr["id"] = 0;
            //dr["id"] = "Select";
            //dt.Rows.Add(dr);
            //dt.AcceptChanges();
            //DataView dw = new DataView();
            //dw = new DataView();
            //dw.Table = dt;
            //dw.Sort = "id asc";
            //ddleditstaffid.DataSource = dw;
            //ddleditstaffid.DataTextField = "id";
            //ddleditstaffid.DataValueField = "id";
            //ddleditstaffid.DataBind();
            //ddleditstaffid.DataSource = dw;
            //ddleditstaffid.DataTextField = "id";
            //ddleditstaffid.DataValueField = "id";
            //ddleditstaffid.DataBind();
            //ddleditstaffid.SelectedValue = "0";
            ddleditstaffid.DataSource =dl.GetData(cmd);
            ddleditstaffid.DataTextField = "id";
            ddleditstaffid.DataValueField = "id";
            ddleditstaffid.DataBind();
            ddleditstaffid.Items.Add("Select");
            ddleditstaffid.Items.FindByValue((e.Row.FindControl("lbleditstaffid") as Label).Text).Selected = true;

            //ListItem lst = new ListItem();
            //lst.Text = "AM";
            //lst.Value = "AM";
            //ListItem lst1 = new ListItem();
            //lst1.Text = "PM";
            //lst1.Value = "PM";
            //ListItem lst2 = new ListItem();
            //lst2.Text = "Select";
            //lst2.Value = "0";
            //DropDownList ddlintime = (DropDownList)e.Row.FindControl("ddlintime");
            //ddlintime.Items.Add(lst2);
            //ddlintime.Items.Add(lst);
            //ddlintime.Items.Add(lst1);

        }


    }

    protected void ddlstaffid_SelectedIndexChanged(object sender, EventArgs e)
    {
        DropDownList ddlstaffid = (DropDownList)gvDetails.FooterRow.FindControl("ddlstaffid");
        TextBox txtname = (TextBox)gvDetails.FooterRow.FindControl("txtname");
        txtname.Text = "";
        if (ddlstaffid.SelectedValue != "Select")
        {
            string query = "select id,name from rip_staff where id='" + ddlstaffid.SelectedValue + "'";
            SqlCommand cmd = new SqlCommand(query);
            txtname.Text = dl.GetData(cmd).Rows[0]["name"].ToString();
        }
    }
 
}

Output