Insert multiple rows from datagridview to table?

rony

New member
Joined
Sep 23, 2012
Messages
1
Programming Experience
Beginner
hi friends
I am beginning to learn vb.net and now i am trying to use DatagridView Control.I am having difficulty to insert two rows in database table from datagridview. I can insert one row , I would like to know how do i run nonqueryCommand.Parameters inside For Each loop twice to insert two row. please help me


VB.NET:
 Dim thisConnection As New OleDbConnection("Provider=Microsoft.ACE.… Source=C:\Dtb.accdb")

'Create Command object
Dim nonqueryCommand As OleDbCommand = thisConnection.CreateCommand()

Try
' Open Connection
thisConnection.Open()
Console.WriteLine("Connection Opened")

' Create INSERT statement with named parameters
nonqueryCommand.CommandText = _
"INSERT INTO myTable (Col1, Col2) VALUES (@Col1, @Col2)"

' Add Parameters to Command Parameters collection
nonqueryCommand.Parameters.Add("@Col1"… OleDbType.VarChar, 50)
nonqueryCommand.Parameters.Add("@Col2"… OleDbType.VarChar, 50)

' Prepare command for repeated execution
nonqueryCommand.Prepare()

' Data to be inserted
For Each row As DataGridViewRow In DataGridView1.Rows
If Not row.IsNewRow Then
nonqueryCommand.Parameters("@Col1").Va… = row.Cells(0).Value.ToString
nonqueryCommand.Parameters("@Col2").Va… = row.Cells(1).Value.ToString
End If
Next

nonqueryCommand.ExecuteNonQuery()

Catch ex As OleDbException
' Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
' Close Connection
thisConnection.Close()
Console.WriteLine("Connection Closed")

End Try
 
Hi,

You have got your execute non query command outside your for loop and therefore it only executes once at the end of the for loop...

It should be inside the for loop to execute for each row of the DataDridView
:-

VB.NET:
For....
  If....
    set param
    set param
    nonqueryCommand.ExecuteNonQuery
  End If
Next
Cheers,

Ian
 
You really don't need a loop. I would strongly suggest using a DataTable and a data adapter. Create the DataTable to begin with, either manually or by calling FillSchema on your data adapter, and bind that to the grid. As the user populates the grid they are also populating the DataTable. When you're done, just call Update on your data adapter and all the data is saved.
 
As said above, it really is the best way to do it. Here is an example of a very simple usage of data adapter/bindingsource/datagridview

When the data is changed in the datagrid, you can create a button to "Save" so it updates



Try
           'Select Command Here
           dim selectCommand = "SELECT * from tblYourTable"
           'Your Connection String Here
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\You\YourDatabase.mdb"
            ' Create a new data adapter based on the specified query. 
              Me.dataAdapter = New OleDb.OleDbDataAdapter(selectCommand, connectionString)
            ' Create a command builder to generate SQL update, insert, and delete commands based on selectCommand.
            Dim commandBuilder As New OleDb.OleDbCommandBuilder(Me.dataAdapter)
            ' Populate a new data table and bind it to the BindingSource. 
            Dim table As New DataTable()
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            'Set the binding source to the data table
            Me.BindingSource1.DataSource = table
            'Populate datagridview
            DataGridView1.DataSource = table
        Catch ex As OleDbException
            MessageBox.Show("Error....Please speak to Admin")
        End Try




Then to save the data changed in the datagridview to your database see the following:

Me.dataAdapter.Update(CType(Me.BindingSource1.DataSource, DataTable))


Hope this helps and clears things up

Dan
 
Last edited by a moderator:

Latest posts

Back
Top