How to set an hour before USER/EMPLOYEE can timeout ? VB.NET

Question

1.00/5 (1 vote)

See more:

Problem / I Want to happen

1) If employee/user time in she have to wait 3 or 5 hours to time out

2)if she tried to time out less than 3 or 5 hours she will get a messagebox/label that he/she have already timed in

This is my codes

getQuery = "SELECT dtr.time_in, dtr.date FROM dtr, employee WHERE employee.employee_id ='" & empid.Text & _
           "'AND dtr.employee_id ='" & empid.Text & "' AND dtr.rfid ='" & txtRFID.Text & "' AND dtr.date ='" & Date.Now.ToString("yyyy-MM-dd") & "'"
     getCommand = New MySqlCommand(getQuery, MySQLConnection)
     getReader = getCommand.ExecuteReader

     If getReader.Read = True Then

         TimeIN = (getReader.Item("time_in").ToString)
         getDate = (getReader.Item("date"))

     End If

     getReader.Close()


     getQuery = "SELECT dtr.time_out, dtr.date FROM dtr, employee WHERE employee.employee_id ='" & empid.Text & "' AND dtr.employee_id ='" & _
         empid.Text & "' AND dtr.rfid ='" & txtRFID.Text & "' AND dtr.date ='" & Date.Now.ToString("yyyy-MM-dd") & "'"
     getCommand = New MySqlCommand(getQuery, MySQLConnection)
     getReader = getCommand.ExecuteReader

     If getReader.Read = True Then

         TimeOUT = (getReader.Item("time_out").ToString)
         getDate = (getReader.Item("date"))


     End If

     getReader.Close()

     If TimeIN = "" Then
         disableTimeOutColor()
         TimeIN = Nothing
         TimeOUT = Nothing
         getDate = Nothing
     ElseIf TimeIN <> "" And TimeOUT <> "" And getDate = Date.Today Then
         TimeOUT = Nothing
         TimeIN = Nothing
         getDate = Nothing
     ElseIf TimeIN <> "" Then
         disableTimeINColor()
         TimeIN = Nothing
         TimeOUT = Nothing
         getDate = Nothing
     ElseIf TimeOUT = "" Then
         disableTimeINColor()
         TimeOUT = Nothing
         TimeIN = Nothing
         getDate = Nothing
     ElseIf TimeOUT <> "" Then
         disableTimeOutColor()
         TimeOUT = Nothing
         TimeIN = Nothing
         getDate = Nothing
     ElseIf TimeIN = "" And TimeOUT = "" And getDate = "" Then

         disableTimeOutColor()
         TimeOUT = Nothing
         TimeIN = Nothing
         getDate = Nothing
     End If

What I have tried:

I have tried using this codes

If DateDiff(DateInterval.Hour, CDate(TimeIN), CDate(Date.Now)) Then

            getTimeOUT()
            TimeOUT = Nothing
            TimeIN = Nothing
            getDate = Nothing

Posted 9-May-18 9:42am

Member 13810830

Updated 9-May-18 10:20am

Add a Solution

2 solutions

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)

Wendelius · Answer 1 · 2018-05-09T09:55:00

Solution 1

First of all, don't concatenate data directly from UI objects to SQL statements. This will leave you open to SQL injections. See SQL injection - Wikipedia[^] The preferred way is to use MySqlParameter Class[^]

What comes to the actual question, one big problem is that you seem to be storing date and time in a character field. In order to solve the problem efficiently, you should use datetime, see MySQL :: MySQL 8.0 Reference Manual :: 11.3.1 The DATE, DATETIME, and TIMESTAMP Types[^]

If the data would be datetime in mysql you could use DateTime Structure (System)[^] in your program and utilizr xomparisonds and calculations datetime has to offer.

For example if you want to check if three hours have passed after TimeIn it could look like

VB

If System.DateTime.Now.Subtract(TimeIn).TotalHours < 3 Then
    MessageBox.Show("Not yet")
End If

Posted 9-May-18 9:55am

Wendelius

Updated 9-May-18 10:07am

v2

Comments

Member 13810830 9-May-18 16:28pm

In my database timein is time

I'm using RFID to it can you guys fix my problem? T_T i really need this get fixed

Wendelius 9-May-18 16:33pm

If the data type in the database is already datetime then also use datetime in your program. For example when fetching the data

getDate = CType(getReader.Item("date"), System.DateTime)

If the data can be null you also have to check that .

Now to do the comparison, you can use code similar to what I wrote in the answer.

Not sure what RFID has to do with this calculation though.

Member 13810830 9-May-18 16:36pm

in my database timein = time and timeout = time

I've been trying to fix this for days.. and i still can't get it right ..
i get confused a lot ..

Sorry i'm just new i don't know what is good code or bad code as long it works it's good for me

Wendelius 9-May-18 16:49pm

If the data type is time, then use TimeSpan Structure (System)[^] in VB

Member 13810830 9-May-18 16:51pm

Can you give me an example of it? That is pretty close to fix my problem?

Wendelius 9-May-18 17:18pm

You mean the comparison? If yes, it would be something like

        If getDate.Subtract(TimeIn).TotalHours < 3 Then
            MessageBox.Show("Not yet")
        End If

However, I don't know the meaning of the fields so please adjust the code based on your requirements.

Member 13810830 10-May-18 4:04am

I tried using this code
Dim getQuery As String = "SELECT dtr.time_out, dtr.date FROM dtr WHERE dtr.employee_id = @Id AND dtr.rfid = @rfid AND dtr.date = @date;"
Using cmd As New MySqlCommand(getQuery, MySQLConnection)
cmd.Parameters.Add("@Id", MySqlDbType.Int32).Value = empid.Text
cmd.Parameters.Add("@rfid", MySqlDbType.Int32).Value = RFID.Text
cmd.Parameters.Add("@date", MySqlDbType.VarChar).Value = Date.Now.ToString("yyyy-MM-dd")
Using dr As MySqlDataReader = cmd.ExecuteReader
If dr.Read Then
TimeOUT = dr.Item("time_out").ToString
getDate = dr.Item("date")
getTimeOUT()
End If
End Using
End Using

But i still don't know what i will do next and how will i do it

Wendelius 10-May-18 4:09am

If the time_out is time data type in the database, don't put it into a string variable. Use timespan as I explained. When using timespan yuo can do calculation with it. In other words

TimeOUT = CType(dr.Item("time_out"), System.TimeSpan)

Member 13810830 10-May-18 4:20am

Where this codes will go? if TimeIn > 3 Then
GetTimeout
Else if TimeIn < 3 Then
Messagebox.Show("UNABLE TO TIME OUT")

Member 13810830 10-May-18 4:25am

when i change my string into timespan it says something input string was not in correct format

Wendelius 10-May-18 6:13am

When you read the data from the database, you should then convert the data from the data reader to correct format.

...
If dr.Read Then
TimeOUT = CType(dr.Item("time_out"), System.TimeSpan)
...

If the convertion cannot be done using CType, aAnother variation is to use GetTimeSpan
...
If dr.Read Then
TimeOUT = dr.GetTimeSpan(0)
...

Patrice T · Answer 2 · 2018-05-09T10:22:00

VB

getQuery = "SELECT dtr.time_out, dtr.date FROM dtr, employee WHERE employee.employee_id ='" & empid.Text & "' AND dtr.employee_id ='" & _
         empid.Text & "' AND dtr.rfid ='" & txtRFID.Text & "' AND dtr.date ='" & Date.Now.ToString("yyyy-MM-dd") & "'"

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]