Total time problem

[dakota97]

Hi all,

 

I'm 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:

 

' 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

Edited May 23, 2006 by PlausiblyDamp

[Nerseus]

First, I wouldn't convert the DateTime values from the database. I'd return them as regular DateTime fields.

 

To get the hours/minutes worked, you'll 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.):

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

[dakota97]

Ok, but how would I do it in VB.Net?

[PlausiblyDamp]

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)

[tinJeff]

helo!

how can i calculate time range in crystal report?