Fill in the database from datatable

Question

hi

I imported the data from an excel file. The file does not contain specified columns, but is variable, and has a maximum of 5 columns. But it can be from 3 columns or 2 to 5 columns. The five columns have names, but the position of the column and the accent changes according to the file. What I want is to transfer data from DataTable to database. The transfer is according to the comparison of the column name and the required value if the column name is in it to the database. If the column name is not present, a "-" symbol is taken and saved in the database

Blockquote

Dim parta, partb, partc, partd As String
Dim i As Integer = 0
For i = 0 To dt2.Rows.Count - 1
If dt2 Is Nothing Then
Exit For : Exit Sub

                        End If

                        If (dt2.Rows(0)("cola").ToString = "cola") Then

                            parta = dt2.Rows(i)("cola")

                        End If


                        If (dt2.Rows(0)("colb").ToString = "colb") Then

                            partb = dt2.Rows(i)("colb")

                        End If

                        If (dt2.Rows(0)("colc").ToString = "colc") Then

                            partc = dt2.Rows(i)("colc")

                        End If

                        If (dt2.Rows(0)("cold").ToString = "cold") Then

                            partd = dt2.Rows(i)("cold")

                        End If
                        Dim com As New OleDbCommand("INSERT INTO EXPORT_TB(EXPORT_a,EXPORT_b,EXPORT_c,EXPORT_d) VALUES (@EXPORT_a,@EXPORT_b,@EXPORT_c,@EXPORT_d)", con)

                        com.Parameters.AddWithValue("@EXPORT_a", OleDbType.VarChar).Value = parta
                        com.Parameters.AddWithValue("@EXPORT_b", OleDbType.VarChar).Value = partb
                        com.Parameters.AddWithValue("@EXPORT_c", OleDbType.VarChar).Value = partc
                        com.Parameters.AddWithValue("@EXPORT_d", OleDbType.VarChar).Value = partd

                        con.Open()
                        com.ExecuteNonQuery()
                        con.Close()

                    Next

Answer 1

Hi @AMER SAID ,
First, you need to create a template table with five columns. For example:

        Dim dt As DataTable = New DataTable  
        dt.Columns.Add("cola")  
        dt.Columns.Add("colb")  
        dt.Columns.Add("colc")  
        dt.Columns.Add("cold")  
        dt.Columns.Add("cole")  

Then merge your 'dt2' into the table.

        dt.Merge(dt2)  

Finally, you can refer to the following code to save datatable to database.

        Dim cmd As New OleDbCommand("INSERT INTO EXPORT_TB(EXPORT_a,EXPORT_b,EXPORT_c,EXPORT_d,EXPORT_e) VALUES (@EXPORT_a,@EXPORT_b,@EXPORT_c,@EXPORT_d,@EXPORT_e)", con)  
        con.Open()  
        For Each row As DataRow In dt.Rows  
            cmd.Parameters.Clear()  
            cmd.Parameters.AddWithValue("EXPORT_a", IIf(row("cola").ToString() = "", "-", row("cola").ToString()))  
            cmd.Parameters.AddWithValue("EXPORT_b", IIf(row("colb").ToString() = "", "-", row("colb").ToString()))  
            cmd.Parameters.AddWithValue("EXPORT_c", IIf(row("colc").ToString() = "", "-", row("colc").ToString()))  
            cmd.Parameters.AddWithValue("EXPORT_d", IIf(row("cold").ToString() = "", "-", row("cold").ToString()))  
            cmd.Parameters.AddWithValue("EXPORT_e", IIf(row("cole").ToString() = "", "-", row("cole").ToString()))  
            cmd.ExecuteNonQuery()  
        Next  
        con.Close()  

Hope it could be helpful.

Best Regards,
Xingyu Zhao
*
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.