Visual Studio中Crystal Report数据重复显示问题求助

在Visual Studio中使用Crystal Report时遇到数据重复显示的问题，相关代码及截图如下：

相关代码 #

Public Class AutoReportForm
    Dim con1 = Form1.con1
    Dim cmd1, cmd2 As OleDbCommand
    Dim adp1, adp2 As OleDbDataAdapter
    Dim dtb1, dtb2, dtb3, dtb4, dtb5 As New DataTable
    Dim myrpt As New ReportDocument
    Dim CrFormatTypeOptions As New PdfRtfWordFormatOptions()
    Dim txt As String = Form1.txt
    Dim ds As New DataSet()

    Sub viewdata()
        con1.open()
        Dim query As String = "Select * from AUTO_AND_RECIPE where AUTO_BATCH_UNIQUE_CODE = '" + txt + "'"
        cmd2 = New OleDbCommand(query, con1)
        adp2 = New OleDbDataAdapter(cmd2)
        adp2.Fill(dtb2)

        cmd1 = New OleDbCommand("Select * from PROCESS where AUTO_BATCH_UNIQUE_CODE_1 = '" + txt + "'", con1)
        adp1 = New OleDbDataAdapter(cmd1)
        adp1.Fill(dtb1)
        con1.Close()
        con1.Dispose()
    End Sub

    Private Sub AutoReportForm_Load(sender As Object, e As EventArgs) Handles Me.Load
        dtb1.Clear()
        dtb2.Clear()
        myrpt.Load("F:\VB2022\SCADAReport\Debug\AutoRpt.rpt")
        'TextBox1.Text = txt
        viewdata()

        myrpt.Database.Tables("AUTO_AND_RECIPE").SetDataSource(dtb2)
        myrpt.Database.Tables("PROCESS").SetDataSource(dtb1)
        CrystalReportViewer1.ReportSource = Nothing
        CrystalReportViewer1.ReportSource = myrpt
    End Sub
End Class

相关截图 #

解决方案 #

• 检查报表表间关联：打开Crystal Report设计器，进入Database Expert，确认AUTO_AND_RECIPE与PROCESS的关联字段（AUTO_BATCH_UNIQUE_CODE和AUTO_BATCH_UNIQUE_CODE_1）是否正确绑定，关联类型需匹配数据的实际关系（一对一/一对多），错误的关联会导致笛卡尔积引发重复。
• 验证数据源原始数据：在viewdata方法中添加调试代码，输出dtb1.Rows.Count和dtb2.Rows.Count，或临时用DataGridView绑定两个DataTable，确认原始查询结果是否本身存在重复数据。
• 改用DataSet统一绑定数据源：将两个DataTable加入同一个DataSet并设置表间关系，再绑定给报表，避免单独设置表数据源导致的关联失效：

  ds.Tables.Add(dtb2)
  ds.Tables.Add(dtb1)
  '建立表间关联
  ds.Relations.Add("AutoProcessLink", 
                   ds.Tables("AUTO_AND_RECIPE").Columns("AUTO_BATCH_UNIQUE_CODE"), 
                   ds.Tables("PROCESS").Columns("AUTO_BATCH_UNIQUE_CODE_1"))
  myrpt.SetDataSource(ds)
  

• 调整报表布局分组：若主表字段放在细节区，子表多条数据会导致主表内容重复。将主表字段移至组头区，按AUTO_BATCH_UNIQUE_CODE设置分组，子表字段保留在细节区。
• 修复SQL注入风险：当前字符串拼接SQL存在安全隐患，改用参数化查询：

  Dim query As String = "Select * from AUTO_AND_RECIPE where AUTO_BATCH_UNIQUE_CODE = ?"
  cmd2 = New OleDbCommand(query, con1)
  cmd2.Parameters.AddWithValue("@BatchCode", txt)
  

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