6 Replies - 735 Views - Last Post: 27 July 2012 - 09:19 PM Rate Topic: -----

#1 maiOHmai  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 86
  • Joined: 09-July 12

inserting more that one row at the same time in database using dgv

Posted 27 July 2012 - 08:12 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: inserting more that one row at the same time in database using dgv

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9194
  • View blog
  • Posts: 34,516
  • Joined: 12-June 08

Re: inserting more that one row at the same time in database using dgv

Posted 27 July 2012 - 08:16 AM

Why must they be in at the same time? Why not just run a for loop to insert each row in a collection?
Was This Post Helpful? 0
  • +
  • -

#3 maiOHmai  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 86
  • Joined: 09-July 12

Re: inserting more that one row at the same time in database using dgv

Posted 27 July 2012 - 08:42 AM

i have no idea how loop works.. so im trying to insert it at the same time..
Was This Post Helpful? 0
  • +
  • -

#4 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2262
  • View blog
  • Posts: 9,462
  • Joined: 29-May 08

Re: inserting more that one row at the same time in database using dgv

Posted 27 July 2012 - 08:47 AM

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


Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9194
  • View blog
  • Posts: 34,516
  • Joined: 12-June 08

Re: inserting more that one row at the same time in database using dgv

Posted 27 July 2012 - 08:51 AM

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.
Was This Post Helpful? 0
  • +
  • -

#6 maiOHmai  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 86
  • Joined: 09-July 12

Re: inserting more that one row at the same time in database using dgv

Posted 27 July 2012 - 09:05 PM

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

Was This Post Helpful? 0
  • +
  • -

#7 maiOHmai  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 86
  • Joined: 09-July 12

Re: inserting more that one row at the same time in database using dgv

Posted 27 July 2012 - 09:19 PM

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..
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1