sum hours in grid vew

Question

User1005947 posted

How hours and minutes are added to each field automatically I have tried the following code and the experiment did not work

    For sum As Integer = 0 To GridView1.Rows.Count - 2
                        Dim N, H, M As String
                        N = DateDiff("n", GridView1.Rows(sum).Cells(5).Text, GridView1.Rows(sum).Cells(6).Text)
                        H = DateDiff("h", GridView1.Rows(sum).Cells(5).Text, GridView1.Rows(sum).Cells(6).Text)
                        M = N - (H * 60)
                    GridView1.Rows(sum).Cells(7).Text = (fixstring(H) + ":" + fixstring(M))
                Next

Answer 1

User-1545767719 posted

Can you show a minimum (must be minimum but complete, please) sample code so that I can copy & paste it in my Visual Studio and reproduce your issue?

Answer 2

User1005947 posted

 Protected Sub BtnSum_Click(sender As Object, e As EventArgs) Handles BtnSum.Click
        Try


            For sum As Integer = 0 To GridView1.Rows.Count - 2
                Dim N, H, M As String
                N = DateDiff("n", GridView1.Rows(sum).Cells(5).Text, GridView1.Rows(sum).Cells(6).Text)
                H = DateDiff("h", GridView1.Rows(sum).Cells(5).Text, GridView1.Rows(sum).Cells(6).Text)
                M = N - (H * 60)
                GridView1.Rows(sum).Cells(7).Text = Convert.ToDateTime(fixstring(H) + ":" + fixstring(M))
            Next


        Catch ex As Exception
            lbMsg.Text = ex.Message
        End Try
    End Sub

 Public Function fixstring(ByVal [date] As String) As String
        Dim dt As Integer = Convert.ToInt32([date])
        If dt < 10 Then
            [date] = "0" & [date]
        End If
        Return [date]
    End Function

Answer 3

User475983607 posted

It's much easier to do the math in SQL than in parsing a GridView.  It's one line of code in SQL.

Answer 4

User1005947 posted

I want an example

Answer 5

User475983607 posted

sultaan12

I want an example

You can learn TSQL by reading the reference docs.  The following TSQL create a column that has the hours, minutes, seconds, and milliseconds.

If(OBJECT_ID('tempdb..#DateDiffEx') IS NOT NULL)
	DROP TABLE #DateDiffEx

CREATE TABLE #DateDiffEx (
	StartDate	DATETIME,
	EndDate		DATETIME
)

INSERT INTO #DateDiffEx (StartDate, EndDate)
VALUES	('2021-01-22 08:55:05.025', '2021-01-22 10:15:11.000'),
		('2021-01-23 08:00:27.000', '2021-01-23 10:05:11.074')


SELECT	StartDate, 
		EndDate,
		CONVERT(varchar, DATEADD(ms, DATEDIFF(ms, StartDate, EndDate), 0), 114) [Hours]
FROM #DateDiffEx

StartDate               EndDate                 Hours
----------------------- ----------------------- ------------------------------
2021-01-22 08:55:05.027 2021-01-22 10:15:11.000 01:20:05:973
2021-01-23 08:00:27.000 2021-01-23 10:05:11.073 02:04:44:073

Answer 6

User-1545767719 posted

Thank you for posting the code. However I cannot help because the code is not enough to reproduce your issue "the experiment did not work".

Answer 7

User-1716253493 posted

        Dim tstotal As TimeSpan
        For sum As Integer = 0 To GridView1.Rows.Count - 2
            Dim ts As TimeSpan = Convert.ToDateTime(GridView1.Rows(sum).Cells(5).Text) - Convert.ToDateTime(GridView1.Rows(sum).Cells(6).Text)
            tstotal.Add(ts)
        Next
        Dim m As Integer = Convert.ToInt32(tstotal.TotalMinutes)
        'now you have total minutes then calculate here
        'you can also use totalhours etc

Answer 8

User1005947 posted

   <asp:DropDownList ID="DropDownList1" runat="server" CssClass="style2">
                            <asp:ListItem Value="False">--day--</asp:ListItem>
                        </asp:DropDownList>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
                            ControlToValidate="DropDownList1" ErrorMessage="*" ForeColor="Red" 
                            InitialValue="False"></asp:RequiredFieldValidator>
                        <asp:Label ID="Label2" runat="server" Text="/"></asp:Label>
                        <asp:DropDownList ID="DropDownList2" runat="server" CssClass="style2">
                            <asp:ListItem Value="False">--month--</asp:ListItem>
                        </asp:DropDownList>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" 
                            ControlToValidate="DropDownList2" ErrorMessage="*" ForeColor="Red" 
                            InitialValue="False"></asp:RequiredFieldValidator>
                        <asp:Label ID="Label3" runat="server" Text="/"></asp:Label>
                        <asp:DropDownList ID="DropDownList3" runat="server" CssClass="style2">
                            <asp:ListItem>--year--</asp:ListItem>
                        </asp:DropDownList>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" 
                            ControlToValidate="DropDownList3" ErrorMessage="*" ForeColor="Red" 
                            InitialValue="False"></asp:RequiredFieldValidator>
                </strong>
                <asp:Button ID="Btnsave" runat="server" Text="save" />
                <asp:Button ID="Button1" runat="server" Text="+" />
                <br />
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmpID" DataSourceID="SDS_Presence">
                    <Columns>
                        <asp:BoundField DataField="EmpID" HeaderText="EmpID" InsertVisible="False" ReadOnly="True" SortExpression="EmpID" />
                        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                        <asp:BoundField DataField="SocialID" HeaderText="SocialID" SortExpression="SocialID" />
                        <asp:BoundField DataField="FullName" HeaderText="FullName" ReadOnly="True" SortExpression="FullName" />
                        <asp:TemplateField HeaderText="Attendees">
                            <ItemTemplate>
                                <asp:DropDownList ID="DropDownList2" runat="server">
                                    <asp:ListItem Value="0">Present</asp:ListItem>
                                    <asp:ListItem Value="1">absent</asp:ListItem>
                                </asp:DropDownList>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="HOURS">
                            <ItemTemplate>
                                <asp:DropDownList ID="DropDownList3" runat="server">
                                    <asp:ListItem>07:30:00</asp:ListItem>
                                    <asp:ListItem>10:00:00</asp:ListItem>
                                </asp:DropDownList>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Presence">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox2" runat="server" Width="70px"></asp:TextBox>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Employee delay hours">
                            <ItemTemplate>
                                <asp:Label ID="lbTotelHours" runat="server" Text="Label"></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Sign out">
                            <ItemTemplate>
                                <asp:DropDownList ID="DropDownList4" runat="server">
                                    <asp:ListItem Value="0">نعم</asp:ListItem>
                                    <asp:ListItem Value="1">لا</asp:ListItem>
                                </asp:DropDownList>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Reason">
                            <ItemTemplate>
                                <asp:DropDownList ID="DropDownList5" runat="server">
                                    <asp:ListItem Value="0">vacation</asp:ListItem>
                                    <asp:ListItem Value="2">medical report</asp:ListItem>
                                    
                                </asp:DropDownList>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Notes">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <EmptyDataTemplate>
                        <asp:DropDownList ID="DropDownList1" runat="server">
                            <asp:ListItem Value="0">yes</asp:ListItem>
                            <asp:ListItem Value="1">no</asp:ListItem>
                        </asp:DropDownList>
                    </EmptyDataTemplate>
                </asp:GridView>

This is a file
Tested and didn't work

Answer 9

User475983607 posted

If you are looking for the aggregate SUM of a column then it is one line of code in TSQL.  The result is in a standard TimeSpan forms. 

If(OBJECT_ID('tempdb..#DateDiffEx') IS NOT NULL)
	DROP TABLE #DateDiffEx

CREATE TABLE #DateDiffEx (
	StartDate	DATETIME,
	EndDate		DATETIME
)

INSERT INTO #DateDiffEx (StartDate, EndDate)
VALUES	('2021-01-22 08:55:05.025', '2021-01-22 10:15:11.000'),
		('2021-01-23 08:00:27.000', '2021-01-23 10:05:11.074')


SELECT	StartDate, 
		EndDate,
		CONVERT(varchar, DATEADD(ms, DATEDIFF(ms, StartDate, EndDate), 0), 114) [Hours]
FROM #DateDiffEx

SELECT  CONVERT(varchar, DATEADD(ms, SUM(DATEDIFF(ms, StartDate, EndDate)), 0), 114) [Total]
FROM #DateDiffEx

StartDate               EndDate                 Hours
----------------------- ----------------------- ------------------------------
2021-01-22 08:55:05.027 2021-01-22 10:15:11.000 01:20:05:973
2021-01-23 08:00:27.000 2021-01-23 10:05:11.073 02:04:44:073


Total
------------------------------
03:24:50:047

Answer 10

User-1716253493 posted

use findcontrols to get the ddl and textbox

        For sum As Integer = 0 To GridView1.Rows.Count - 2
            Dim ddl3 As DropDownList = CType(GridView1.Rows(sum).FindControl("DropDownList3"), DropDownList)
            Dim tb2 As TextBox = CType(GridView1.Rows(sum).FindControl("Textbox2"), TextBox)
            Dim ts As TimeSpan = Convert.ToDateTime(ddl3.Text) - Convert.ToDateTime(tb2.Text)
            tstotal.Add(ts)
        Next
        Dim m As Integer = Convert.ToInt32(tstotal.TotalMinutes)
        'calculate here

Answer 11

User1005947 posted

Not all of my experiences worked for me in calculating late hours for all employees at once
Please help me write the code for calculating the delay hours for each employee, either as labeltotalhours

Appears

Argument 'Date1' cannot be converted to type 'Date'.


Conversion from string ":" to type 'Double' is not valid


String was not recognized as a valid DateTime.

Answer 12

User475983607 posted

The community has provided two working approaches for doing date calculations using standard types and formats.  It seems you have a new requirement to group by employee and sum.  Either approach shown will work but you have to add the grouping logic.  

IMHO, TSQL is easier the approach because TSQL is designed for set operation but you can do the same in C#.

Share your current design and include enough code so the community can find the bugs and help you fix the code.  The current error indicates you are trying to cast a string with a DateTime or TimeSpan format to a double.   IMHO, it is much easier to work with a TimeSpan which is why both approach above use a TimeSpan format.

Answer 13

User-1716253493 posted

You need to know the value (string) before convert it.

Ensure all values are correct value, if not all values typed correctly then skip calculation.

Calculate only correct date using isdate

IsDate

            Dim ddl3 As DropDownList = CType(GridView1.Rows(sum).FindControl("DropDownList3"), DropDownList)
            Dim tb2 As TextBox = CType(GridView1.Rows(sum).FindControl("Textbox2"), TextBox)
            If IsDate(ddl3.SelectedValue) And IsDate(tb2.Text) Then
                Dim ts As TimeSpan = Convert.ToDateTime(ddl3.SelectedValue) - Convert.ToDateTime(tb2.Text)
                tstotal.Add(ts)
            End If

Answer 14

User-1545767719 posted

Your code in the question of your first post includes:

N = DateDiff("n", GridView1.Rows(sum).Cells(5).Text, GridView1.Rows(sum).Cells(6).Text)
H = DateDiff("h", GridView1.Rows(sum).Cells(5).Text, GridView1.Rows(sum).Cells(6).Text)

I guess that the Cells(5) and Cells(6) are corresponding to the DropDownList3 and TextBox2 in the GridView respectively.

<asp:TemplateField HeaderText="HOURS">
    <ItemTemplate>
        <asp:DropDownList ID="DropDownList3" runat="server">
            <asp:ListItem>07:30:00</asp:ListItem>
            <asp:ListItem>10:00:00</asp:ListItem>
        </asp:DropDownList>
    </ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Presence">
    <ItemTemplate>
        <asp:TextBox ID="TextBox2" runat="server" Width="70px"></asp:TextBox>
    </ItemTemplate>
</asp:TemplateField>

And I also guess that after a user select the DropDownList3 and write a time in the TextBox2, you want calculate the time difference between the DropDownList3 and the TextBox2 and put the result in the Cell(7) (i.e., the lbTotelHours text box in the GridView) on PostBack at the server side.

<asp:TemplateField HeaderText="Employee delay hours">
    <ItemTemplate>
        <asp:Label ID="lbTotelHours" runat="server" Text="Label"></asp:Label>
    </ItemTemplate>
</asp:TemplateField>

Is the above understanding correct? If so, have you considered performing the above operation at the PreLender event?

Answer 15

User-1545767719 posted

Based on the understanding in my reply above...

Copy & paste the following codes and try it. I put button to initiate the calculation of time difference instead of using the PreRender event suggested in above reply. Please note that you will have to add the validation of the user inputs.

<%@ Page Language="C#" AutoEventWireup="true" 
    CodeBehind="WebForm15.aspx.cs" Inherits="WebApplication1.WebForm15" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:Button ID="Button1" runat="server" Text="Calculate Delay" OnClick="Button1_Click" />

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="EmpID" HeaderText="EmpID" 
                    InsertVisible="False" ReadOnly="True" SortExpression="EmpID" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:TemplateField HeaderText="HOURS">
                    <ItemTemplate>
                        <asp:DropDownList ID="DropDownList3" runat="server">
                            <asp:ListItem>07:30:00</asp:ListItem>
                            <asp:ListItem>10:00:00</asp:ListItem>
                        </asp:DropDownList>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Presence">
                    <ItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Width="70px"></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Employee delay hours">
                    <ItemTemplate>
                        <asp:Label ID="lbTotelHours" runat="server" Text="Label"></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace WebApplication1
{
    public partial class WebForm15 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                GridView1.DataSource = CreateDataSource();
                GridView1.DataBind();
            }
        }

        protected DataTable CreateDataSource()
        {
            DataTable table = new DataTable();
            table.Columns.Add("EmpID", typeof(int));
            table.Columns.Add("Name", typeof(string));

            for (int i = 0; i < 5; i++)
            {
                DataRow row = table.NewRow();
                row["EmpID"] = i;
                row["Name"] = "Name-" + i;
                table.Rows.Add(row);
            }

            return table;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            foreach (GridViewRow row in GridView1.Rows)
            {
                DropDownList dropDownList3 = row.FindControl("DropDownList3") as DropDownList;
                TextBox textBox2 = row.FindControl("TextBox2") as TextBox;
                Label lbTotelHours = row.FindControl("lbTotelHours") as Label;
                if (dropDownList3 != null && textBox2 != null && lbTotelHours != null)
                {
                    string timeSetString = dropDownList3.SelectedValue;
                    string timeInString = textBox2.Text;
                    TimeSpan timeSet;
                    TimeSpan timeIn;

                    if (TimeSpan.TryParse(timeSetString, out timeSet) &&
                        TimeSpan.TryParse(timeInString, out timeIn))
                    {
                        TimeSpan diff = timeIn - timeSet;
                        lbTotelHours.Text = diff.ToString();
                    }

                }
            }
        }
    }
}

Answer 16

User1005947 posted

Thank you very much oned_gk, SurferOnWww


Actually the solution was done with this code

Try
            For sum As Integer = 0 To GridView1.Rows.Count - 1
                Dim ddl3 As DropDownList = CType(GridView1.Rows(sum).FindControl("DropDownList3"), DropDownList)
                Dim tb2 As TextBox = CType(GridView1.Rows(sum).FindControl("Textbox2"), TextBox)
                If IsDate(ddl3.SelectedValue) And IsDate(tb2.Text) Then
                    Dim N, H, M As String
                    N = DateDiff("n", ddl3.SelectedItem.Text, tb2.Text)
                    H = DateDiff("h", ddl3.SelectedItem.Text, tb2.Text)
                    M = N - (H * 60)
                    GridView1.Rows(sum).Cells(7).Text = (fixstring(H) + ":" + fixstring(M))
                End If
            Next
        Catch ex As Exception
            lbMsg.Text = ex.Message
        End Try

And code

 foreach (GridViewRow row in GridView1.Rows)
            {
                DropDownList dropDownList3 = row.FindControl("DropDownList3") as DropDownList;
                TextBox textBox2 = row.FindControl("TextBox2") as TextBox;
                Label lbTotelHours = row.FindControl("lbTotelHours") as Label;
                if (dropDownList3 != null && textBox2 != null && lbTotelHours != null)
                {
                    string timeSetString = dropDownList3.SelectedValue;
                    string timeInString = textBox2.Text;
                    TimeSpan timeSet;
                    TimeSpan timeIn;

                    if (TimeSpan.TryParse(timeSetString, out timeSet) &&
                        TimeSpan.TryParse(timeInString, out timeIn))
                    {
                        TimeSpan diff = timeIn - timeSet;
                        lbTotelHours.Text = diff.ToString();
                    }

                }
            }

Answer 17

User-1545767719 posted

Please close this thread as it seems that your problem has been solved. 

Answer 18

User1005947 posted

It is true already the problem was solved successfully all thanks to you for that
How to close the topic

Answer 19

User-1545767719 posted

How to close the topic

Give "Marked as answer" to the replies which are helpful to find the solution of your problem,