Datagrid problem displaying time

[dakota97]

Hi all,

 

I've got a datagrid that I'm populating from an Access DB. My DB has a table with time and date entries from employee clock in and out times. The problem that I'm having is when I try to calculate the total hours worked on a given day, it's not working out at all. I have tried everything that I can, including spending hours scouring the different posts to see how to determine the hours worked. The table in the DB is set up as follows:

 

TimeID - AutoNumber - Primary Key

EmpID - Number

SSN - Text

DateIn - Date/Time

StartTime - Date/Time

EndTime - Date/Time

 

My form has a textbox to type in the employee's SSN and 2 date/time pickers to select the timeframe to view. My code to generate the dataset and populate the datagrid is as follows:

 

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

 

Dim strEmployee As String = txtEmpSSN.Text.Trim.ToString

Dim strStart As String = dtStart.Value.ToShortDateString

Dim strEnd As String = dtEnd.Value.ToShortDateString

 

Dim strSQL As String

Dim strConnection As String

 

Try

strSQL = "SELECT TimeIn.SSN, TimeIn.DateIn, Format([TimeIn.StartTime], 'hh:mm') as StartTime, Format([TimeIn.EndTime], 'hh:mm') as EndTime "

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 & "#));"

 

Dim strPath As String = Application.StartupPath()

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

 

Dim ds As New DataSet

 

myDA.Fill(ds, "TimeIn")

 

dgTimeView.DataSource = ds

dgTimeView.DataMember = "TimeIn"

 

myCnn.Close()

myCnn.Dispose()

myDA.Dispose()

ds.Dispose()

strSQL = ""

 

Catch ex As Exception

Dim strMessage As String

strMessage = ex.Message.ToString

strMessage = strMessage & " " & ex.ToString

 

Label1.Text = strMessage

 

End Try

End Sub

 

 

Any help at all would be greatly appreciated, as I have been racking my brain trying to figure this out.

 

Thanks in advance,

 

Chris

[hog]

OK, I have worked on times etc in an application here that calc user time spent on a given assembly run.

 

First thing tho, why hve you quoted this bit of code?

 

& "'" & strEmployee & "'" & ")

 

rather than

 

& strEmployee & ")"

 

can you give me a bit more detail and I'll see what I can do:)

[dakota97]

hog,

 

Sorry for the delay in response, but something was messed up with my account. Robby helped me get it straightened out (THANKS ROBBY!!!).

 

Anyways, I finally figured out how to get the total hours with the following addition into my sql statement. It added another column to the datagrid with the correct hours and minutes that the employee worked:

 

 

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 & "#));"

 

 

However, this poses another question as to how I can add up all of the hours worked and display the result in a textbox, or add another row to the datagrid with only the total hours. I think I'd rather have it in a textbox, but what would you suggest?

 

Thanks in advance,

 

Chris