VB.NET员工考勤系统无法向MySQL tbl_attendance表插入打卡数据求助

问题概述 #

使用VB.NET开发Windows窗体版员工考勤管理系统，MySQL作为数据库，已实现向tbl_employee表插入员工数据，但无法向tbl_attendance表录入打卡记录，经排查问题出在Button1_Click事件第18行的createlog调用处。

相关代码 #

Button1_Click事件代码 #

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            If txtEmployeeID.Text = "" Then
                MessageBox.Show("Please enter Employee ID", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Else
                reloadtext("SELECT * FROM tbl_employees WHERE EMPLOYEEID='" & txtEmployeeID.Text & "'")
                If dt.Rows.Count > 0 Then
                    reloadtext("SELECT * FROM tbl_attendance WHERE EMPLOYEEID='" & txtEmployeeID.Text & "' AND LOGDATE='" & lblDate.Text & "' AND AM_STATUS='Time In' AND PM_STATUS='Time Out'")
                    If dt.Rows.Count > 0 Then
                        MessageBox.Show("You already have an attendance for today", "Reminder", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Else
                        reloadtext("SELECT * FROM tbl_attendance WHERE EMPLOYEEID ='" & txtEmployeeID.Text & "' AND LOGDATE='" & lblDate.Text & "' AND AM_STATUS ='Time In'")
                        If dt.Rows.Count > 0 Then
                            updatelog("UPDATE tbl_attendance SET TIMEOUT='" & TimeOfDay & "', PM_STATUS='Time Out' WHERE EMPLOYEEID='" & txtEmployeeID.Text & "' AND LOGDATE='" & lblDate.Text & "'")
                            MessageBox.Show("Successfully Timed out", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        Else
                            createlog("INSERT INTO tbl_attendance(EMPLOYEEID,LOGDATE,TIMEIN,AM_STATUS)VALUES('" & txtEmployeeID.Text & "','" & lblDate.Text & "','" & TimeOfDay & "','Time In')")
                            MessageBox.Show("Successfully Timed in", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        End If
                    End If
                Else
                    MessageBox.Show("Employee ID not found", "Not found", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                End If
            End If
        Catch ex As Exception
        End Try
    End Sub

CRUDConnection模块代码 #

Imports MySql.Data.MySqlClient
Module CRUDConnection
    Public result As String
    Public cmd As New MySqlCommand
    Public da As New MySqlDataAdapter
    Public dt As New DataTable
    Public ds As New DataSet
    Public Sub create(ByVal sql As String)
        Try
            conn.Open()
            With cmd
                .Connection = conn
                .CommandText = sql
                result = cmd.ExecuteNonQuery
                If result = 0 Then
                    MessageBox.Show("Data failed to insert.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                Else
                    MessageBox.Show("Data successfully inserted.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
            End With
        Catch ex As Exception
        Finally
            conn.Close()
        End Try
    End Sub
    Public Sub reload(ByVal sql As String, ByVal DTG As Object)
        Try
            conn.Open()
            dt = New DataTable
            With cmd
                .Connection = conn
                .CommandText = sql
            End With
            da.SelectCommand = cmd
            da.Fill(dt)
            DTG.DataSource = dt
        Catch ex As Exception
        Finally
            conn.Close()
            da.Dispose()
        End Try
    End Sub
    Public Sub reloadtext(ByVal sql As String)
        Try
            conn.Open()
            With cmd
                .Connection = conn
                .CommandText = sql
            End With
            dt = New DataTable
            da = New MySqlDataAdapter(sql, conn)
            da.Fill(dt)
        Catch ex As Exception
        Finally
            conn.Close()
            da.Dispose()
        End Try
    End Sub
    Public Sub createlog(ByVal sql As String)
        Try
            conn.Open()
            With cmd
                .Connection = conn
                .CommandText = sql
                result = cmd.ExecuteNonQuery
            End With
        Catch ex As Exception
        Finally
            conn.Close()
        End Try
    End Sub
    Public Sub updatelog(ByVal sql As String)
        Try
            conn.Open()
            With cmd
                .Connection = conn
                .CommandText = sql
                result = cmd.ExecuteNonQuery
            End With
        Catch ex As Exception
        Finally
            conn.Close()
        End Try
    End Sub
End Module

数据库结构说明 #

tbl_attendance表包含字段：EMPLOYEEID（员工ID）、LOGDATE（打卡日期）、TIMEIN（上班时间）、TIMEOUT（下班时间）、AM_STATUS（上午状态）、PM_STATUS（下午状态）。

问题排查与修复建议 #

1. 类型不匹配错误 #

模块中result被定义为String，但ExecuteNonQuery()返回的是Integer类型，隐式转换会导致执行异常，修改为：

Public result As Integer

2. 无错误反馈 #

createlog和updatelog的Catch块为空，执行出错时无法得知具体原因，添加错误提示：

Catch ex As Exception
    MessageBox.Show($"执行出错：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)

3. 全局Command对象复用风险 #

全局的cmd对象被多个方法复用，可能导致命令状态残留，建议在每个方法内部创建独立的MySqlCommand对象，例如修改createlog：

Public Sub createlog(ByVal sql As String)
    Using cmd As New MySqlCommand(sql, conn)
        Try
            conn.Open()
            result = cmd.ExecuteNonQuery
        Catch ex As Exception
            MessageBox.Show($"执行出错：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
        End Try
    End Using
End Sub

4. SQL字符串拼接的问题 #

字符串拼接不仅有SQL注入风险，还可能因日期时间格式、特殊字符导致语法错误，改用参数化查询，例如修改打卡插入逻辑：

' 替换原createlog调用
Dim insertSql As String = "INSERT INTO tbl_attendance(EMPLOYEEID,LOGDATE,TIMEIN,AM_STATUS)VALUES(@empId, @logDate, @timeIn, @amStatus)"
Using cmd As New MySqlCommand(insertSql, conn)
    cmd.Parameters.AddWithValue("@empId", txtEmployeeID.Text)
    cmd.Parameters.AddWithValue("@logDate", DateTime.Parse(lblDate.Text)) ' 确保日期格式正确
    cmd.Parameters.AddWithValue("@timeIn", DateTime.Now.TimeOfDay)
    cmd.Parameters.AddWithValue("@amStatus", "Time In")
    Try
        conn.Open()
        Dim rowsAffected As Integer = cmd.ExecuteNonQuery
        If rowsAffected > 0 Then
            MessageBox.Show("Successfully Timed in", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            MessageBox.Show("打卡记录插入失败", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
    Catch ex As Exception
        MessageBox.Show($"执行出错：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Finally
        conn.Close()
    End Try
End Using

5. 日期时间格式问题 #

确保lblDate.Text的格式与MySQL的DATE类型兼容（推荐yyyy-MM-dd），或直接传递DateTime类型参数，避免字符串格式不匹配导致的插入/查询失败。

内容的提问来源于stack exchange，提问作者Newbster