VB.NET中如何通过ComboBox事件实现DataGridView字段计算

此前我使用bunifuDataGridView1_CellEndEdit事件实现了DataGridView的列计算功能，现在希望改用ComboBox1_SelectionChangeCommitted事件完成DataGridView的字段计算，但不清楚具体实现方法，现有代码如下，恳请提供解决方案：

界面说明：界面包含下拉选择框ComboBox1、DataGridView（列包含NOD、ITM、CIU、QTY、Total）、现金输入框txtCash、总计标签lblTotal、找零标签lblChange。

Private Sub bunifuDataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles bunifuDataGridView1.CellEndEdit
    'calcutalte
    If e.ColumnIndex = 2 OrElse e.ColumnIndex = 3 Then
        Dim row = bunifuDataGridView1.Rows(e.RowIndex)
        row.Cells(4).Value = Double.Parse(row.Cells(2).Value.ToString()) * Double.Parse(row.Cells(3).Value.ToString())
        Calculate()
    End If
End Sub

Private Sub Calculate()
    Dim tot As Double = 0
    Dim cash As Double = Double.Parse(txtCash.Text)
    For Each item As DataGridViewRow In bunifuDataGridView1.Rows
        tot += Double.Parse(item.Cells(4).Value.ToString())
    Next item

    lblTotal.Text = tot.ToString("N2")
    lblChange.Text = (cash - tot).ToString("N2")
End Sub

Private Sub ComboBox1_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectionChangeCommitted
    fillDataGridView1()
End Sub

Private Sub fillDataGridView1()
    Try
        Dim query As String = "SELECT NOD,ITM,CIU,QTY FROM RPD WHERE QTY > 0 AND PNM=@PNM"
        Using con As OleDbConnection = New OleDbConnection(cn)
            Using cmd As OleDbCommand = New OleDbCommand(query, con)
                cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)
                Using da As New OleDbDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable()
                    da.Fill(dt)
                    da.Dispose()
                    source1.DataSource = dt
                    Me.bunifuDataGridView1.DataSource = source1
                    dt.Columns("Total").Expression = "[CIU]*[QTY]"          
                    Me.bunifuDataGridView1.Refresh()
                End Using
            End Using
        End Using
    Catch ex As Exception
    End Try
End Sub

Private Sub PopulateComboBox()
    Dim query As String = "SELECT DISTINCT PNM FROM RPD "
    Try
        Using con As OleDbConnection = New OleDbConnection(cn)
            Using sda As OleDbDataAdapter = New OleDbDataAdapter(query, con)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)

                Dim row As DataRow = dt.NewRow()
                row(0) = ""
                dt.Rows.InsertAt(row, 0)

                ComboBox1.DataSource = dt
                ComboBox1.DisplayMember = "PNM"
                ComboBox1.ValueMember = "PNM"
            End Using
        End Using
    Catch myerror As OleDbException
        MessageBox.Show("Error: " & myerror.Message)
    Finally
    End Try
End Sub

核心修改点是在fillDataGridView1方法中正确创建计算列，并在数据填充完成后触发总计统计，确保ComboBox切换选择后自动完成所有计算逻辑：

1. 避免重复添加Total列：填充DataTable后先检查是否存在Total列，不存在再创建并设置计算表达式，防止报错；
2. 自动触发总计统计：在ComboBox选择事件中，数据加载完成后调用Calculate()方法，更新总计和找零标签。

修改后的关键代码如下：

Private Sub ComboBox1_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectionChangeCommitted
    fillDataGridView1()
    ' 现金输入框有值时，自动更新总计和找零
    If Not String.IsNullOrEmpty(txtCash.Text) Then
        Calculate()
    End If
End Sub

Private Sub fillDataGridView1()
    Try
        Dim query As String = "SELECT NOD,ITM,CIU,QTY FROM RPD WHERE QTY > 0 AND PNM=@PNM"
        Using con As OleDbConnection = New OleDbConnection(cn)
            Using cmd As OleDbCommand = New OleDbCommand(query, con)
                cmd.Parameters.AddWithValue("@PNM", ComboBox1.SelectedValue)
                Using da As New OleDbDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable()
                    da.Fill(dt)
                    da.Dispose()

                    ' 检查并添加Total计算列
                    If Not dt.Columns.Contains("Total") Then
                        Dim totalCol As New DataColumn("Total", GetType(Double))
                        totalCol.Expression = "[CIU] * [QTY]"
                        dt.Columns.Add(totalCol)
                    End If

                    source1.DataSource = dt
                    Me.bunifuDataGridView1.DataSource = source1
                    Me.bunifuDataGridView1.Refresh()
                End Using
            End Using
        End Using
    Catch ex As Exception
        MessageBox.Show("数据加载错误：" & ex.Message)
    End Try
End Sub

额外说明 #

• 保留原bunifuDataGridView1_CellEndEdit事件，用户手动修改CIU或QTY列时仍能实时计算该行Total并更新总和；
• 若txtCash为空，切换ComboBox后不会更新找零，避免空值转换报错，可根据需求调整判断逻辑；
• 添加了异常提示，便于排查数据加载过程中的问题。

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