display total hours in gridview footer C#

Question

4.33/5 (3 votes)

See more:

Hi there

I have this little problem as I am still VERY new to c#

I need to show the total hours worked in the footer of my GridView and all the fields are auto generated cell no [7].

CSS

INDX | CLIENTNAME | PROJECTNAME | NAME |  DATE     |   START  |  END     | HOURS_WORKED
  1  | ME         | ProjTest    |aj    |06/27/2012 | 14:10:33 | 12:07:33 |   21:57:00

C#

private void DoGridQuery() {
      string userid = GetUserID();
      string projectid = GetProjectID();
      string clientno = GetClientNO();


      string s = null;


      s = "SELECT A.INDX, B.CLIENTNAME, C.PROJECTNAME, D.NAME,CONVERT(VARCHAR(10), A.CDATE,101)[DATE],CONVERT(TIME(0), A.START_TIME,108)[START],CONVERT(TIME(0), A.END_TIME,108)[END],CONVERT(TIME(0),(A.END_TIME - A.START_TIME ),108)[HOURS_WORKED]";
      s += "FROM LOGSHEET A, CLIENTS B, PROJECTS C, DEVELOPERS D ";
      s += "WHERE D.USERID = @p2 ";
      if ((DropDownList1.SelectedValue != String.Empty) && (DropDownList1.SelectedValue != "...")) {
        s += "AND B.CLIENTNO = @p0 ";
      }
      if ((DropDownList2.SelectedValue != String.Empty) && (DropDownList2.SelectedValue != "...")) {

          s += "AND C.PROJECTID = @p1 ";
      }

      String[] pr = new String[3];
      pr[0] = clientno;
      pr[1] = projectid;
      pr[2] = userid;

      GridView1.DataSource = cQ.CreateDataSet(s, pr);

      GridView1.DataBind();


      GridView1.Visible = true;
    }

C#

and this is my GridView source

XML

<asp:GridView ID="GridView1" runat="server" onrowcommand="GridView1_RowCommand"
             ShowFooter="True" onrowdatabound="GridView1_RowDataBound">
            <Columns>
                <asp:ButtonField CommandName="Stop" Text="Stop" />
            </Columns>

            <HeaderStyle BackColor="#006600" ForeColor="White" />
        </asp:GridView>

ANY help would be appreciated
thanks in advance

Posted 28-Jun-12 0:35am

A.J Bosch

Add a Solution

3 solutions

Solution 1

Have a look at below links with similar discussions, hope it helps:
http://forums.asp.net/t/1119102.aspx[^]
http://www.asp.net/web-forms/tutorials/data-access/custom-formatting/displaying-summary-information-in-the-gridview-s-footer-cs[^]
http://csharpdotnetfreak.blogspot.in/2009/07/display-total-in-gridview-footer.html[^]

Posted 28-Jun-12 0:41am

Vani Kulkarni

Comments

A.J Bosch 28-Jun-12 7:13am

Hi when I got the reply I saw that I've been through 2 of the 3 links and they need bound fields as to mine that auto generates at the and of the day as far a I understand I need to go through each row Cell[7] get those amounts and put the sum in the footer Cell[7]

basically the closest i got was
http://www.codeproject.com/Articles/249429/Frequently-Asked-Questions-Series-1-The-ASP-Net-Gr#heading0001
Displaying a running total inside a GridView

but the
<footertemplate>
<asp:label ID="label" runat="server" />

part gives me
Type 'System.web.UI.WebControls.GridView' does not have a public property named 'FooterTemplate'.

The only site that I found that might help me is:
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.templatefield.footertemplate.aspx

but honestly don't know what to do there or how to ad the syntax to get to Type: System.Web.UI.ITemplate

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohamed Mitwalli · Accepted Answer · 2012-06-28T03:27:00

Hi ,
Check this

C#

DateTime dt = new DateTime();
   TimeSpan ts;
   protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
   {
       if (e.Row.RowType == DataControlRowType.DataRow)
       {
           //here make your calculation by using TimeSpan .
           //ts = dt - dt;
           //ts.TotalHours;
           dt = Convert.ToDateTime(e.Row.Cells[0].Text);
       }
       else if (e.Row.RowType == DataControlRowType.Footer)
       {
           //here you put your final result
           e.Row.Cells[0].Text = ts.TotalMinutes; ;
       }
   }

Best Regards
M.Mitwalli

A.J Bosch · Accepted Answer · 2012-07-02T23:40:00

honestly the best and easiest solution for me was to create a timediff field[TIME_SPENT] "datetime" and also a minutes field [MINUTES] as "int" in SQL then by running and update query

C#

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) {
if (e.CommandName == "Stop") {
        int row = int.Parse(e.CommandArgument.ToString());
        string indx = GridView1.Rows[row].Cells[1].Text;
        string s = null;
        s = "UPDATE LOGSHEET ";
        s += "SET TIME_SPENT = DATEADD(MINUTE, DATEDIFF(MINUTE,START_TIME, END_TIME), 0) ";
        s += "WHERE INDX = @p0 ";


        String[] pr = new String[1];
        pr[0] = indx;

        cQ.execSql(s, pr);
        DoGridQuery();
      }

if (e.CommandName == "Stop") {
        int row = int.Parse(e.CommandArgument.ToString());
        string indx = GridView1.Rows[row].Cells[1].Text;  
        string s = null;
        s = "UPDATE LOGSHEET ";
        s += "SET MINUTES = (DATEPART(HOUR,TIME_SPENT)*60)+(DATEPART(MINUTE,TIME_SPENT)) ";
        s += "WHERE INDX = @p0 ";


        String[] pr = new String[1];
        pr[0] = indx;

        cQ.execSql(s, pr);
        DoGridQuery();
      }
   }

So your table will look like

LOGSHEET
========

VB

INDX|CLIENTNAME|PROJECTNAME|NAME| DATE     |  START | END    |TIME_SPENT|MINUTES|
  1 |ME        |ProjTest   |aj  |06/27/2012|14:10:00|13:10:00| 01:00:00 |60     |

C#

protected string GetTotal() {
      string userid = Session["id"].ToString();
      string s = null;


      s = "SELECT SUM(MINUTES) ";
      s += "FROM LOGSHEET ";
      s += "WHERE USERID = @p0 ";

      String[] pr = new String[1];
      pr[0] = userid;

      return cQ.GenQuery(s, pr);
    }

AND THEN JUST

Label5.Text = GetTotal();

But then again that's just me?!? ;)

if anyone has other ways feel free to post them I'm keen on learning new ways

display total hours in gridview footer C#

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0