Hello,

I'm writing a time entry program to record when an employee checks in and when they check out. I'm struggling on the part where I need to calculate a time span (time out - time in). I am selecting the In Time, Out Time, and Total columns. I want it to take the Out Time subtract it from the In Time and place that value in the Total column. Here is the code I have so far and have tried several variations of different updates but have had no luck. Where am I going wrong?

Code:
    Public Sub CalculateTotals()
        Dim mydataset As New DataSet
        Dim mytable As New DataTable
        Dim myrow As DataRow
        Dim combuilder As New OleDbCommandBuilder
        Dim Con As New OleDbConnection(My.Settings.TimeEntryConnectionString)
        Dim DaAd As New OleDbDataAdapter

        DaAd.SelectCommand = New OleDbCommand("SELECT [ID],[In Time],[Out Time],[Total] FROM [" & cmbTables.Text & "] WHERE ((([In Time]) Is Not Null) AND (([Out Time]) Is Not Null) AND (([Total]) Is Null))", Con)
        combuilder = New OleDbCommandBuilder(DaAd)
        combuilder.GetUpdateCommand()
        Con.Open()
        DaAd.Fill(mydataset, cmbTables.Text)
        mytable = mydataset.Tables(cmbTables.Text)

        Try

            For Each myrow In mytable.Rows
                'calculate totals
                Dim time1 As DateTime
                Dim time2 As DateTime

                time1 = DateTime.Parse(myrow.Item("In Time"))
                time2 = DateTime.Parse(myrow.Item("Out Time"))

                Dim ts As TimeSpan = time2 - time1
                myrow.Item("Total") = ts.TotalHours.ToString

            Next

            'just using this to see if the timespan is calculating correctly and it is
            DataGridView1.DataSource = mytable

            mytable.GetChanges()
            DaAd.Update(mydataset, cmbTables.Text)
            Con.Close()

        Catch ex As OleDbException
            MsgBox(ex.Message & vbCrLf & ex.Source)
        End Try

    End Sub
In Time and Out Time are formatted like so: "hh:mm" (ex: 14:30) and the Total column is a total amount of hours.


I keep getting a syntax error in the UPDATE statment. I'm not very fluent with OleDB yet and I'm not sure if I'm doing the update correctly. I have no problem with OleDB and single UPDATES or single field updates but when I am updating multiple rows with different values, I'm lost.

Any suggestions are more than welcome!

Thanks!