Fill in the database from datatable

asked 2021-04-05T14:44:05.463+00:00
AMER SAID 331 Reputation points

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
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
1,823 questions
No comments
{count} votes

Accepted answer
  1. answered 2021-04-06T07:59:40.797+00:00
    Xingyu Zhao-MSFT 5,326 Reputation points

    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.


0 additional answers

Sort by: Most helpful