How to insert multiple data using dgv?

[junskie]

is there anyone there can help how to insert a multiple data using dgv in my database...i can insert a data but the only it is only rather two i want?
here is my code any can help i do appreciate tnx in advance.....student :-)

 ' Create INSERT statement with named parameters
            nonqueryCommand.CommandText = _
               "INSERT  INTO ordetail (Account_code,description) VALUES (@Account_code,@description)"

            ' Add Parameters to Command Parameters collection
            nonqueryCommand.Parameters.Add("@Account_code", MySqlDbType.String, 0)
            nonqueryCommand.Parameters.Add("@description", MySqlDbType.String, 0)
            '  nonqueryCommand.Parameters.Add("@Col2", SqlDbType.VarChar, 50)

            ' Prepare command for repeated execution
            nonqueryCommand.Prepare()

            ' Data to be inserted
            For Each row As DataGridViewRow In DataGridView1.Rows
                If Not IsNothing(row.Cells(0).Value) Then
                    '  For i As Integer = 0 To DataGridView1.Rows.Count - 1

                    With nonqueryCommand

                        ' .Parameters[0].Value = datagridview1.Rows[i].Cells[0].Value

                        .Parameters("@Account_code").Value = row.Cells(0).Value.ToString

                        .Parameters("@description").Value = row.Cells(1).Value.ToString

                    End With
                    ' Next

                    'nonqueryCommand.Parameters("@Col2").Value = row.Cells(1).Value.ToString
                End If
            Next row

            nonqueryCommand.ExecuteNonQuery()
            ' nonqueryCommand.Parameters.Clear()

        Catch ex As MySqlException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        Finally
            ' Close Connection
            tryCon.Close()
            MsgBox("Details hav been successfully added", MsgBoxStyle.Information, "ORdetail information")

        End Try

[jmcilhinney]

Create a DataTable with the appropriate schema, either by building it manually or calling FillSchema on a data adapter. Bind that DataTable to your grid. Adding data to the grid will push it into the DataTable. You can then use a data adapter, perhaps the same one you called FillSchema on, to save the data to the database with a call to Update. To learn how to use the data adapter, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

[junskie]

ok tnx...can you give me an example?

[m.davide]

hi jmc, i dunno if this is allowed. but can i ask a follow up question since im also doing this.
can i just loop through the dgv and insert each row? thank you.

[jmcilhinney]

ok tnx...can you give me an example?

If you have read my post and followed the instructions provided then you already have an example. Why should I bother posting if you're not going to read what I post and do what it says?

[jmcilhinney]

hi jmc, i dunno if this is allowed. but can i ask a follow up question since im also doing this.
can i just loop through the dgv and insert each row? thank you.

That is absolutely allowed. If your question is on the same topic then it is encouraged to post to an existing thread.

Yes you can loop through the grid an insert each row but you shouldn't. Using a DataTable and data adapter is a better option. Unless you have a compelling reason to do otherwise, you should use the better option. If you do have a compelling reason then you would create a command and add parameters to it first. You would then loop through the grid and, for each row, set the parameter values of the command and execute it. That's exactly what junskie is doing in post #1 and I advised against it then.

[junskie]

sori by the way...i did not follow your instruction...i read it anyway it was great tnx for your help...hope i can ask again if i have further question...god bless :-)

[jmcilhinney]

sori by the way...i did not follow your instruction...i read it anyway it was great tnx for your help...hope i can ask again if i have further question...god bless :-)

Sure you can ask more questions. That's what we're here for. Just please, if and when an answer is provided, make sure you read it and follow any instructions provided. Also, pick out any keywords and, if you need more information, search for it first before asking. Do that and we'll get on famously.

[Baselx]

Thank you

[m.davide]

That is absolutely allowed. If your question is on the same topic then it is encouraged to post to an existing thread.

Yes you can loop through the grid an insert each row but you shouldn't. Using a DataTable and data adapter is a better option. Unless you have a compelling reason to do otherwise, you should use the better option. If you do have a compelling reason then you would create a command and add parameters to it first. You would then loop through the grid and, for each row, set the parameter values of the command and execute it. That's exactly what junskie is doing in post #1 and I advised against it then.

ok, thanks!

[junskie]

this code statement is totaly correct im just miss look up my table name supposed to be or_detail in i wrote it ----ordetail---- thats why my select cannot find any table and my catch exeption supposed to be a msgbox.show (ex.message) and not console.writeline it is not running in vb.net mode only in C# try to look at my code....hehehe here the code try to look at this


' Create INSERT statement with named parameters
nonqueryCommand.CommandText = _
"INSERT INTO ordetail (Account_code,description) VALUES (@Account_code,@description)"

' Add Parameters to Command Parameters collection
nonqueryCommand.Parameters.Add("@Account_code", MySqlDbType.String, 0)
nonqueryCommand.Parameters.Add("@description", MySqlDbType.String, 0)
' nonqueryCommand.Parameters.Add("@Col2", SqlDbType.VarChar, 50)

' Prepare command for repeated execution
nonqueryCommand.Prepare()

' Data to be inserted
For Each row As DataGridViewRow In DataGridView1.Rows
If Not IsNothing(row.Cells(0).Value) Then
' For i As Integer = 0 To DataGridView1.Rows.Count - 1

With nonqueryCommand

' .Parameters[0].Value = datagridview1.Rows[i].Cells[0].Value

.Parameters("@Account_code").Value = row.Cells(0).Value.ToString

.Parameters("@description").Value = row.Cells(1).Value.ToString

End With
' Next

'nonqueryCommand.Parameters("@Col2").Value = row.Cells(1).Value.ToString
End If
Next row

nonqueryCommand.ExecuteNonQuery()
' nonqueryCommand.Parameters.Clear()

Catch ex As MySqlException
' Display error
Console.WriteLine("Error: " & ex.ToString())
Finally
' Close Connection
tryCon.Close()
MsgBox("Details hav been successfully added", MsgBoxStyle.Information, "ORdetail information")