Insert multiple rows from datagridview to table?

[rony]

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

 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

 

[IanRyder]

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:-

For....
  If....
    set param
    set param
    nonqueryCommand.ExecuteNonQuery
  End If
Next

Cheers,

Ian

 

[jmcilhinney]

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.

 

[danarchy]

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