薪资表字段求和生成RDLC报表时PAYTEMP数据异常问题

核心问题点 #

1. 遍历全部员工记录：外层循环遍历了STAFFDATA的225条记录，无论员工是否有对应年度的薪资数据，都会向PAYTEMP插入记录，导致生成225条数据而非预期的10条。
2. DataSet重复填充未清空：内层循环每次调用adapter.Fill(ds, "PAYROLL")时，未清空ds中已有的PAYROLL表数据，导致后续循环中数据不断累加，统计时重复计算历史数据。
3. 变量大小写混用：代码中Rent1和RENT1大小写混用（VB.NET不区分大小写，但易引发逻辑混乱）。
4. SQL拼接存在安全风险：直接将用户输入txtYear.Text拼接到SQL语句中，存在SQL注入隐患。
5. Reliefs统计逻辑错误：Reliefs1 = Reliefs直接覆盖值而非累加（若RELIEFS为月度数据则需累加，需根据业务调整）。

修正后的代码 #

Try
    Dim MonthlyBasic1 As Double
    Dim Rent1 As Double
    Dim Transport1 As Double
    Dim OtherA1 As Double
    Dim Reliefs1 As Double
    Dim PAYE1 As Double

    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0; data source=C:\ECTSPAYROLL\ECTSPAY.accdb"
    Using conDb As New OleDb.OleDbConnection(connString)
        conDb.Open()

        ' 清空临时表
        Using cmd As New OleDb.OleDbCommand("DELETE * FROM PAYTEMP", conDb)
            cmd.ExecuteNonQuery()
        End Using

        ' 仅查询有对应年度薪资数据的员工
        Dim sqlStaff As String = "SELECT DISTINCT s.STAFFID, s.SURNAME, s.OTHERNAMES 
                                 FROM STAFFDATA s 
                                 INNER JOIN PAYROLL p ON s.STAFFID = p.STAFFID 
                                 WHERE p.SALARYYEAR = ?"
        Using cmdStaff As New OleDb.OleDbCommand(sqlStaff, conDb)
            cmdStaff.Parameters.AddWithValue("@Year", txtYear.Text)
            Using adapter1 As New OleDb.OleDbDataAdapter(cmdStaff)
                Dim dsStaff As New DataSet()
                adapter1.Fill(dsStaff, "STAFFDATA")

                ' 预加载所有年度薪资数据，避免循环内重复查询
                Dim sqlPayroll As String = "SELECT * FROM PAYROLL WHERE SALARYYEAR = ?"
                Using cmdPayroll As New OleDb.OleDbCommand(sqlPayroll, conDb)
                    cmdPayroll.Parameters.AddWithValue("@Year", txtYear.Text)
                    Using adapterPayroll As New OleDb.OleDbDataAdapter(cmdPayroll)
                        Dim dsPayroll As New DataSet()
                        adapterPayroll.Fill(dsPayroll, "PAYROLL")

                        For Each staffRow As DataRow In dsStaff.Tables("STAFFDATA").Rows
                            Dim staffID As String = staffRow("STAFFID").ToString().ToLower()
                            Dim surname As String = staffRow("SURNAME").ToString()
                            Dim otherNames As String = staffRow("OTHERNAMES").ToString()

                            ' 重置统计变量
                            MonthlyBasic1 = 0
                            Rent1 = 0
                            Transport1 = 0
                            OtherA1 = 0
                            Reliefs1 = 0
                            PAYE1 = 0
                            Dim department As String = ""
                            Dim tinNo As String = ""
                            Dim staffName As String = ""

                            ' 筛选当前员工的薪资记录并统计
                            For Each payRow As DataRow In dsPayroll.Tables("PAYROLL").Rows
                                If payRow("STAFFID").ToString().ToLower() = staffID Then
                                    surname = payRow("SURNAME").ToString()
                                    otherNames = payRow("OTHERNAMES").ToString()
                                    department = payRow("DEPARTMENT").ToString()
                                    tinNo = payRow("TINNO").ToString()

                                    MonthlyBasic1 += Convert.ToDouble(payRow("BASICPM"))
                                    Rent1 += Convert.ToDouble(payRow("RENT"))
                                    Transport1 += Convert.ToDouble(payRow("TRANSPORT"))
                                    OtherA1 += Convert.ToDouble(payRow("OTHERA"))
                                    Reliefs1 += Convert.ToDouble(payRow("RELIEFS")) ' 改为累加，若业务为年度固定值可调整
                                    PAYE1 += Convert.ToDouble(payRow("PAYE"))
                                End If
                            Next

                            staffName = $"{surname}, {otherNames}"
                            Dim deductionHeading As String = $"ANNUAL TAX RETURNS FOR YEAR {txtYear.Text}"

                            ' 参数化插入，避免SQL注入
                            Dim sqlInsert As String = "INSERT INTO PAYTEMP (HEADINGS, STAFFID, SURNAME, DEPARTMENT, TINNO, BASICPM, RENT, TRANSPORT, OTHERA, RELIEFS, PAYE) 
                                                      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                            Using cmdInsert As New OleDb.OleDbCommand(sqlInsert, conDb)
                                cmdInsert.Parameters.AddWithValue("@Headings", deductionHeading)
                                cmdInsert.Parameters.AddWithValue("@StaffID", staffRow("STAFFID"))
                                cmdInsert.Parameters.AddWithValue("@Surname", staffName)
                                cmdInsert.Parameters.AddWithValue("@Department", department)
                                cmdInsert.Parameters.AddWithValue("@TINNo", tinNo)
                                cmdInsert.Parameters.AddWithValue("@BASICPM", MonthlyBasic1)
                                cmdInsert.Parameters.AddWithValue("@RENT", Rent1)
                                cmdInsert.Parameters.AddWithValue("@TRANSPORT", Transport1)
                                cmdInsert.Parameters.AddWithValue("@OTHERA", OtherA1)
                                cmdInsert.Parameters.AddWithValue("@RELIEFS", Reliefs1)
                                cmdInsert.Parameters.AddWithValue("@PAYE", PAYE1)

                                cmdInsert.ExecuteNonQuery()
                            End Using
                        Next
                    End Using
                End Using
            End Using
        End Using
    End Using
Catch ex As Exception
    MsgBox(ex.Message.ToString())
End Try

关键改进说明 #

• 缩小员工范围：通过INNER JOIN仅查询有对应年度薪资数据的员工，确保生成10条目标记录。
• 预加载薪资数据：提前加载所有年度薪资数据到dsPayroll，避免循环内重复查询数据库，提升执行效率。
• 重置统计变量：每次处理新员工时重置所有统计变量，避免累加错误。
• 参数化查询：所有SQL操作使用参数化，杜绝SQL注入风险，同时避免字符串拼接的格式问题。
• 统一变量命名：修正大小写不一致的变量，提升代码可读性。
• Using语句管理资源：用Using自动管理数据库连接、命令和适配器的资源，避免资源泄漏。

内容的提问来源于stack exchange，提问作者Samuel Chukwudum Ezeokonkwo