Can't UPDATE DATABASE |DATATABLE

[Erald]

I'm updating my database using this code. only id and attendance date is updated but the time(AM_TIME_IN etc...) is not updating. What could be wrong?

I'm using access and time(AM_TIME_IN etc...) datatype is shorttext

Dim a As String = Now.ToShortTimeString
If a.Contains("AM") Then
    a = "AM"
Else
    a = "PM"
End If
Dim datarow As Object
Dim dsx As New DataSet
Dim dtx As New DataTable
Dim dtz As DataTable = New DataTable
Dim addlog_today As Boolean = False
Dim daz As OleDbDataAdapter
Dim ds As New DataSet
Dim dt As New DataTable

Try
    If con.State = ConnectionState.Open Then
    con.Close()
    End If
    con.Open()
    dsx.Tables.Add(dtx)
    Dim da As New OleDbDataAdapter("Select * FROM Attendance where EMPID='" & emp.EMPID & "'and ATTENDANCEDATE='" & Now.ToShortDateString & "'", con)
    da.Fill(dtx)
    For Each datarow In dtx.Rows
    attendancedate = datarow.Item(2).ToString
    Next
    If attendancedate = "" Then
    Try
        Dim dtxx As DataTable = New DataTable
        Dim dx As OleDbDataAdapter = New OleDbDataAdapter("select * from Attendance", con)
        dx.Fill(dtxx)
        Dim r As DataRow = dtxx.NewRow
        r(1) = emp.EMPID
        r(2) = Now.ToShortDateString
        r(3) = emp.Designation
        dtxx.Rows.Add(r)
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(dx)
        dx.Update(dtxx)
    Catch ex As Exception

    End Try
    End If
    If a = "AM" Then

    ds.Tables.Add(dt)
    Dim dax As New OleDbDataAdapter("Select AM_TIME_IN,AM_TIME_OUT FROM Attendance where EMPID='" & emp.EMPID & "'and ATTENDANCEDATE='" & Now.ToShortDateString & "'", con)
    dax.Fill(dt)
    For Each datarow In dt.Rows
        timein = datarow.Item(0).ToString
        timeout = datarow.item(1).ToString
    Next

    If timein = "" Then
        Try

        daz = New OleDbDataAdapter("Select * FROM Attendance where EMPID='" & emp.EMPID & "'and ATTENDANCEDATE='" & Now.ToShortDateString & "'", con)
        daz.Fill(dtz)
        dtz.Rows(0).BeginEdit()
        dtz.Rows(0)(3) = Now.ToShortTimeString
        dtz.Rows(0).EndEdit()
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(daz)
        daz.Update(dtz)
        con.Close()
        Catch ex As Exception

        End Try

    ElseIf timeout = "" Then
        Try

        daz = New OleDbDataAdapter("Select * FROM Attendance where EMPID='" & emp.EMPID & "'and ATTENDANCEDATE='" & Now.ToShortDateString & "'", con)
        daz.Fill(dtz)
        dtz.Rows(0).BeginEdit()
        dtz.Rows(0)(4) = Now.ToShortTimeString
        dtz.Rows(0).EndEdit()
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(daz)
        daz.Update(dtz)
        con.Close()
        Catch ex As Exception

        End Try
    End If

    ElseIf a = "PM" Then
    ds.Tables.Add(dt)
    Dim dax As New OleDbDataAdapter("Select PM_TIME_IN,PM_TIME_OUT FROM Attendance where EMPID='" & emp.EMPID & "'and ATTENDANCEDATE='" & Now.ToShortDateString & "'", con)
    dax.Fill(dt)
    For Each datarow In dt.Rows
        timein = datarow.Item(0).ToString
        timeout = datarow.Item(1).ToString
    Next
    If timein = "" Then
        Try

        daz = New OleDbDataAdapter("Select * FROM FROM Attendance where EMPID='" & emp.EMPID & "'and ATTENDANCEDATE='" & Now.ToShortDateString & "'", con)
        daz.Fill(dtz)
        dtz.Rows(0).BeginEdit()
        dtz.Rows(0)(5) = Now.ToShortTimeString
        dtz.Rows(0).EndEdit()
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(daz)
        daz.Update(dtz)
        con.Close()
        Catch ex As Exception
        MsgBox(ex.ToString)
        End Try
    ElseIf timeout = "" Then
        Try

        daz = New OleDbDataAdapter("Select * FROM Attendance where EMPID='" & emp.EMPID & "'and ATTENDANCEDATE='" & Now.ToShortDateString & "'", con)
        daz.Fill(dtz)
        dtz.Rows(0).BeginEdit()
        dtz.Rows(0)(6) = Now.ToShortTimeString
        dtz.Rows(0).EndEdit()
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(daz)
        daz.Update(dtz)
        con.Close()
        Catch ex As Exception
        MsgBox(ex.ToString)
        End Try
    End If
    End If

Catch ex As Exception

End Try

[JOSheaIV]

That's a lot of code, and quickly reading it (and while I only know a big VB), there are a few things I see

First of all you have foreach loops that assign to a variable. However, if I am reading it right, the way you do this only the last index gets assigned the value (so like attendence, will be the value from the last record).

Now something you might want to try. Run this in debug mode and place some breakpoints on it. One thing I would like to rule out would be, is the database even pulling back records to update? Maybe your fetches are coming back with no results (and therefore, you can't really update). Also while I don't know the full design behind the table and code, I do notice a few things.

If attendence, timein, and timeout are NOT blank, then you also never seem to be executing an update command. So that's something to check for as well

[Taywin]

As JOShealV said, your code is wrong starting at for each loop (Line 23~35). What is the purpose of the loop?

You are reading each row data into attendancedate, but keep replacing the value until you are done with all records you retrieved. the whole datarow. However, your datarow is initialized as an empty object and is never assigned anything before using in line 23?

Another thing, your attendancedate is no where to be found before using in line 24. In other words, where did you declare the variable and/or initialize?

Not sure how this portion of code gets executed properly...