question

AMERSAID-7084 avatar image
0 Votes"
AMERSAID-7084 asked XingyuZhao-MSFT commented

Fill in the database from datatable

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
dotnet-visual-basic
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

XingyuZhao-MSFT avatar image
0 Votes"
XingyuZhao-MSFT answered XingyuZhao-MSFT commented

Hi @AMERSAID-7084 ,
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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI
Xingyu Zhao

"cola" The column does not belong to the table


I pulled the column names from the Excel copyBast file and they are there. Why is it wrong?

0 Votes 0 ·

Hi @AMERSAID-7084 ,
You need to extract all possible column names from excel and use them to create datatable 'dt'.

0 Votes 0 ·