if you have any doughts cor this the whole code:
Public Sub createtable(ByVal newtable As DataTable, ByVal filepath As String)
Dim Cmd As OleDbCommand
Dim SQL As String
Dim objCmd As New OleDbCommand
Dim Con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data source =" & filepath)
Dim columns As String = ""
Dim colarray(newtable.Columns.Count - 1) As String
Dim colstr As String
For col = 0 To newtable.Columns.Count - 1
columns = columns & " [" & newtable.Columns(col).ColumnName.ToString & "] " & IIf(newtable.Columns(col).DataType.Name = "String", "TEXT(30),", IIf(newtable.Columns(col).DataType.Name
= "Int32", "INT,", IIf(newtable.Columns(col).DataType.Name = "DateTime", "DateTime,", "")))
colstr = colstr & newtable.Columns(col).ColumnName.ToString & ","
colarray(col) = newtable.Columns(col).ColumnName.ToString
Next
colstr = Mid(colstr, 1, colstr.Length - 1)
columns = "(" & Mid(columns, 1, columns.Length - 1) & ")"
SQL = "CREATE TABLE " & newtable.TableName & columns
Cmd = New OleDbCommand(SQL, Con)
Con.Open()
objCmd = New OleDbCommand(SQL, Con)
objCmd.ExecuteNonQuery()
'-----------------------------------------------------------------------
Dim oddaTbl1 As OleDb.OleDbDataAdapter
Dim CBTbl1 As OleDb.OleDbCommandBuilder
Dim DTTbl1 As New DataTable
oddaTbl1 = New OleDb.OleDbDataAdapter("select * from TBL_Source1", Con)
CBTbl1 = New OleDb.OleDbCommandBuilder(oddaTbl1)
oddaTbl1.Fill(DTTbl1)
Dim DR_Tbl1NewRow As DataRow = DTTbl1.NewRow()
Dim insertcommand As OleDbCommand
Dim colname(newtable.Columns.Count - 1) As String
For nt = 0 To newtable.Rows.Count - 1
For t = 0 To colarray.Length - 1
DR_Tbl1NewRow(colarray(t)) = newtable.Rows(nt)(colarray(t))
Next
For colnum = 0 To newtable.Columns.Count - 1
Select Case newtable.Columns(colnum).DataType.Name
Case "String"
colname(colnum) = "'" & newtable.Rows(nt)(colarray(colnum)) & "'"
Case "Int32"
If Not IsDBNull(newtable.Rows(nt)(colarray(colnum))) Then
colname(colnum) = newtable.Rows(nt)(colarray(colnum))
End If
Case "DateTime"
colname(colnum) = "#" & newtable.Rows(nt)(colarray(colnum)) & "#"
End Select
colnum = colnum
Next
Dim colValstr As String '= Join(colname, ",")
colValstr = String.Join(",", colname)
'colValstr = String.Join(",", Array.ConvertAll(colname, Function(s) "'" & s & "'"))
DTTbl1.Rows.Add(DR_Tbl1NewRow)
insertcommand = New OleDbCommand("insert into TBL_Source1 (" & colstr & ") Values (" & colValstr & ")")
For p = 0 To newtable.Columns.Count - 1
Select Case newtable.Columns(p).DataType.Name
Case "String"
insertcommand.Parameters.Add(colarray(p), OleDbType.Char, 50, colarray(p))
Case "Int32"
insertcommand.Parameters.Add(colarray(p), OleDbType.Integer, 10, colarray(p))
Case "DateTime"
insertcommand.Parameters.Add(colarray(p), OleDbType.Date, 10, colarray(p))
End Select
Next
oddaTbl1.InsertCommand = insertcommand
oddaTbl1.Update(DTTbl1)
Next
Con.Close()
End Sub
Danielicy
turning an array into a string
