how to SUM the timespan values from a datagridview

Question

I want to sum the values that are in gridview, but it is a timespan value.

I want to calculate the total duration of two values from a table, that is start_time and end_time are the column in both table and grid view.
The duration value must be added to next column in gridview. It works fine by my code below.
Then the all the total_hours column on gridview must be summed and showed on a textbox

For example, the following is my grid view looks:

Days    Start_Time  End_Time    Total_Hours  
--------------------------------------------
day1    10:00AM     07:00PM     09:00:00
day2    10:00AM     08:00PM     10:00:00

The above tablel is a Gridview. I want to sum the total hours and display on a textbox

I tried the following code:

If dr.HasRows Then
    While dr.Read
        Dim s_hour = dr("start_time")
        Dim e_hour = dr("end_time")
        Dim duration As TimeSpan = e_hour - s_hour
        DataGridView1.Rows.Add()
        DataGridView1.Item(dgv_sno.Name, DataGridView1.Rows.Count - 1).Value = DataGridView1.Rows.Count
        DataGridView1.Item(dgv_personid.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("PersonID")), 0, dr("PersonID"))
        DataGridView1.Item(dgv_person.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("name")), 0, dr("name"))
        DataGridView1.Item(dgv_company.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("CompanyName")), 0, dr("CompanyName"))
        DataGridView1.Item(dgv_project.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("proj_Name")), 0, dr("proj_Name"))
        DataGridView1.Item(dgv_tasks.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("task")), 0, dr("task"))
        DataGridView1.Item(dgv_status.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("status")), 0, dr("status"))
        DataGridView1.Item(dgv_date.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("Record_Date")), 0, dr("Record_Date"))
        DataGridView1.Item(dgv_starttime.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("start_time")), 0, dr("start_time"))
        DataGridView1.Item(dgv_endtime.Name, DataGridView1.Rows.Count - 1).Value = IIf(IsDBNull(dr("end_time")), 0, dr("end_time"))
        DataGridView1.Item(dgv_hrs.Name, DataGridView1.Rows.Count - 1).Value = duration 

        If Val(DataGridView1.Item(dgv_hrs.Name, DataGridView1.Rows.Count - 1).Value) >= 1 Then
            span1 = dr("start_time")
            span2 = dr("end_time")
            Dim span3 As TimeSpan = span1.Add(span2)
            txttotalwork.Text = span3.ToString
        Else
            ?
        End If

    End While
End If

If you already have the duration of each row in the datareader, why not simply use the while loop you have to sum them up? — Zohar Peled, Jul 23 '15 at 05:29

ehh · Answer 1 · 2015-07-23T05:48:07.630

0

No need for this block:

If Val(DataGridView1.Item(dgv_hrs.Name, DataGridView1.Rows.Count - 1).Value) >= 1 Then
                span1 = dr("start_time")
                span2 = dr("end_time")
                Dim span3 As TimeSpan = span1.Add(span2)
                txttotalwork.Text = span3.ToString
            Else
               ?
            End If

Instead Add to span3 the duration:

span3.Add (duration)

And then Outside of the end of the loop:

txttotalwork.Text = span3.ToString

edited Jul 23 '15 at 05:48

answered Jul 23 '15 at 05:41

ehh

3,412
7
43
91

Thanks for your instructions, so kind of you. @ehh – Praveen Ezlilan Jul 23 '15 at 06:39

Zohar Peled · Accepted Answer · 2015-07-23T06:07:18.390

0

You already have the duration variable in your while loop, which I assume is of type TimeSpan and is populated correctly.

So all you have to do is add the code to your loop to sum it:

'before the loop:
Dim TotalDuration = new Timespan(0)

'inside the loop:
TotalDuration = TotalDuration.Add(duration)

'after the loop:
txttotalwork.Text = TotalDuration.ToString()

edited Jul 23 '15 at 06:07

answered Jul 23 '15 at 05:42

Zohar Peled

79,642
10
69
121

It returns 00:00:00. when the loop execute the TotalDuration does not get the values of duration. @Zohar Peled – Praveen Ezlilan Jul 23 '15 at 06:01
Works Fine.. , but how to convert it to hour format. like. 35 hours,19 hours. – Praveen Ezlilan Jul 23 '15 at 06:12
It will help you to format your time: http://stackoverflow.com/questions/463642/what-is-the-best-way-to-convert-seconds-into-hourminutessecondsmilliseconds – ehh Jul 23 '15 at 06:30
Thanks for your instructions, so kind of you. @ZoharPeled – Praveen Ezlilan Jul 23 '15 at 06:39

how to SUM the timespan values from a datagridview

2 Answers2