VB.NET中SQL Server For Each语句未按预期工作，部门打印异常

数据库表说明 #

表中包含部门1和部门2，需求是为每个部门打印单独的凭证。

获取唯一部门的代码 #

Public Sub token_print_()
openconnection1()
Try

    Dim searchQuery As String = "select distinct department as 'department' from tb_transactions where invoice_id='" & TextBox1.Text & "'"
    Dim command As New SqlCommand(searchQuery, MYSQLCon)
    Dim adapter As New SqlDataAdapter(command)
    Dim table As New DataTable

    adapter.Fill(table)

    For Each row As DataRow In table.Rows
        For Each colu In table.Columns

            receipt_filldatagridview2(row(colu))

        Next

    Next

    command.Dispose()
Catch ex As Exception
    MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Receipt Fill Error 107")

 End Try 
End Sub

需求是读取每个唯一部门（如1和2），逐个填充DataGridView并打印凭证（将部门传入receipt_filldatagridview2(row(colu))），但当前For Each语句未按预期工作：DataGridView仅显示部门2的条目，跳过了部门1。

DataGridView填充代码 #

Public Sub receipt_filldatagridview2(ByVal dept As String)
openconnection1()
Try
    DataGridView_thermal.AutoGenerateColumns = False

    Dim searchQuery As String = "Select tb_transactions.product_name as 'product_name', cast(quantity as numeric(36,1)) as 'quantity', tb_transactions.rate as 'rate'  from tb_transactions  where tb_transactions.invoice_id = '" & TextBox1.Text & "' AND tb_transactions.department = '" & dept.ToString & "'"

    Dim command As New SqlCommand(searchQuery, MYSQLCon)
    Dim adapter As New SqlDataAdapter(command)
    Dim table As New DataTable

    adapter.Fill(table)
    DataGridView_thermal.DataSource = table

    BTPRINT.PerformClick()


    table.Dispose()
    adapter.Dispose()
    command.Dispose()
Catch ex As Exception
    MsgBox(ex.Message, "Receipt Fill Error 107")

    End Try
End Sub

发票打印相关代码 #

打印触发与纸张设置 #

Private Sub BTPRINT_Click(sender As Object, e As EventArgs) Handles BTPRINT.Click
        changelongpaper()
        PPD.Document = PD
        PD.PrinterSettings.PrinterName = "Black Copper BC-85AC"
        PD.Print()  'Direct Print
    End Sub

 Sub changelongpaper()
      Dim rowcount As Integer
      longpaper = 0
      rowcount = DataGridView_thermal.Rows.Count
      longpaper = rowcount * 15
      longpaper = longpaper + 240
  End Sub

  Private Sub PD_BeginPrint(sender As Object, e As PrintEventArgs) Handles PD.BeginPrint
      Dim pagesetup As New PageSettings
      pagesetup.PaperSize = New PaperSize("Custom", 285, 500) 'fixed size
      'pagesetup.PaperSize = New PaperSize("Custom", 250, longpaper)
      PD.DefaultPageSettings = pagesetup
  End Sub

发票打印内容设计 #

Private Sub PD_PrintPage(sender As Object, e As PrintPageEventArgs) Handles PD.PrintPage
    Dim f8 As New Font("Calibri", 10, FontStyle.Regular)
    Dim f10 As New Font("Calibri", 10, FontStyle.Regular)
    Dim f10b As New Font("Calibri", 10, FontStyle.Bold)
    Dim f10c As New Font("Calibri", 14, FontStyle.Bold)
    Dim f14 As New Font("Calibri", 14, FontStyle.Bold)
    Dim f9 As New Font("Calibri", 10, FontStyle.Bold)

    Dim leftmargin As Integer = PD.DefaultPageSettings.Margins.Left
    Dim centermargin As Integer = PD.DefaultPageSettings.PaperSize.Width / 2
    Dim rightmargin As Integer = PD.DefaultPageSettings.PaperSize.Width

    'font alignment
    Dim right As New StringFormat
    Dim center As New StringFormat

    right.Alignment = StringAlignment.Far
    center.Alignment = StringAlignment.Center

    Dim line As String
    line = "****************************************************************"

    'range from top
    'logo
    Dim logoImage As Image = My.Resources.ResourceManager.GetObject("logo")
    e.Graphics.DrawImage(logoImage, CInt((e.PageBounds.Width - 150) / 2), 5, 150, 35)

    e.Graphics.DrawString("New York Street 15 Avenue", f10, Brushes.Black, centermargin, 40, center)
    e.Graphics.DrawString("Tel +1763545473", f10, Brushes.Black, centermargin, 55, center)


    e.Graphics.DrawString("Item", f9, Brushes.Black, 0, 110)
    e.Graphics.DrawString("Qty", f9, Brushes.Black, 150, 110)
    e.Graphics.DrawString("Price", f9, Brushes.Black, 220, 110, right)
    e.Graphics.DrawString("Total", f9, Brushes.Black, rightmargin, 110, right)
    '
    e.Graphics.DrawString(line, f8, Brushes.Black, 0, 123)

    Dim height As Integer 'DGV Position
    Dim i As Long
    DataGridView_thermal.AllowUserToAddRows = False

    For row As Integer = 0 To DataGridView_thermal.RowCount - 1
        height += 15

        e.Graphics.DrawString(DataGridView_thermal.Rows(row).Cells(0).Value.ToString, f8, Brushes.Black, 0, 117 + height)
        e.Graphics.DrawString(DataGridView_thermal.Rows(row).Cells(1).Value.ToString, f8, Brushes.Black, 152, 117 + height)
        i = DataGridView_thermal.Rows(row).Cells(2).Value
        DataGridView_thermal.Rows(row).Cells(2).Value = Format(i, "##,##0")
        e.Graphics.DrawString(DataGridView_thermal.Rows(row).Cells(2).Value.ToString, f8, Brushes.Black, 220, 117 + height, right)

        Dim totalprice As Long
        totalprice = Val(DataGridView_thermal.Rows(row).Cells(1).Value * DataGridView_thermal.Rows(row).Cells(2).Value)
        e.Graphics.DrawString(totalprice.ToString("##,##0"), f8, Brushes.Black, rightmargin, 117 + height, right)
        '
    Next

    e.Graphics.DrawString("~ Thanks for shopping ~", f10, Brushes.Black, centermargin, 80 + height, center)
    e.Graphics.DrawString("~ Nosware Store ~", f10, Brushes.Black, centermargin, 95 + height, center)

End Sub

问题分析与修复方案 #

核心原因 #

PD.Print()是异步操作，循环调用receipt_filldatagridview2时，第一个部门的打印任务还未完成，DataGridView的数据源就被第二个部门的数据覆盖，导致最终仅打印最后一个部门的内容。

修复步骤 #

1. 同步打印，等待任务完成
   移除BTPRINT.PerformClick()，直接在receipt_filldatagridview2中执行同步打印逻辑，确保上一个部门打印完成后再处理下一个：

   Public Sub receipt_filldatagridview2(ByVal dept As String)
   openconnection1()
   Try
       DataGridView_thermal.AutoGenerateColumns = False
   
       Dim searchQuery As String = "Select tb_transactions.product_name as 'product_name', cast(quantity as numeric(36,1)) as 'quantity', tb_transactions.rate as 'rate'  from tb_transactions  where tb_transactions.invoice_id = '" & TextBox1.Text & "' AND tb_transactions.department = '" & dept.ToString & "'"
   
       Dim command As New SqlCommand(searchQuery, MYSQLCon)
       Dim adapter As New SqlDataAdapter(command)
       Dim table As New DataTable
   
       adapter.Fill(table)
       DataGridView_thermal.DataSource = table
   
       ' 同步执行打印，等待完成
       changelongpaper()
       PPD.Document = PD
       PD.PrinterSettings.PrinterName = "Black Copper BC-85AC"
       PD.Print()
   
       table.Dispose()
       adapter.Dispose()
       command.Dispose()
   Catch ex As Exception
       MsgBox(ex.Message, "Receipt Fill Error 107")
   
       End Try
   End Sub
   

2. 简化循环逻辑
   原代码中嵌套列遍历（当前仅一列），可直接提取部门值，优化代码可读性：

   For Each row As DataRow In table.Rows
       Dim dept As String = row("department").ToString()
       receipt_filldatagridview2(dept)
   Next
   

3. 替换为参数化查询防注入
   当前SQL拼接存在注入风险，修改为参数化查询：

   ' 在token_print_中修改查询
   Dim searchQuery As String = "select distinct department as 'department' from tb_transactions where invoice_id = @InvoiceId"
   Dim command As New SqlCommand(searchQuery, MYSQLCon)
   command.Parameters.AddWithValue("@InvoiceId", TextBox1.Text)
   
   ' 在receipt_filldatagridview2中修改查询
   Dim searchQuery As String = "Select product_name, cast(quantity as numeric(36,1)) as quantity, rate from tb_transactions where invoice_id = @InvoiceId AND department = @Dept"
   Dim command As New SqlCommand(searchQuery, MYSQLCon)
   command.Parameters.AddWithValue("@InvoiceId", TextBox1.Text)
   command.Parameters.AddWithValue("@Dept", dept)
   

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