VB.NET DataGridView多行数据保存报索引超出范围错误

• 开发场景：基于VB.NET实现库存入库功能，需将DataGridView控件录入的商品数量数据写入MySQL数据库
• 测试表现：单条行数据保存流程运行完全正常，录入多行数据触发保存操作时直接抛出运行时异常
• 报错定位：异常触发位置为@pid参数传递代码行：

  .Parameters.AddWithValue("@pid", CInt(DataGridView1.Rows(i).Cells(1).Value.ToString))
  

• 异常提示：保存多行GridView数据时，出现「索引超出范围。必须为非负值且小于集合大小」的错误

• 多行保存的循环逻辑For i As Integer = 0 To DataGridView1.Rows.Count - 1内部，在处理第一行数据时，代码直接执行了DataGridView1.Rows.Clear()清空整个DataGridView的行集合
• 循环迭代到第二行时，i值增长为1，但此时DataGridView的行集合已被清空，集合大小为0，访问Rows(i)时自然触发索引越界异常
• 单条数据保存时循环仅执行1次，不会出现清空后重复访问的逻辑，因此表现正常

Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
    TxtReferenceNo.Text.Trim()
    TxtReceiveBy.Text.Trim()

    For a As Integer = 0 To DataGridView1.Rows.Count - 1
        If DataGridView1.Rows(a).Cells(7).Value = String.Empty Then
            MsgBox("Quantity must not be empty", MsgBoxStyle.Critical, "")
            Exit Sub
        End If
    Next

    Dim sdate As String = DateTimeReceived.Value.ToString("yyyy-MM-dd")
    Dim d1, d2 As Date
    Dim found As Boolean
    d1 = sdate
    d2 = Now.ToShortDateString

    If d1 <> d2 Then
        MsgBox("Date should should be current date", MsgBoxStyle.Critical, "")
        Exit Sub
    End If

    For i As Integer = 0 To DataGridView1.Rows.Count - 1
        Dim Tsql As String
        Tsql = "select * from tblstockin where refno like '" & TxtReferenceNo.Text & "'"

        Con = New MySqlConnection
        With Con
            If .State = ConnectionState.Closed Then
                .ConnectionString = StrCon
                .Open()
            End If
        End With

        Comm = New MySqlCommand(Tsql, Con)
        dr = Comm.ExecuteReader
        dr.Read()
        If dr.HasRows Then
            found = True
        End If
        dr.Close()
        Con.Close()

        Try
            If Not (IsNumeric(DataGridView1.Rows(i).Cells(7).Value.ToString)) Then
                MsgBox("Quantity should be numeric", MsgBoxStyle.Critical, "")
                Exit Sub
            End If
        Catch ex As Exception

        End Try

        If found = False Then
            Dim sql As String
            sql = "insert into tblstockin (refno, receivedby, pid, qty, sdate) 
                  values (@refno, @receivedby, @pid, @qty, @sdate) "

            Con = New MySqlConnection
            With Con
                If .State = ConnectionState.Closed Then
                    .ConnectionString = StrCon
                    .Open()
                End If
            End With

            Comm = New MySqlCommand(sql, Con)
            With Comm
                .Parameters.Clear()
                .CommandText = sql
                .Parameters.AddWithValue("@refno", TxtReferenceNo.Text)
                .Parameters.AddWithValue("@receivedby", TxtReceiveBy.Text)
                .Parameters.AddWithValue("@pid", CInt(DataGridView1.Rows(i).Cells(1).Value.ToString))
                .Parameters.AddWithValue("@qty", CInt(DataGridView1.Rows(i).Cells(7).Value.ToString))
                .Parameters.AddWithValue("@sdate", sdate)
            End With

            If MessageBox.Show("Are you sure to save Stock", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                Dim result As Integer = Comm.ExecuteNonQuery
                MsgBox("Stock has been saved", MsgBoxStyle.Information, "")
                TxtReceiveBy.Clear()
                TxtCount.Clear()
                Con.Close()

                Dim hsql As String
                hsql = " insert into tblgenid (codeid, code) values (@codeid, @code) "

                Con = New MySqlConnection
                With Con
                    If .State = ConnectionState.Closed Then
                        .ConnectionString = StrCon
                        .Open()
                    End If
                End With

                Comm = New MySqlCommand(hsql, Con)
                With Comm
                    .Parameters.Clear()
                    .CommandText = hsql
                    .Parameters.AddWithValue("@codeid", LblD.Text)
                    .Parameters.AddWithValue("@code", TxtReferenceNo.Text)
                End With
                Comm.ExecuteNonQuery()
                Con.Close()
                TxtReferenceNo.Clear()
            End If

            Dim rsql As String
            rsql = "update tblproduct set qty = qty + " & CInt(DataGridView1.Rows(i).Cells(7).Value.ToString) &
                " where id like '" & DataGridView1.Rows(i).Cells(1).Value.ToString & "'"

            Con = New MySqlConnection
            With Con
                If .State = ConnectionState.Closed Then
                    .ConnectionString = StrCon
                    .Open()
                End If
            End With

            Comm = New MySqlCommand(rsql, Con)
            With Comm
                .Parameters.Clear()
                .CommandText = rsql
            End With
            Comm.ExecuteNonQuery()
            DataGridView2.Rows.Clear()
            DataGridView1.Rows.Clear()
            CbxSearch.ResetText()
            TxtSearch.Clear()
            TxtReferenceNo.Clear()
            Con.Close()
        Else
            MsgBox("Reference no already exists", MsgBoxStyle.Critical, "")
        End If
    Next

    GenerateStock()

    With Frm_Product_List
        .LoadProductList()
    End With
End Sub

• 将DataGridView1.Rows.Clear()、控件清空、页面刷新等逻辑，移到整个多行循环执行完成后再运行，避免循环过程中清空行集合导致后续迭代访问越界
• 将单号重复校验逻辑移到循环外部，仅校验一次当前录入的单据号是否存在即可，无需每一行重复查询数据库
• 补充found变量的初始化逻辑，每次执行数据库查询前重置状态，避免上一次循环的查询结果影响当前行的判断
• 优化数据库连接管理逻辑，不要在循环内反复创建、关闭连接，减少不必要的数据库开销
• 补全SQL参数化逻辑，将商品数量、商品id等拼接在SQL语句中的值改为参数传递，规避SQL注入风险
• 将保存确认弹窗逻辑移到循环外部，仅在所有行数据校验完成后弹出一次确认框，优化交互体验

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