vb.net - 无法从 datagridview 保存数据

Question

发生的事情是我想将数据量保存在 qty 列中，它适用于单行但是一旦你放置多行条目，它会在 comm 开始时给出错误：

指数超出范围。保存多个gridview数据时必须是非负的并且小于集合的大小

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

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 & "'"
        '"' and pid like '" & DataGridView1.Rows(i).Cells(1).Value.ToString &
        '"' and sdate like '" & sdate & "'"

        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) "

            'sql = "insert into tblstockin (refno, receivedby, pid, qty, sdate) " &
            '"values ('" & TxtReferenceNo.Text & "',
            '         '" & TxtReceiveBy.Text & "', 
            '         '" & CInt(DataGridView1.Rows(i).Cells(1).Value.ToString) & "',
            '         '" & CInt(DataGridView1.Rows(i).Cells(7).Value.ToString) & "', 
            '         '" & 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 fsql As String
            'fsql = "update tblproduct set qty=@qty where id=@id"

            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 & "'"
            'alternate code to update product quantity in product table
            'will update or add the number indicated on quantity to the existing product number in table

            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
                '.Parameters.AddWithValue("@qty", CInt(DataGridView1.Rows(i).Cells(7).Value.ToString))
                '.Parameters.AddWithValue("@id", CInt(DataGridView1.Rows(i).Cells(1).Value.ToString))
            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

标签： vb.net