For almost one week of debugging and configuring every loop, I finally solved the problem. Here is my solution:
cmd3 = New OleDbCommand(query, conn1)
cmd3.Parameters.Clear()
cmd3.Parameters.AddWithValue("@ID", id)
Dim reader As OleDbDataReader = cmd.ExecuteReader
schemaTable = New DataTable
Dim adapter As New OleDbDataAdapter(cmd3)
Dim i As Integer
adapter.Fill(schemaTable)
conn4.Open()
query2 = "INSERT INTO LOGDATES(ID,CHECKDATE,AMIN,LUNCHOUT,LUNCHIN,PMOUT) " _
& "VALUES(@ID,@DATE,@AM,@LOUT,@LIN,@PM)"
cmd2 = New OleDbCommand(query2, conn4)
startDay = DateTime.Parse(fromDate)
endDay = DateTime.Parse(toDate)
If reader.HasRows Then
While reader.Read
i = 0
Do While startDay <= endDay
If i <= schemaTable.Rows.Count - 1 Then
With cmd2
.Parameters.Clear()
.Parameters.AddWithValue("@ID", reader.Item(0))
If DateTime.Parse(startDay).Date = DateTime.Parse(schemaTable.Rows(i)(1)).Date Then
.Parameters.AddWithValue("@DATE", DateTime.Parse(schemaTable.Rows(i)(1)).Date)
If Not IsDBNull(schemaTable.Rows(i)(2)) Then
.Parameters.AddWithValue("@AM", DateTime.Parse(schemaTable.Rows(i)(2)).ToString("HH:mm tt"))
Else
.Parameters.AddWithValue("@AM", DBNull.Value)
End If
If Not IsDBNull(schemaTable.Rows(i)(3)) Then
.Parameters.AddWithValue("@LOUT", DateTime.Parse(schemaTable.Rows(i)(3)).ToString("HH:mm tt"))
Else
.Parameters.AddWithValue("@LOUT", DBNull.Value)
End If
If Not IsDBNull(schemaTable.Rows(i)(4)) Then
.Parameters.AddWithValue("@LIN", DateTime.Parse(schemaTable.Rows(i)(4)).ToString("HH:mm tt"))
Else
.Parameters.AddWithValue("@LIN", DBNull.Value)
End If
If Not IsDBNull(schemaTable.Rows(i)(5)) Then
.Parameters.AddWithValue("@PM", DateTime.Parse(schemaTable.Rows(i)(5)).ToString("HH:mm tt"))
Else
.Parameters.AddWithValue("@PM", DBNull.Value)
End If
i = i + 1
Else
.Parameters.AddWithValue("@DATE", DateTime.Parse(startDay).ToString("MM/dd/yyyy"))
.Parameters.AddWithValue("@AM", DBNull.Value)
.Parameters.AddWithValue("@LOUT", DBNull.Value)
.Parameters.AddWithValue("@LIN", DBNull.Value)
.Parameters.AddWithValue("@PM", DBNull.Value)
End If
.ExecuteNonQuery()
End With
End If
startDay = startDay.AddDays(1)
Loop
End While
I added another connection and fill a datatable with it and loop inside the date loop just by using the IF statement. Now it prints all of the users attendance along with the dates that he/she absents.