sum hours in grid vew

Reply

Member

13 Points

sum hours in grid vew

Jan 22, 2021 04:19 PM|sultaan12|LINK

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

Reply

SurferOnWww

Member

330 Points

235 Posts

Re: sum hours in grid vew

Jan 22, 2021 11:31 PM|SurferOnWww|LINK

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?

Reply

sultaan12

Member

13 Points

8 Posts

Re: sum hours in grid vew

Jan 23, 2021 12:53 PM|sultaan12|LINK

 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

Reply

mgebhard

All-Star

53041 Points

23612 Posts

Re: sum hours in grid vew

Jan 23, 2021 03:59 PM|mgebhard|LINK

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

Reply

sultaan12

Member

13 Points

8 Posts

Re: sum hours in grid vew

Jan 23, 2021 04:05 PM|sultaan12|LINK

I want an example

Reply

mgebhard

All-Star

53041 Points

23612 Posts

Re: sum hours in grid vew

Jan 23, 2021 04:53 PM|mgebhard|LINK

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

Reply

SurferOnWww

Member

330 Points

235 Posts

Re: sum hours in grid vew

Jan 24, 2021 01:56 AM|SurferOnWww|LINK

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".

Reply

oned_gk

All-Star

52673 Points

15720 Posts

Re: sum hours in grid vew

Jan 24, 2021 01:05 PM|oned_gk|LINK

        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

Programming to simplify, don't look for difficult way
Suwandi - Non Graduate Programmer

Reply

sultaan12

Member

13 Points

8 Posts

Re: sum hours in grid vew

Jan 25, 2021 07:03 PM|sultaan12|LINK

   <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

Reply

mgebhard

All-Star

53041 Points

23612 Posts

Re: sum hours in grid vew

Jan 25, 2021 07:39 PM|mgebhard|LINK

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

Reply

oned_gk

All-Star

52673 Points

15720 Posts

Re: sum hours in grid vew

Jan 25, 2021 09:06 PM|oned_gk|LINK

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

Programming to simplify, don't look for difficult way
Suwandi - Non Graduate Programmer

Reply

sultaan12

Member

13 Points

8 Posts

Re: sum hours in grid vew

Jan 26, 2021 06:46 PM|sultaan12|LINK

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.

Reply

mgebhard

All-Star

53041 Points

23612 Posts

Re: sum hours in grid vew

Jan 26, 2021 06:58 PM|mgebhard|LINK

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.

Reply

oned_gk

All-Star

52673 Points

15720 Posts

Re: sum hours in grid vew

Jan 26, 2021 09:25 PM|oned_gk|LINK

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

Programming to simplify, don't look for difficult way
Suwandi - Non Graduate Programmer

Reply

SurferOnWww

Member

330 Points

235 Posts

Re: sum hours in grid vew

Jan 27, 2021 02:56 AM|SurferOnWww|LINK

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?

Reply

SurferOnWww

Member

330 Points

235 Posts

Re: sum hours in grid vew

Jan 27, 2021 05:32 AM|SurferOnWww|LINK

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();
                    }

                }
            }
        }
    }
}

Reply

sultaan12

Member

13 Points

8 Posts

Re: sum hours in grid vew

Jan 31, 2021 05:25 PM|sultaan12|LINK

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();
                    }

                }
            }

Reply

SurferOnWww

Member

330 Points

235 Posts

Re: sum hours in grid vew

Jan 31, 2021 10:02 PM|SurferOnWww|LINK

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

Reply

sultaan12

Member

13 Points

8 Posts

Re: sum hours in grid vew

Feb 14, 2021 05:41 PM|sultaan12|LINK

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

Reply

SurferOnWww

Member

330 Points

235 Posts

Re: sum hours in grid vew

Feb 15, 2021 01:20 AM|SurferOnWww|LINK

sultaan12
How to close the topic

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

sum hours in grid vew[Answered] RSS

19 replies

Shortcuts

Related Links

sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

sultaan12

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

Re: sum hours in grid vew

sultaan12