Total time problem

D

dakota97

Well-known member
Joined
Nov 14, 2003
Messages
113
Location
Pennsylvania
Hi all,

Im creating a timeclock program, and having a problem calculating the total time that an employee works. I have a form that retrieves the employees information from the database and fills a dataset. The dataset is then displayed in a datagrid. The fields that are displayed are the Date, Start Time, End Time, and Hours worked. What I want to do is calculate the Total Hours worked and display it in a textbox. How can I do that? My code is as follows:

Code: 
 Attempt to retrieve the data from the database based on the SSN entered and the date range
            Try
                strSQL = "SELECT TimeIn.DateIn, Format([TimeIn.StartTime], hh:mm) as StartTime, Format([TimeIn.EndTime], hh:mm) as EndTime, Format([TimeIn.EndTime]-[TimeIn.StartTime], hh:mm) as HoursWorked "
                strSQL = strSQL & "FROM Employees INNER JOIN TimeIn ON Employees.EmpID = TimeIn.EmpID "
                strSQL = strSQL & "WHERE (((TimeIn.SSN)=" & "" & strEmployee & "" & ") AND ((TimeIn.DateIn) Between #" & strStart & "# And #" & strEnd & "#));"

                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "\timeclock.mdb"

                Dim myCnn As New OleDbConnection(strConnection)
                Dim myDA As New OleDbDataAdapter(strSQL, myCnn)
                myCnn.Open()

                 Fill the dataset with the data retrieved from the database
                Dim ds As New DataSet
                myDA.Fill(ds, "TimeIn")

                 Populate the datagrid with the information in the dataset
                dgTimeView.DataSource = ds
                dgTimeView.DataMember = "TimeIn"

                 Close the connections, and set all values to nothing
                myCnn.Close()
                myCnn.Dispose()
                myDA.Dispose()
                ds.Dispose()
                strSQL = ""

                 If there is a problem with the data retrieved from the DB, display the error
                 information in Label1 so the user will know what happened
            Catch ex As Exception
                Dim strMessage As String
                strMessage = ex.Message.ToString
                strMessage = strMessage & "  " & ex.ToString

                Label1.Text = strMessage

            End Try

Thanks in advance,

Chris
 
Last edited by a moderator:
First, I wouldnt convert the DateTime values from the database. Id return them as regular DateTime fields.

To get the hours/minutes worked, youll need to get the values of start and end date into DateTime variables from your dataset. You can then Subtract them and get a TimeSpan object which will show you the hours (and minutes, etc.):
C#: 
DateTime startDate = (DateTime)ds.Tables["TimeIn"].Rows[0]["StartTime"];
DateTime endDate = (DateTime)ds.Tables["TimeIn"].Rows[0]["EndTime"];
TimeSpan diff = endDate.Subtract(startDate);
Debug.WriteLine(diff.Hours);

-Nerseus
 
Code: 
dim startDate as DateTime = ds.Tables("TimeIn").Rows(0)("StartTime")
dim endDate as DateTime = ds.Tables("TimeIn").Rows(0)("EndTime")
dim diff as timeSpan = endDate.Subtract(startDate)
Debug.WriteLine(diff.Hours)
 
Back
Top