Check if Mysql table row is NULL using MySqlDataReader

Question

I am developing a biometric daily monitoring system and I need to check if a Mysql table row has already been inserted with data based on the date.

I found this post, Limiting the time in and time out in a day in VB.NET? But i can'f figure out how to use it in my code.

The flow is, the first login of an employee for the day will be amIn, the next is amOut, next is pmIn, and last is pmOut.

LOGIC :

If 'amIn' is not NULL 
         then insert data to 'amOut'
else if 'amOut' is not NULL
         then insert data to 'pmIn'
else if 'pmIn' is not NULL
         then insert data to 'pmOut'

VB.NET code

    #CONNECTION STRING HERE
    conn.Open()
    Dim sqlCommand As New MySqlCommand
    Dim empDate As New DateTime(Convert.ToInt32(empYear.Text), Convert.ToInt32(empMonth.Text), Convert.ToInt32(empDay.Text), Convert.ToInt32(empHour.Text), Convert.ToInt32(empMin.Text), Convert.ToInt32(empSec.Text))
    Dim str_insertAttendance As String
    Dim reader As MySqlDataReader
    Try
        sqlCommand.Connection = conn
        str_insertAttendance = "select count(*) from attendance where empid=@id and date=@date and amin=@amIn and amout=@amOut and pmin=@pmIn and pmout=@pmOut)"
        sqlCommand.CommandText = str_insertAttendance
        reader = sqlCommand.ExecuteReader()
        If reader.Read() Then
            If reader.IsDBNull(0) Then
                str_insertAttendance = "insert into attendance values (@id, @date, @amIn, @amOut, @pmIn, @pmOut)"
                sqlCommand.CommandText = str_insertAttendance
                sqlCommand.Parameters.AddWithValue("@id", Convert.ToInt32(empID.Text))
                sqlCommand.Parameters.AddWithValue("@date", empDate)
                sqlCommand.Parameters.AddWithValue("@amIn", empDate)
                sqlCommand.Parameters.AddWithValue("@amOut", "")
                sqlCommand.Parameters.AddWithValue("@pmIn", "")
                sqlCommand.Parameters.AddWithValue("@pmOut", "")
                sqlCommand.ExecuteNonQuery()
            End If
        End If
        Return True
        conn.Close()
    Catch ex As Exception
        Return False
        MsgBox("Error occured: Could not insert record")
    End Try

TABLES

CREATE TABLE `employee` (
  `empID` int(11) NOT NULL,
  PRIMARY KEY (`empID`)
);

CREATE TABLE `attendance` (
  `empID` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `amIn` datetime DEFAULT NULL,
  `amOut` datetime DEFAULT NULL,
  `pmIn` datetime DEFAULT NULL,
  `pmOut` datetime DEFAULT NULL,
  KEY `empID` (`empID`),
  CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`empID`) REFERENCES `employee` (`empID`)
);

You probably intended the unique constraint on date to allow one record per employee per day but a) it will really allow only one record per day and b) one In-Out record per emp per day is probably not realistic - people take time off for doctor's appointments, court hearings for your child etc all the time/ What are you trying to do with "`insert ignore into...`"? — Ňɏssa Pøngjǣrdenlarp, Jan 26 '17 at 15:02
hi, i have updated my table. i need to get at least a pair of in-out record for a day, one in the morning and one in the afternoon. basically the employee is required to in-out in the morning and in-out in the afternoon. i need to check whether if `amIn` has value then add to `amOut` else add to `pmIn` else add data to `pmOut` — oozmac, Jan 26 '17 at 23:02
I would do it differently. Store the DateTimeIn on Employee, then when they clock out create an attendance record and null out the Emp.InTime. Attendance just needs to be {EmpId, DateTimeIn, DateTimeOut} and maybe a comment (?); the point is there is no need to split date and time. It would be very easy to query who is clocked in and easy to run a query to clock out everyone who forgot — Ňɏssa Pøngjǣrdenlarp, Jan 26 '17 at 23:13
i am sorry, i read through and through i am unable to catch up with what you are trying to suggest. :( if there is only a way that i could see a draft. just a layman's `if else` or the `table columns` you suggest. i'm really sorry if i don't follow — oozmac, Jan 26 '17 at 23:18
Until they clock out, you dont really have a complete record. Store the TimeIn in the Employee table. When they clock out, use that time and DateTime now for a new attendance record — Ňɏssa Pøngjǣrdenlarp, Jan 26 '17 at 23:20

score 2 · Answer 1 · answered Jan 26 '17 at 09:01

2

Maybe it's the easiest way, to do something like that:

SELECT COUNT(*) FROM attendance WHERE empID=@id and date=@date and amIn=@amIn and amOut=@amOut and pmIn=@pmIn and pmOut=@pmOut;

and if it returns >0 then it will not insert the data.

answered Jan 26 '17 at 09:01

muffi

366
6
18

i'm new with vb.net.. how do you go about doing that> – oozmac Jan 26 '17 at 21:24
i have edited my question and vb code. can you assist me with this please? – oozmac Jan 27 '17 at 00:22
Well I am not able to serve you the solution. I can only assist you. If you like to try it via count(*) then this is a method for `If CType(sqlCommand.ExecuteScalar, Integer) > 0 Then Else End If` – muffi Jan 27 '17 at 05:16
hi, i have also used this in my code. thank you so much. this helped me as well – oozmac Jan 31 '17 at 19:54

score 2 · Accepted Answer · answered Jan 28 '17 at 23:29

Your scheme is too complicated and restrictive. There is no reason to record 2 and only 2 In/Out pairs per day. What if someone has an emergency after lunch - you have nowhere to record a 3rd time pair.

A simpler attendance entity:

CREATE TABLE `attendance` (
  `empID` int(11) DEFAULT NULL,
  `TimeIn` datetime DEFAULT NULL,
  `TimeOut` datetime DEFAULT NULL,
  KEY `empID` (`empID`),
  CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`empID`) REFERENCES `employee` (`empID`)
);

Storing AM/PM pairs of In/Out times doesnt allow for things like a doctor's appointment after lunch or other emergency
The PM pair is just repeating the AM structure so it need not exist
The Date will be part of the In and Out times, so its not needed
Only complete, valid records (ones with an In and Out time) will be saved.
- This will allow whatever app processes them to zip thru them and not have to check if there is an OutTime or if there is a PM time pair.
- Also, we wont have to try and find them

So, if only complete records are saved, we need a place to save the TimeIn. Employee is a good place for this:

CREATE TABLE `employee` (
  `Id` int(11) NOT NULL,
  `TimeIn` datetime DEFAULT NULL,
  PRIMARY KEY (`empID`)
);

Since this is homework, the code is up to you. You should be able to do everything in one method and 2-3 queries. When a timeclock event happens:

Check the Employee table to see if there is a TimeIn for this employee
If there is not a time, save the current DateTime to it

If there is a time, then they are going out/home/doctor's/jail/someplace:

Get the TimeIn into a variable
INSERT INTO Attendance, using the EmpId, TimeIn and DateTime.Now for the out time.
UPDATE Employee and set the TimeIn to Null

Especially the clock out steps could be done more efficiently using a stored procedure which does both things at once. The first steps are also more easily done from a DataTable, even combining steps 1 and 3.

the report i am trying to come up with is the image attached [here](http://stackoverflow.com/questions/41670829/display-values-of-datetime-to-different-rows-in-a-datagridview) will i be able to do that report with your above suggestion? : — oozmac, Jan 28 '17 at 23:38
Yes, that report is possible. Just add a AM/PM indicator to the table and set it as part of making a record; use the InTime to determine which one it is — Ňɏssa Pøngjǣrdenlarp, Jan 29 '17 at 00:24
thank you, i'll be doing this right away and get back to this post for approval of the answer. thank you. — oozmac, Jan 29 '17 at 00:26

Check if Mysql table row is NULL using MySqlDataReader

2 Answers2