时间戳

首页 > 解决方案 > 如何在 USER/EMPLOYEE 超时前设置一个小时？VB.NET MYSQL

mysql - 如何在 USER/EMPLOYEE 超时前设置一个小时？VB.NET MYSQL

问题描述

问题/我想发生

1）如果员工/用户时间在她必须等待 3 或 5 小时才能超时

2) 如果她尝试超时少于 3 或 5 小时，她将收到一个他/她已经超时的消息框/标签

这是我的代码

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

标签： mysqlvb.net

解决方案

在编译选项卡的项目属性中打开 Option Strict。这将在您运行代码之前捕获许多潜在错误。

首先，您的查询。您只从一张表中进行选择，因此似乎没有任何理由引用员工表。使用参数重写这段代码非常重要；否则你很容易受到 SQL 注入的攻击。

Using cn As New MySqlConnection("Your connection string")
            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, cn)
                cmd.Parameters.Add("@Id", MySqlDbType.Int32).Value = empid.Text
                cmd.Parameters.Add("@rfid", MySqlDbType.Int32).Value = txtRFID.Text
                cmd.Parameters.Add("@date", MySqlDbType.VarChar).Value = Date.Now.ToString("yyyy-MM-dd")
                cn.Open()
                Using dr As MySqlDataReader = cmd.ExecuteReader
                    If dr.Read Then
                        TimeOut = dr.Item("time_out").ToString
                        getDate = dr.Item("date")
                    End If
                End Using
            End Using
    End Using

我猜到了数据类型。检查您的数据库以获取正确的类型。您似乎将数据值存储为字符串。日期应该真正存储为日期。

Using 语句可确保您的对象正确关闭和处置，即使出现错误也是如此。

如果不知道 TimeIn，我真的不能再进一步了。

推荐阅读