Inserting multiple rows from datagridview to database table

[rony_78]

hi friendsI 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 objectDim nonqueryCommand As OleDbCommand = thisConnection.CreateCommand()Try' Open ConnectionthisConnection.Open()Console.WriteLine("Connection Opened")' Create INSERT statement with named parametersnonqueryCommand.CommandText = _"INSERT INTO myTable (Col1, Col2) VALUES (@Col1, @Col2)"' Add Parameters to Command Parameters collectionnonqueryCommand.Parameters.Add("@Col1"… OleDbType.VarChar, 50)nonqueryCommand.Parameters.Add("@Col2"… OleDbType.VarChar, 50)' Prepare command for repeated executionnonqueryCommand.Prepare()' Data to be insertedFor Each row As DataGridViewRow In DataGridView1.RowsIf Not row.IsNewRow ThennonqueryCommand.Parameters("@Col1").Va… = row.Cells(0).Value.ToStringnonqueryCommand.Parameters("@Col2").Va… = row.Cells(1).Value.ToStringEnd IfNextnonqueryCommand.ExecuteNonQuery()Catch ex As OleDbException' Display errorConsole.WriteLine("Error: " & ex.ToString())Finally' Close ConnectionthisConnection.Close()Console.WriteLine("Connection Closed")End Try

[jassticky]

Hey rony,This is the concept to insert into database for 2 entries . We have to keep one column as primary key and using for loop we can achive this. Using this concept you can do n number of columns insertion.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click		Dim str3		Try			Dim str2 As String			Dim count As Integer			If (DataGridView1.Rows.Count > 0) Then				For i As Integer = 0 To DataGridView1.Rows.Count - 2					str2 = "select count (*) from userinfo where userid = '" + DataGridView1.Rows(i).Cells(0).Value + "'"					str3 = New SqlCommand(str2, con)					con.Open()					count = str3.ExecuteScalar					con.Close()					If (count = 0) Then						Dim sqlcmd = New SqlCommand("insert into userinfo values('" + DataGridView1.Rows(i).Cells(0).Value + "','" + DataGridView1.Rows(i).Cells(1).Value + "')", con)                        sqlcmd.CommandType = CommandType.Text						con.Open()						sqlcmd.ExecuteNonQuery()						con.Close()					End If				Next                MsgBox("no records found to be inserted")			End If		Catch ex As Exception			MsgBox(ex.Message)		End Try	End Sub

Edited January 17, 2013 by jassticky