Creating a new version of data table with same column name and column data type

Question

Converting tables from .mdbs to .accdbs by creating new .accdb with same tables and the tables have same column names with same column data types. Using OleDB and sql to ExecuteNonQuery. I am having problems when I try to have the same datatype from the old data table column for the new column. I bring fill dTable with

adp.Fill(dTable)

The following code was giving me issues with columns of different data types other than varchar.

Public Sub ConvertTable(dTable As DataTable, tableName As String)

    Dim i As Int16 = 0
    Dim j As Int16 = 0
    strConnOutput = SharedRoutines.GetAccessACEConnectionString(aceDatabase)
    Using con2 As New OleDbConnection(strConnOutput)
        con2.Open()
        Dim sqlText As String = ""

        'Set up query to create a table with the same name as the'
        'spreadsheet.'
        sqlText = "CREATE TABLE [" & tableName & "] ("

        'Create all the columns that exist in the spreadsheet.'
        For Each col As DataColumn In dTable.Columns
            sqlText &= "[" & col.ColumnName & "] varchar, "
        Next
        'Clean up the end of the CREATE query.'
        sqlText = sqlText.Substring(0, sqlText.Length - 2) & ")"

        Using cmValid As New OleDbCommand(sqlText, con2)
            cmValid.ExecuteNonQuery()
        End Using

        'Set the INSERT SQL query for the data in the spreadsheet.'
        sqlText = "INSERT INTO [" & tableName & "] ("
        'Add all the columns to the INSERT query.'
        For Each col As DataColumn In dTable.Columns
            sqlText &= "[" & col.ColumnName & "], "
        Next
        'Clean up the end of the query string and prepare for the parameters.'
        sqlText = sqlText.Substring(0, sqlText.Length - 2) & ") VALUES ("
        'Add all the parameter placeholders to the INSERT query.'
        For Each col As DataColumn In dTable.Columns
            sqlText &= "?, "
        Next
        'Clean up the end of the INSERT query.'
        sqlText = sqlText.Substring(0, sqlText.Length - 2) & ")"

        Try
            'Loop through all the rows of data in the spreadsheet.'
            For Each rw As DataRow In dTable.Rows
                Using cmValid As New OleDbCommand(sqlText, con2)

                    'Add a parameter for each column.'
                    For Each col As DataColumn In dTable.Columns
                        cmValid.Parameters.Add(New OleDbParameter(col.ColumnName, rw(col.ColumnName)))
                    Next
                    'Send data to the new table.'
                    cmValid.ExecuteNonQuery()

                End Using
            Next
        Catch ex As Exception
            j += 1
            convertResponse &= tableName & " from " & aceDatabase & " was not completed."
            MessageBox.Show(tableName & " from " & aceDatabase & " was not completed. Message:" & ex.ToString)
        End Try



        dTable.Dispose()
        i += 1

        convertResponse &= "Table " & tableName & " was added to " & aceDatabase & ControlChars.CrLf
    End Using

End Sub

I changed the For Each loop with

        For Each col As DataColumn In dTable.Columns
            sqlText &= "[" & col.ColumnName & "] " & col.DataType.ToString & ", "
        Next

but now the executeNonQuery() is throwing a syntax error. Is it not col.dataType.Tostring? SqlText with varchar CREATE TABLE [BASIC_DESCRIPTION] ([ColumnName] varchar, [Order] varchar) Compared with CREATE TABLE [BASIC_DESCRIPTION] ([ColumnName] System.String, [Order] System.Int32) My question is what do I need to put in the sqlText to make sure that the new columns being created are the same type as the old table? The closest answer I found is this. Link

The column name is not the same as the data type at all. You could/should read the schema from whatever DB the second table is related to; let NET build the DT — Ňɏssa Pøngjǣrdenlarp, Feb 28 '17 at 22:52
I have no idea, you dont actually show how you built the second table. My crystal ball says you just added DataColumns in a loop without specifying the type which would create text/string/varchar columns; thus the error when the column data is something else. — Ňɏssa Pøngjǣrdenlarp, Feb 28 '17 at 22:59
It will create the tables fine. I just want to make sure that the dataType for the columns is the same. the error is thrown with the sqlText in ExecuteNonQuery() — Matthew Kruse, Feb 28 '17 at 23:01
The table is not fine if you put the wrong type of columns in it. — Ňɏssa Pøngjǣrdenlarp, Feb 28 '17 at 23:04
I updated this question with the whole method. The method created tables with columns and row but automatically creates the dataType with short Text. My question what do I need to put in the sqlText to make sure that the new columns being created are the same type as the old table? — Matthew Kruse, Mar 01 '17 at 21:54
I have no idea what you are playing at. A DataTable is completely agnostic about the underlying database - they are just about the data. So the idea that you *need* to convert `tables from .mdbs to .accdbs` is wrong. If the new one does indeed have the same col names and types, none of that is needed. I cant quite follow much because the title asks about CREATING a new table (datatable?) but the crux of the code is `ConvertTable`. — Ňɏssa Pøngjǣrdenlarp, Mar 01 '17 at 22:08
`but now the executeNonQuery() is throwing a syntax error. ` Did you remember to remove the extra comma from your looping? Post the sqlText if you can't figure it out. — LarsTech, Mar 01 '17 at 22:12
@LarsTech the sqlText = "CREATE TABLE [BASIC_DESCRIPTION] ([ColumnName] System.String, [Order] System.Int32)". This is with the updated For Each Loop. When I try to run this the executeNonQuery() throws an error. — Matthew Kruse, Mar 01 '17 at 22:31
@Plutonix the new table columns do not have the same data types as the old ones. When I try to change the sqlText to address this the syntax error is thrown. — Matthew Kruse, Mar 01 '17 at 22:31
The database isn't going to use that same language: `System.String`, `System.Int32`, etc. You would have to use the equivalent Access version, `TEXT`, `VARCHAR`, etc. — LarsTech, Mar 01 '17 at 22:36
`the tables have same column name with same column data types` It is starting to sound like you have also confused DataTable with database Table — Ňɏssa Pøngjǣrdenlarp, Mar 01 '17 at 22:37
@LarsTech Thank you! so instead of me using col.datatype.tostring or col.datatype.fullname is there another command to get it into Text, or Varchar? — Matthew Kruse, Mar 02 '17 at 01:17

score 0 · Answer 1 · answered Feb 28 '17 at 22:54

0

I'm not a VB.NET guy, but I would try either ditching the .ToString and hoping it is implicit, or making it .ToString() because it is a method call.

answered Feb 28 '17 at 22:54

Phasmid

1
1

score 0 · Accepted Answer · answered Mar 03 '17 at 21:32

Created a GetDBType function from here and did ToString.

      'Create all the columns that exist in the spreadsheet.'
        For Each col As DataColumn In dTable.Columns
            Dim colSQLType As SqlDbType = GetDBType(col.DataType)
            Dim colType As String = colSQLType.ToString

            sqlText &= "[" & col.ColumnName & "] " & colType & ", "
        Next

Creating a new version of data table with same column name and column data type

2 Answers2