Matagal ko na po ginagawan ng paraan na mapagsama ko po yung limang table ko sa isang main table at isave yung resulta sa database pero di ko pa rin magawa. Patulong naman po validation of time logs po kase itong ginagawa ko po. Ito po yung code ko. Gamit ko po pala visual studio 2008. salamat po ngmarame sa makakatulong po sa akin.
Public Sub testRead()
conn1 = New OleDbConnection
conn1.ConnectionString = conCopy
Dim query1, query2, query3, query4 As String
Dim sqlTable1, sqlTable2, sqlTable3, sqlTable4, mainTable As New DataTable
Try
conn1.Open()
query1 = "SELECT USERID, CHECKDATE,MIN(CHECKTIME) AS [AM IN] FROM CHECKINOUT WHERE " _
& "USERID = 562 AND CHECKTYPE = 'Check In' GROUP BY USERID,CHECKDATE"
query2 = "SELECT USERID, CHECKDATE, MIN(CHECKTIME) AS [LUNCH OUT] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Break Out' GROUP BY USERID,CHECKDATE"
query3 = "SELECT USERID, CHECKDATE, MAX(CHECKTIME) AS [LUNCH IN] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Break In' GROUP BY USERID, CHECKDATE"
query4 = "SELECT USERID, CHECKDATE, MAX(CHECKTIME) AS [CHECK OUT] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Check Out' GROUP BY USERID, CHECKDATE"
Dim ds1, ds2, ds3, ds4 As New DataSet
Dim adapter1 As New OleDbDataAdapter(query1, conn1)
Dim adapter2 As New OleDbDataAdapter(query2, conn1)
Dim adapter3 As New OleDbDataAdapter(query3, conn1)
Dim adapter4 As New OleDbDataAdapter(query4, conn1)
adapter1.Fill(sqlTable1)
adapter2.Fill(sqlTable2)
adapter3.Fill(sqlTable3)
adapter4.Fill(sqlTable4)
conn1.Close()
adapter1.Dispose()
adapter2.Dispose()
adapter3.Dispose()
adapter4.Dispose()
For Each row As DataRow In sqlTable2.Rows
MsgBox(row.Item(0) & " " & row.Item(1) & " " & row.Item(2))
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Public Sub testRead()
conn1 = New OleDbConnection
conn1.ConnectionString = conCopy
Dim query1, query2, query3, query4 As String
Dim sqlTable1, sqlTable2, sqlTable3, sqlTable4, mainTable As New DataTable
Try
conn1.Open()
query1 = "SELECT USERID, CHECKDATE,MIN(CHECKTIME) AS [AM IN] FROM CHECKINOUT WHERE " _
& "USERID = 562 AND CHECKTYPE = 'Check In' GROUP BY USERID,CHECKDATE"
query2 = "SELECT USERID, CHECKDATE, MIN(CHECKTIME) AS [LUNCH OUT] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Break Out' GROUP BY USERID,CHECKDATE"
query3 = "SELECT USERID, CHECKDATE, MAX(CHECKTIME) AS [LUNCH IN] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Break In' GROUP BY USERID, CHECKDATE"
query4 = "SELECT USERID, CHECKDATE, MAX(CHECKTIME) AS [CHECK OUT] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Check Out' GROUP BY USERID, CHECKDATE"
Dim ds1, ds2, ds3, ds4 As New DataSet
Dim adapter1 As New OleDbDataAdapter(query1, conn1)
Dim adapter2 As New OleDbDataAdapter(query2, conn1)
Dim adapter3 As New OleDbDataAdapter(query3, conn1)
Dim adapter4 As New OleDbDataAdapter(query4, conn1)
adapter1.Fill(sqlTable1)
adapter2.Fill(sqlTable2)
adapter3.Fill(sqlTable3)
adapter4.Fill(sqlTable4)
conn1.Close()
adapter1.Dispose()
adapter2.Dispose()
adapter3.Dispose()
adapter4.Dispose()
For Each row As DataRow In sqlTable2.Rows
MsgBox(row.Item(0) & " " & row.Item(1) & " " & row.Item(2))
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub