[maiOHmai]

im trying to insert at least 3 columns in my database using datagridview.

im using this code to try inserting 3 rows at the same time:

 Private Sub btnBRR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBRR.Click
        If btnBRR.Text = "Borrow" Then
            con.Open()
            Try
             
                Dim rowa, rowb, rowc As String
                For i As Integer = 0 To Me.dgvIssuedBooks.Rows.Count
                    rowa = Me.dgvIssuedBooks.Rows(i).Cells(0).ToString()
                    rowb = Me.dgvIssuedBooks.Rows(i).Cells(1).ToString()
                    rowc = Me.dgvIssuedBooks.Rows(i).Cells(2).ToString()


                    da = New OleDbDataAdapter("insert into IssueBooks values('" & rowa & "','" & rowb & "','" & rowc & "')", con)
                  
                Next
            Catch ex As OleDb.OleDbException
                MsgBox(ex.Message)
            End Try
            con.Close()
        End If
    End Sub

but i've got this kind of error message

Quote

"Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index"

i also tried this other code,

 Private Sub btnBRR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBRR.Click
        If btnBRR.Text = "Borrow" Then
            con.Open()
            Try
                Dim i As Integer
                Dim dtmTest As Date
                dtmTest = DateValue(Now)
                Dim dt As New DataTable
                Dim ds As New DataSet
                ds.Tables.Add(dt) 'DataSet to DataTable

                da = New OleDbDataAdapter("Insert into IssueBooks(AccessionNumber, CallNumber, Title, ID, Borrower, DateBorrowed, DueDate) values ('" & dgvIssuedBooks.Rows(i).Cells("AccessionNumber").Value & "','" & dgvIssuedBooks.Rows(i).Cells("CallNumber").Value & "','" & dgvIssuedBooks.Rows(i).Cells("Title").Value & "','" & dgvSearch.Rows(i).Cells("ID").Value & "', '" & dgvSearch.Rows(i).Cells("FullName").Value & "','" & dtpBorrow.Text & "', '" & dtmTest & "')", con)
                da.Fill(dt)
                MsgBox("Book Borrowed")
                txtAccess2.Text = ""
               
               
            Catch ex As OleDb.OleDbException
                MsgBox(ex.Message)
              
            End Try
            con.Close()
        End If
    End Sub

it WORKS but it inserts only one row into my db even my dgv has 2 or more rows.

can someone help me in inserting more than one row in the database using dgv.

[modi123_1]

Why must they be in at the same time? Why not just run a for loop to insert each row in a collection?

[maiOHmai]

i have no idea how loop works.. so im trying to insert it at the same time..

[AdamSpeight2008]

Off by one error. Collection in .net are Zero-based, eg the third element is at index 2

 For i As Integer = 0 To Me.dgvIssuedBooks.Rows.Count - 1

[modi123_1]

Well then you need to read up on for-loops! Get some basic programming information down before you head out to do crazy stuff.

Side note - you can't insert multiples rows at the same time.. you need to feed the database the information one at a time.

[maiOHmai]

thank you AdamSpeight2008, your code works great for me..

here is my whole code:

Private Sub btnBRR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBRR.Click
If btnBRR.Text = "Borrow" Then
con.Open()
Try
For i As Integer = 0 To Me.dgvIssuedBooks.Rows.Count - 1
Dim a As Integer
Dim dtmTest As Date
dtmTest = DateValue(Now)
Dim dt As New DataTable
Dim ds As New DataSet
ds.Tables.Add(dt) 'DataSet to DataTable

da = New OleDbDataAdapter("Insert into IssueBooks(AccessionNumber, CallNumber, Title, ID, Borrower, DateBorrowed, DueDate) values ('" & dgvIssuedBooks.Rows(i).Cells("AccessionNumber").Value & "','" & dgvIssuedBooks.Rows(i).Cells("CallNumber").Value & "','" & dgvIssuedBooks.Rows(i).Cells("Title").Value & "','" & dgvSearch.Rows(a).Cells("ID").Value & "', '" & dgvSearch.Rows(a).Cells("FullName").Value & "','" & dtpBorrow.Text & "', '" & dtmTest & "')", con)

da.Fill(dt)
Next
MsgBox("Book Borrowed")


Catch ex As OleDb.OleDbException
MsgBox(ex.Message)

End Try
con.Close()
End If
End Sub

This post has been edited by maiOHmai: 27 July 2012 - 09:14 PM

[maiOHmai]

i've got another question. how would i limit my dgv row input into 3? i mean, all i want to insert is at least 3 maximum rows in the dgv..