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();
}
}
}
<%@ 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();
}
}
}
Category:
