Add data to existing row in datatable

Question

0.00/5 (No votes)

See more:

I am trying to combine 4 different select statement from one table into one datatable using visual studio 2008 but I can only add them as new row. This is the code that I am doing:

What I have tried:

I already tried datatable.merge and it still add as new row. Please help!!

VB

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 MIN(CHECKTIME) AS [LUNCH OUT] FROM CHECKINOUT WHERE USERID = 562 " _
           & "AND CHECKTYPE = 'Break Out' GROUP BY USERID,CHECKDATE"
           query3 = "SELECT MAX(CHECKTIME) AS [LUNCH IN] FROM CHECKINOUT WHERE USERID = 562 " _
           & "AND CHECKTYPE = 'Break In' GROUP BY USERID, CHECKDATE"
           query4 = "SELECT MAX(CHECKTIME) AS [CHECK OUT] FROM CHECKINOUT WHERE USERID = 562 " _
           & "AND CHECKTYPE = 'Check Out' GROUP BY USERID, CHECKDATE"

           mainTable.Columns.Add("ID")
           mainTable.Columns.Add("DATE")
           mainTable.Columns.Add("CHECK IN")
           mainTable.Columns.Add("LUNCH OUT")
           mainTable.Columns.Add("LUNCH IN")
           mainTable.Columns.Add("CHECK OUT")

           cmd = New OleDbCommand(query1, conn1)
           Dim reader As OleDbDataReader
           reader = cmd.ExecuteReader

           While reader.Read
               Dim irow As DataRow = mainTable.NewRow
               irow("ID") = reader.Item(0)
               irow("DATE") = DateTime.Parse(reader.Item(1)).ToString("MM/dd/yyyy")
               irow("CHECK IN") = DateTime.Parse(reader.Item(2)).ToString("HH:mm tt")
               mainTable.Rows.Add(irow)
           End While

           reader.Close()
           cmd.Dispose()

           cmd2 = New OleDbCommand(query2, conn1)
           Dim reader2 As OleDbDataReader
           reader2 = cmd2.ExecuteReader

           While reader2.Read
               Dim irow As DataRow = mainTable.NewRow
               irow("LUNCH OUT") = DateTime.Parse(reader2.Item(0)).ToString("HH:mm tt")
               mainTable.Rows.Add(irow)
           End While

           conn1.Close()

       Catch ex As Exception
           MsgBox(ex.Message)
       End Try
       Return mainTable

Posted 27-Sep-19 0:32am

RaydenFox

Updated 29-Sep-19 2:36am

Add a Solution

3 solutions

Solution 2

Since all of your data has a common link, i.e., userID, you may wish to make an series of JOIN's on the table.

SQL

SELECT A.userID, A.checkDate, A.CheckIN, B.LunchIn, C.LunchOut, D.CheckOut
FROM A 
LEFT JOIN CHECKIN B ON A.userid = b.userID
LEFT JOIN CHECKIN C ON A.userid = c.userID
LEFT JOIN CHECKIN D ON A.userid = d.userID

Two notes:
- I am assuming you cannot checkout, lunchin, lunch out unless you first check in as you can't do any of these until your actually "get there"
- Using LEFT JOIN, NULL's are returned automatically where there is not match.

Posted 27-Sep-19 2:22am

W∴ Balboos, GHB

Comments

RaydenFox 28-Sep-19 2:21am

The 4 queries are from one table I only want to get the different time of each date by using MIN and MAX

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RaydenFox · Accepted Answer · 2019-09-29T02:36:00

Thanks for your suggestion and changed your given code to IIF statement and now it works , the only problem that i have right now is when null value is passed on variable that will be formatted to date it shows "Conversion of type DBNull to string is not allowed"
by the way for future references and might help others this is the code that I have done

SELECT CHECKINOUT.USERID,CHECKINOUT.CHECKDATE,
MIN(IIF(CHECKINOUT.CHECKTYPE='Check In',CHECKINOUT.CHECKTIME,NULL) ) AS 'AM IN',
MIN(IIF(CHECKINOUT.CHECKTYPE='Break Out',CHECKINOUT.CHECKTIME,NULL) ) AS 'LUNCH OUT',
MAX(IIF(CHECKINOUT.CHECKTYPE='Break In',CHECKINOUT.CHECKTIME,NULL) ) AS 'LUNCH IN',
MAX(IIF(CHECKINOUT.CHECKTYPE='Check Out',CHECKINOUT.CHECKTIME,NULL) ) AS 'PM OUT'
FROM CHECKINOUT WHERE CHECKINOUT.USERID = 563
GROUP BY CHECKINOUT.USERID, CHECKINOUT.CHECKDATE

Maciej Los · Accepted Answer · 2019-09-27T01:44:00

Solution 1

If you would like to run 4 queries, you have to do that in a loop. But(!)... i'd suggest to change a set of queries into one. It would be something like this:

SQL

SELECT DISTINCT USERID, CHECKDATE, 
    [CheckIn] = MIN(CASE WHEN CHECKTYPE = 'Check In' THEN CHECKTIME ELSE NULL END),
    [LunchIN] = MIN(CASE WHEN CHECKTYPE = 'Lunch In' THEN CHECKTIME ELSE NULL END),
    [LunchOut] = MAX(CASE WHEN CHECKTYPE = 'Lunch Out' THEN CHECKTIME ELSE NULL END),
    [CheckOut] = MAX(CASE WHEN CHECKTYPE = 'Check Out' THEN CHECKTIME ELSE NULL END)
FROM CHECKINOUT
WHERE WHERE USERID = 562
GROUP BY USERID, CHECKDATE

ANother way is to use PIVOT[^].

Posted 27-Sep-19 1:44am

Maciej Los

Comments

RaydenFox 28-Sep-19 2:19am

I tried your answer but I am having syntax error missing operator in [CheckIn] = MIN(CASE WHEN CHECKTYPE = 'Check In' THEN CHECKTIME ELSE NULL END)

RaydenFox 28-Sep-19 4:24am

Thanks for your suggestion and changed your given code to IIF statement and now it works , the only problem that i have right now is when null value is passed on variable that will be formatted to date it shows "Conversion of type DBNull to string is not allowed"
by the way for future references and might help others this is the code that I have done

SELECT CHECKINOUT.USERID,CHECKINOUT.CHECKDATE,
MIN(IIF(CHECKINOUT.CHECKTYPE='Check In',CHECKINOUT.CHECKTIME,NULL) ) AS 'AM IN',
MIN(IIF(CHECKINOUT.CHECKTYPE='Break Out',CHECKINOUT.CHECKTIME,NULL) ) AS 'LUNCH OUT',
MAX(IIF(CHECKINOUT.CHECKTYPE='Break In',CHECKINOUT.CHECKTIME,NULL) ) AS 'LUNCH IN',
MAX(IIF(CHECKINOUT.CHECKTYPE='Check Out',CHECKINOUT.CHECKTIME,NULL) ) AS 'PM OUT'
FROM CHECKINOUT WHERE CHECKINOUT.USERID = 563
GROUP BY CHECKINOUT.USERID, CHECKINOUT.CHECKDATE

Maciej Los 30-Sep-19 5:54am

You're very welcome.