You must Sign In to post a response.
  • Category: ASP.NET

    How to find senior employee having 5 years experience from emp_table ?

    I have a table in database with employee records like below.

    id | name | salary | doj | Bonus
    --------------------------------------
    101 sr 2000 10/01/1996
    102 si 4000 12/12/2010
    103 sd 5000 11/06/1994
    104 sh 6000 09/02/1991
    105 sa 2000 01/04/2015

    I want to show all these in a gridview.
    How to highlight gridview row based on the condition below.
    If an employee having more than 5 years experience then
    that particular row should be highlighted and in bonus column
    it should display "Yes".

    Regards

    Sridhar Thota.
  • #761849

    You can try the following query

    select id, name ,salary , doj ,
    case when (DATEDIFF(yy,TestDate, getdate()) > =5) then 'yes' end '' as bonus
    from Emp


    By Nathan
    Direction is important than speed

  • #761851
    Hi Nathan.

    I want it to be implemented in asp.net using c# in gridview and also the highlighting particular row based on the condition.

    Regards

    Sridhar Thota.

    Sridhar Thota.
    Editor: DNS Forum.

  • #761856
    Hi sridar,

    After implement the bonus column by using "nathan" response on code behind onRowDataBound event of gridview you need to check one condition like below.


    protected void gv_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    string bonus=(e.Row.FindControl("lblBonus") as Label).Text;
    if(bonus.ToLower()=="yes")
    {
    e.Row.BackColor=System.Drawing.Color.Yellow;
    }
    }
    }


    you can try like above..

    Hope this will helpful to you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #761858
    Hi Naveen.

    I got how to set the bonus column value as yes or no.
    DateTime dt= Convert.ToDate(e.rows.cells[4]);
    DateTime yr= 01/01/2010;
    if(dt< yr)
    {
    e.rows.cells[5].Text="Yes"
    }
    else

    e.rows.cells[5].Text="No"
    But how to check the condition in rowbound event using c# with date of join(doj), that he has experience of 5 years from his joining.

    Regards

    Sridhar Thota.

    Sridhar Thota.
    Editor: DNS Forum.

  • #761859

    Hi sridhar,

    You can do it in below way.

    protected void gv_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    string doj=(e.Row.FindControl("lbldoj") as Label).Text;
    DateTime dtdoj=Convert.ToDateTime(doj);
    DateTime dtCurr=DateTime.Now;
    TimeSpan diff=(dtCurr-dtdoj).TotalDays;
    int yrs=(Int32)Math.Round(convert.ToInt32(diff)/365);
    if(yrs >= 5)
    {
    e.Row.BackColor=System.Drawing.Color.Yellow;
    }
    }
    }


    I didn't text it, I wrote directly. I request you to please test it from your side and if any correction please take care.


    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #761861

    You could also try the below code:
    string doj = (e.Row.FindControl("lbldoj") as Label).Text;
    DateTime dt = Convert.ToDateTime(doj);
    //DateTime dt = new DateTime(2014, 1, 1);
    TimeSpan ts = DateTime.Now-dt;
    int daysInyears = 365*5;
    if(ts.Days > daysInyears)
    { e.Row.BackColor=System.Drawing.Color.Yellow;
    }


    Miss. Jain
    Microsoft Certified Technology Specialist in .Net

  • #761863
    Hi Naveen and Miss Jain.

    Naveen there is conversion error in your code.
    why this line " string doj=(e.Row.FindControl("lbldoj") as Label).Text; " not working for me?

    The following code worked for me, but the doj 12/12/2009 is not coming under 5 years experience.

    I have attached screen shot below.


    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    string doj = e.Row.Cells[5].Text;
    DateTime dt = Convert.ToDateTime(doj);
    TimeSpan ts = DateTime.Now - dt;
    int daysInyears = 365 * 5;
    if (ts.Days > daysInyears)
    {
    e.Row.BackColor = System.Drawing.Color.Red;
    e.Row.Cells[6].Text = "Yes";
    }
    else
    {
    e.Row.Cells[6].Text="No";
    }
    }
    }

    Sridhar Thota.
    Editor: DNS Forum.

    Delete Attachment

  • #761866
    Hi

    Thanks one and all for responding.

    Its working fine now as expected.
    How to write a stored procedure for the same requirement.

    Regards

    Sridhar Thota.

    Sridhar Thota.
    Editor: DNS Forum.

  • #761868
    Hi
    Sridhar

    This line working

    string doj=(e.Row.FindControl("lbldoj") as Label).Text;


    You can copy and paste your web application in my code which is i given below . This is working for me.


    Client Side Code


    <asp:GridView runat="server" ID="grd1" AutoGenerateColumns="false" OnRowDataBound="grd1_RowDataBound">
    <Columns>
    <asp:BoundField HeaderText="ID" DataField="ID" />
    <asp:BoundField HeaderText="Name" DataField="name" />
    <asp:BoundField HeaderText="Salary" DataField="salary" />
    <asp:BoundField HeaderText="Doj" DataField="doj" />
    <asp:BoundField HeaderText="Bonus" DataField="Bonus" />

    <asp:TemplateField>
    <ItemTemplate>
    <asp:Label ID="lbldoj" runat="server" Text='<%# Eval("doj") %>'></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>

    </Columns>
    </asp:GridView>



    Server Side
    If you need from sql server means use this code



    sqladp = new SqlDataAdapter("Select Id,name,salary,doj,CASE WHEN (DATEDIFF(yy,doj,getdate())>5) THEN 'YES' ELSE 'NO' END as bonus from tblpays", sqlcon);
    sqladp.Fill(dt);
    grd1.DataSource = dt;
    grd1.DataBind();

    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    if (e.Row.Cells[4].Text == "YES")
    {
    //Particular Row coloring
    e.Row.BackColor = System.Drawing.Color.Yellow;
    //Particular Cell Coloring
    //e.Row.Cells[4].BackColor = System.Drawing.Color.Yellow;
    }
    }



    This is working for label doj control check this


    if (e.Row.RowType == DataControlRowType.DataRow)
    {

    string doj = (e.Row.FindControl("lbldoj") as Label).Text;

    //string doj = e.Row.Cells[3].Text;
    DateTime dt = Convert.ToDateTime(doj);
    TimeSpan ts = DateTime.Now - dt;
    int daysInyears = 365 * 5;
    if (ts.Days > daysInyears)
    {
    e.Row.BackColor = System.Drawing.Color.Red;
    e.Row.Cells[4].Text = "Yes";
    }
    else
    {
    e.Row.Cells[4].Text = "No";
    }
    }


    I have attached Images given below check and let me know.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    Delete Attachment

  • #761869
    Hi
    Sridhar

    If you need stored Procedure means follow this code

    stored procedure use this Query


    Create PRoc sp_ProcDays
    as
    Select Id,name,salary,doj,CASE WHEN (DATEDIFF(yy,doj,getdate())>5) THEN 'YES' ELSE 'NO' END as bonus from tblpays



    In the server side code



    public void FillGrid()
    {
    sqladp = new SqlDataAdapter("sp_ProcDays", sqlcon);
    sqladp.Fill(dt);
    grd1.DataSource = dt;
    grd1.DataBind();
    }

    protected void grd1_RowDataBound(object sender, GridViewRowEventArgs e)
    {


    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    if (e.Row.Cells[4].Text == "YES")
    {
    //Particular Row coloring
    e.Row.BackColor = System.Drawing.Color.Yellow;
    //Particular Cell Coloring
    //e.Row.Cells[4].BackColor = System.Drawing.Color.Yellow;
    }
    }



    if (e.Row.RowType == DataControlRowType.DataRow)
    {

    string doj = (e.Row.FindControl("lbldoj") as Label).Text;

    //string doj = e.Row.Cells[3].Text;
    DateTime dt = Convert.ToDateTime(doj);
    TimeSpan ts = DateTime.Now - dt;
    int daysInyears = 365 * 5;
    if (ts.Days > daysInyears)
    {
    e.Row.BackColor = System.Drawing.Color.Red;
    e.Row.Cells[4].Text = "Yes";
    }
    else
    {
    e.Row.Cells[4].Text = "No";
    }
    }
    }

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #761874
    Hi sridhar,

    If you want to prepare stored procedure for the above task you just use "Nathan" Post for reference, and include columns whatever you required.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments