none
想请教有关multi select datagridview insert data 进 database RRS feed

  • 问题

  • 我遇到的问题是Index was out of range. Must be non-negative and less than the size of the collection.Parameter name: index 

    Private Sub BindDataGridView()
            Dim dt As New DataTable()
            dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
            dt.Rows.Add(1, "John Hammond", "United States")
            dt.Rows.Add(2, "Mudassar Khan", "India")
            dt.Rows.Add(3, "Suzanne Mathews", "France")
            dt.Rows.Add(4, "Robert Schidner", "Russia")
            dt.Rows.Add(5, "Robert", "Malaysia")
            dt.Rows.Add(6, "Apple", "Japan")
            dt.Rows.Add(7, "Banana", "Korea")
            dt.Rows.Add(8, "Rat", "UK")
            dt.Rows.Add(9, "Kitty", "Africa")
            dt.Rows.Add(10, "Comrel", "UK")

            Me.dataGridView1.DataSource = dt

            'Add a CheckBox Column to the DataGridView at the first position.
            Dim checkBoxColumn As New DataGridViewCheckBoxColumn()
            checkBoxColumn.HeaderText = ""
            checkBoxColumn.Width = 30
            checkBoxColumn.Name = "checkBoxColumn"
            dataGridView1.Columns.Insert(0, checkBoxColumn)
        End Sub

    Insert 数据的code

    Dim inserted As Integer = 0
            For Each row As DataGridViewRow In DataGridView1.Rows
                Dim isSelected As Boolean = Convert.ToBoolean(row.Cells("checkBoxColumn").Value)
                If isSelected Then
                      Dim cmd As New OleDb.OleDbCommand
                    If Not cnn.State = ConnectionState.Open Then
                        cnn.Open()
                    End If
                    cmd.Connection = cnn
                    cmd.CommandText = "INSERT INTO [MYDATA](CustomerId, Name, Country )VALUES ('" & Me.DataGridView1.SelectedCells(0).ToString & "','" & Me.DataGridView1.SelectedCells(1).ToString & "','" & Me.DataGridView1.SelectedCells(2).ToString & "','" & Me.DataGridView1.SelectedCells(3).ToString & "','" & Me.DataGridView1.SelectedCells(4).ToString & "','" & Me.DataGridView1.SelectedCells(5).ToString & "','" & Me.DataGridView1.SelectedCells(6).ToString & "','" & Me.DataGridView1.SelectedCells(7).ToString & "','" & Me.DataGridView1.SelectedCells(8).ToString & "','" & Me.DataGridView1.SelectedCells(9).ToString & "')"

                    cmd.ExecuteNonQuery()
                    inserted = inserted + 1
                End If
            Next
            If inserted > 0 Then
                MessageBox.Show(String.Format("{0} records inserted.", inserted), "Message")
            End If

    谢谢

    2020年5月4日 9:32

答案

  • Hi christing,

    DataGridView.SelectedCells  属性是获取选定的单位格,如果用户只是点击 DataGridView 中的 CheckBox 行的话那么只能得到SelectedCells(0)的值,后续的 SelectedCells(1)...会超出范围,同时由于向表中插入三个元素,‘VALUES’ 后的元素个数也应是三。

    将插入语句修改成以下代码:

    cmd.CommandText = "INSERT INTO [MYDATA](CustomerId,Name, Country )VALUES ('" & row.Cells("Id").Value.ToString & "','" & row.Cells("Name").Value.ToString & "','" & row.Cells("Country").Value.ToString &"')"

    测试结果:

    希望可以帮助你解决问题。

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年5月5日 5:52
    版主