How to loop through rows and columns in a DatagridView to insert their values in a mysql table

Question

Well, I have a bunch of .txt files that I'm importing in a DataGridView with vb.net, these files will always have different columns, just like these two examples:

First and Second DataGridView

And I'm using the next code to loop through the rows and columns of the DataGridView and also the code insert the data to a mysql table, now, the process I make, it's the next:

I open the .txt file.
I tried to insert the data with the First DataGridView, but with the error, the data it's not supposed to be inserted to the table, nevertheless it does it, and although it only has 4 colummns, when I check my database, it shows everything messed up, and it duplicates columns, like the lower image.
And then I "export" them to the mysql table. And when I do that, I get the error in the upper image.

Here's my code:

 
Private Sub ExportarToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExportarToolStripMenuItem.Click

        Dim conn As MySqlConnection = New MySqlConnection("Server=localhost;user=root;password=1234;database=chafa;port=3306")
        conn.Open()

        Dim comm As MySqlCommand = New MySqlCommand()
        comm.Connection = conn



        Dim col1, col2, col3, col4, col5, col6, col7, col8, col9, col10

        Dim tabla As New DataTable


        For i = 2 To DgvDatos.Rows.Add - 1 Step 1
            For j = 0 To Me.DgvDatos.Columns.Count - 1

                col1 = DgvDatos.Rows(i).Cells(j).Value()
                col2 = DgvDatos.Rows(i).Cells(j).Value()
                col3 = DgvDatos.Rows(i).Cells(j).Value()
                col4 = DgvDatos.Rows(i).Cells(j).Value()
                col5 = DgvDatos.Rows(i).Cells(j).Value()
                col6 = DgvDatos.Rows(i).Cells(j).Value()
                col7 = DgvDatos.Rows(i).Cells(j).Value()
                col8 = DgvDatos.Rows(i).Cells(j).Value()
                col9 = DgvDatos.Rows(i).Cells(j).Value()
                col10 = DgvDatos.Rows(i).Cells(j).Value()


                comm.CommandText = "insert into ejemplo(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10) values('" & col1 &_ "','" & col2 & "','" & col3 & "','" & col4 & "','" & col5 & "','" & col6 & "','" & col7 & "','" & col8 & "','" & col9 & "','" & col10 & "')"
                comm.ExecuteNonQuery()
            Next
        Next



        MessageBox.Show("Datos Agregados Correctamente")
        conn.Close()

    End Sub

What am I doing wrong? Thanks in advance and sorry If I'm asking a repeated question.

First: you don't need to loop over the columns (this produces duplicates) Second: Learn how to use a parameterized query — Steve
Use a DataTable as the DataSource and just add rows to the datatable. Whenever possible, work with the data not the user's view of it. — Ňɏssa Pøngjǣrdenlarp
Create a MySqlDataAdapter using a SELECT command and a connection. Then using a MySqlCommandBuilder create INSERT, UPDATE and DELETE command automatically. Put data in the DataTable and if you need to manipulate them, set the DataTable as DataSource of the DataGridView. To save changes, call Update method of table adapter. — Reza Aghaei
Apparently these are imported into the DGV prior to exporting to MySQL. Does the user have to do something with these first? If not you could just directly import them to MySQL — Ňɏssa Pøngjǣrdenlarp
Well, actually I'm looking for a process to create the MySQL table from the data of the .txt files, (actually they are .csv), reading the second row, and using it as the headers ot each column, but they're always different, they can go from 50 columns, to 500, so, if you can tell me how to process them, before I can use "LOAD DATA INFILE" or someting like that, thanks in advance. — Jess Cervantes

TAB.96 TAB.96 · Accepted Answer · 2017-07-21T04:48:22

I have done something similar using sql not mysql but maybe it can help you:

query = String.Empty
        query &= "UPDATE schedule SET Task = @Task, Complete = @Complete, Start_date = @Start_date, "
        query &= "Due_date = @Due_date, JRID = @JRID, Task_Manager = @Task_Manager, Entered_By = @Entered_By, Time_Entered = @Time_Entered "
        query &= "WHERE TaskID = @TaskID "
        query &= "IF @@ROWCOUNT = 0 INSERT INTO schedule ( TaskID, Task, start_date, Due_Date, Complete, Task_Manager, JRID, Entered_By, Time_Entered)"
        query &= " VALUES ( @TaskID, @Task, @start_date, @Due_Date, @Complete, @Task_Manager, @JRID, @Entered_By, @Time_Entered);"
        If MainSchedule.isokclicked = 1 Then
            For Each row As DataGridViewRow In MainSchedule.DataGridView1.Rows
                If Not (row.Cells(0).Value = Nothing) Then
                    insertcommand.Parameters.Clear()
                    insertcommand.CommandText = query
                    insertcommand.Parameters.AddWithValue("@TaskID", row.Cells(0).Value)
                    insertcommand.Parameters.AddWithValue("@Complete", "False")
                    insertcommand.Parameters.AddWithValue("@Task", row.Cells(1).Value)
                    insertcommand.Parameters.AddWithValue("@Start_date", row.Cells(2).Value)
                    insertcommand.Parameters.AddWithValue("@Due_Date", row.Cells(3).Value)
                    insertcommand.Parameters.AddWithValue("@JRID", txtJRID.Text)
                    insertcommand.Parameters.AddWithValue("@Task_Manager", row.Cells(4).Value)
                    insertcommand.Parameters.AddWithValue("@Entered_By", GetUserName())
                    insertcommand.Parameters.AddWithValue("@Time_Entered", Now)
                    insertcommand.ExecuteNonQuery()
                End If
                keypos = keypos + 1
            Next
            Connexion.Close()
        Else
        End If

Its just a basic FOR loop that runs parameterized queries, it may not be the most efficient way, but it is simple. This code will also update the datatable, or insert if there is no matching key in the current database. Please don't hesitate to let me know if you have any questions, or even suggestions about/for my code.

How to loop through rows and columns in a DatagridView to insert their values in a mysql table

1 Answers