Combine five datatable into one datatable and save it to database

[nelvin99]

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

 

[DrewV]

ts mas maganda mag provide ka ng sample data, tapos sample output na gusto mo..
kung iisipin ko at mag aassume lang, meron kang main table para sa mga USER ..
eto logic ayusing mo nlng sql coding (left join or right join)


select u.userid, checkin.[AM IN],lunchout.[LUNCH OUT], lunchin.[LUNCH IN],checkout.[CHECK OUT] from USER u

let join (SELECT USERID, CHECKDATE,MIN(CHECKTIME) AS [AM IN] FROM CHECKINOUT WHERE " _
& "USERID = 562 AND CHECKTYPE = 'Check In' GROUP BY USERID,CHECKDATE) checkin on checkin.userid=u.userid

left join (SELECT USERID, CHECKDATE, MIN(CHECKTIME) AS [LUNCH OUT] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Break Out' GROUP BY USERID,CHECKDATE) lunchout on lunchout.userid=u.userid

left join (SELECT USERID, CHECKDATE, MAX(CHECKTIME) AS [LUNCH IN] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Break In' GROUP BY USERID, CHECKDATE) lunchin on lunchin.userid=u.userid

left join (SELECT USERID, CHECKDATE, MAX(CHECKTIME) AS [CHECK OUT] FROM CHECKINOUT WHERE USERID = 562 " _
& "AND CHECKTYPE = 'Check Out' GROUP BY USERID, CHECKDATE) checkout on checkout.userid=u.userid;


sample output(pagpantayin mo nlng):
USERID | AM IN| LUNCH OUT| LUNCH IN| CHECKOUT
562| 7:00| 12:00| 13:00| 16:00

 

[WP Sil]

Basahin mo yung "views"
Sample