VB.NET中Access查询返回异常：两窗体输出互相覆盖问题求助

问题描述 #

开发了两个Windows窗体：

• 销售窗体：查询指定日期范围内的所有销售数据
• 待付款窗体：按客户名称分组查询所有待付款数据

异常现象：

• 先打开销售窗体，它可正常工作，但后续打开待付款窗体时，返回的是销售窗体的查询结果（尽管SQL语句完全不同）
• 先打开待付款窗体，它能正常运行，但后续打开销售窗体时，返回的是待付款窗体的查询结果

销售窗体代码 #

Public Sub call_data(ByVal str As String)
    'Try
        Dim totalsales As Integer
        Dim totalincome As Integer
        DataGridView1.DataSource = Nothing
        DataGridView1.Rows.Clear()
        DataGridView1.Refresh()

        ds.Clear()
        cmd = New OleDb.OleDbCommand(str, cn)
        da.SelectCommand = cmd
    da.Fill(ds, "invoice")
    DataGridView1.Rows.Clear()
    For i = 0 To ds.Tables("invoice").Rows.Count - 1
            DataGridView1.Rows.Add(ds.Tables("invoice").Rows(i)(2).ToString(), ds.Tables("invoice").Rows(i)(1).ToString(), ds.Tables("invoice").Rows(i)(0).ToString(), Convert.ToInt64(ds.Tables("invoice").Rows(i)(8).ToString()) + Convert.ToInt64(ds.Tables("invoice").Rows(i)(9).ToString()) + Convert.ToInt64(ds.Tables("invoice").Rows(i)(10).ToString()), Convert.ToInt64(ds.Tables("invoice").Rows(i)(11).ToString()))
            totalsales += Convert.ToInt64(ds.Tables("invoice").Rows(i)(8).ToString()) + Convert.ToInt64(ds.Tables("invoice").Rows(i)(9).ToString()) + Convert.ToInt64(ds.Tables("invoice").Rows(i)(10).ToString())
            totalincome += Convert.ToInt64(ds.Tables("invoice").Rows(i)(11).ToString())
        Next
        Label2.Text = totalsales
        Label5.Text = totalincome
        Label7.Text = totalsales - totalincome

    ' Catch ex As Exception

   ' End Try
End Sub

Private Sub add_Click(sender As Object, e As EventArgs) Handles add.Click
    call_data("SELECT * from [invoice] Where [invoice_date] Between #" & from_date.Value.ToString("MM/dd/yyyy") & "# And #" & To_Date.Value.ToString("MM/dd/yyyy") & "#")
End Sub

待付款窗体代码 #

Public Sub call_data(ByVal str As String)
    Try
        DataGridView1.DataSource = Nothing
        DataGridView1.Rows.Clear()
        DataGridView1.Refresh()
        Dim totalpurchase As Integer
        Dim totalpaid As Integer
        Dim totalpending As Integer
        ds.Clear()
        cmd = New OleDb.OleDbCommand(str, cn)
        cmd.Parameters.Clear()
        da.SelectCommand = cmd
        da.Fill(ds, "invoice")
        DataGridView1.Rows.Clear()
        For i = 0 To ds.Tables("invoice").Rows.Count - 1
            totalpurchase = Convert.ToInt64(ds.Tables("invoice").Rows(i)(1).ToString()) + Convert.ToInt64(ds.Tables("invoice").Rows(i)(2).ToString()) + Convert.ToInt64(ds.Tables("invoice").Rows(i)(3).ToString())
            totalpaid = Convert.ToInt64(ds.Tables("invoice").Rows(i)(4).ToString())
            DataGridView1.Rows.Add(ds.Tables("invoice").Rows(i)(0).ToString(), totalpurchase, ds.Tables("invoice").Rows(i)(4).ToString(), totalpurchase - totalpaid)
        Next
    Catch ex As Exception
    End Try
End Sub

Private Sub add_Click(sender As Object, e As EventArgs) Handles add.Click
    call_data("SELECT client_name, Sum(invoice.freight_rate) AS SumOffreight_rate, Sum(invoice.total_basic_amount) AS SumOftotal_basic_amount, Sum(invoice.delivery_rate) AS SumOfdelivery_rate, Sum(invoice.advanced_amount) AS SumOfadvanced_amount FROM invoice GROUP BY invoice.client_name")
End Sub

问题根源 #

核心原因是两个窗体共享了全局的数据库对象（ds、cmd、da）。当第一个窗体初始化这些对象并执行查询后，第二个窗体复用同一个DataSet、DataAdapter和OleDbCommand，新的查询会直接覆盖原有对象中的数据，甚至因为两次查询的结果集结构不同，导致数据读取时出现错位。

解决方案：每个窗体使用独立的数据库对象 #

不要使用全局共享的数据库操作对象，而是在每个窗体的call_data方法内部创建局部对象，确保两个窗体的查询操作完全隔离。

修改后的销售窗体代码 #

Public Sub call_data(ByVal str As String)
    Dim totalsales As Integer
    Dim totalincome As Integer
    DataGridView1.DataSource = Nothing
    DataGridView1.Rows.Clear()
    DataGridView1.Refresh()

    ' 使用局部对象，自动释放资源
    Using ds As New DataSet()
        Using cmd As New OleDb.OleDbCommand(str, cn)
            Using da As New OleDb.OleDbDataAdapter(cmd)
                da.Fill(ds, "invoice")
                DataGridView1.Rows.Clear()
                For i = 0 To ds.Tables("invoice").Rows.Count - 1
                    Dim col8 = Convert.ToInt64(ds.Tables("invoice").Rows(i)(8).ToString())
                    Dim col9 = Convert.ToInt64(ds.Tables("invoice").Rows(i)(9).ToString())
                    Dim col10 = Convert.ToInt64(ds.Tables("invoice").Rows(i)(10).ToString())
                    Dim col11 = Convert.ToInt64(ds.Tables("invoice").Rows(i)(11).ToString())
                    
                    DataGridView1.Rows.Add(
                        ds.Tables("invoice").Rows(i)(2).ToString(),
                        ds.Tables("invoice").Rows(i)(1).ToString(),
                        ds.Tables("invoice").Rows(i)(0).ToString(),
                        col8 + col9 + col10,
                        col11
                    )
                    totalsales += col8 + col9 + col10
                    totalincome += col11
                Next
                Label2.Text = totalsales.ToString()
                Label5.Text = totalincome.ToString()
                Label7.Text = (totalsales - totalincome).ToString()
            End Using
        End Using
    End Using
End Sub

Private Sub add_Click(sender As Object, e As EventArgs) Handles add.Click
    Dim sql = $"SELECT * from [invoice] Where [invoice_date] Between #{from_date.Value.ToString("MM/dd/yyyy")}# And #{To_Date.Value.ToString("MM/dd/yyyy")}#"
    call_data(sql)
End Sub

修改后的待付款窗体代码 #

Public Sub call_data(ByVal str As String)
    Try
        DataGridView1.DataSource = Nothing
        DataGridView1.Rows.Clear()
        DataGridView1.Refresh()

        ' 使用局部对象，自动释放资源
        Using ds As New DataSet()
            Using cmd As New OleDb.OleDbCommand(str, cn)
                Using da As New OleDb.OleDbDataAdapter(cmd)
                    da.Fill(ds, "invoice")
                    DataGridView1.Rows.Clear()
                    For i = 0 To ds.Tables("invoice").Rows.Count - 1
                        Dim col1 = Convert.ToInt64(ds.Tables("invoice").Rows(i)(1).ToString())
                        Dim col2 = Convert.ToInt64(ds.Tables("invoice").Rows(i)(2).ToString())
                        Dim col3 = Convert.ToInt64(ds.Tables("invoice").Rows(i)(3).ToString())
                        Dim col4 = Convert.ToInt64(ds.Tables("invoice").Rows(i)(4).ToString())
                        
                        Dim totalpurchase = col1 + col2 + col3
                        DataGridView1.Rows.Add(
                            ds.Tables("invoice").Rows(i)(0).ToString(),
                            totalpurchase,
                            col4.ToString(),
                            totalpurchase - col4
                        )
                    Next
                End Using
            End Using
        End Using
    Catch ex As Exception
        ' 添加异常提示，方便调试
        MessageBox.Show($"查询出错：{ex.Message}")
    End Try
End Sub

Private Sub add_Click(sender As Object, e As EventArgs) Handles add.Click
    Dim sql = "SELECT client_name, Sum(invoice.freight_rate) AS SumOffreight_rate, Sum(invoice.total_basic_amount) AS SumOftotal_basic_amount, Sum(invoice.delivery_rate) AS SumOfdelivery_rate, Sum(invoice.advanced_amount) AS SumOfadvanced_amount FROM invoice GROUP BY invoice.client_name"
    call_data(sql)
End Sub

额外优化建议 #

1. 避免SQL注入：当前代码直接拼接日期到SQL语句，存在安全风险。建议改用参数化查询：

   ' 销售窗体add_Click修改为参数化方式
   Private Sub add_Click(sender As Object, e As EventArgs) Handles add.Click
       Dim sql = "SELECT * from [invoice] Where [invoice_date] Between @FromDate And @ToDate"
       Using cmd As New OleDb.OleDbCommand(sql, cn)
           cmd.Parameters.AddWithValue("@FromDate", from_date.Value)
           cmd.Parameters.AddWithValue("@ToDate", To_Date.Value)
           ' 可修改call_data方法直接接收OleDbCommand，进一步提升安全性
           call_data(cmd.CommandText)
       End Using
   End Sub
   

2. 保留异常日志：不要空捕获异常，至少记录错误信息，便于后期调试排查
3. 使用Using语句：确保数据库对象自动释放资源，避免内存泄漏

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