inserting data from a .NET datatable into ODBC

[jan_be]

Hi you great specialists everywhere ...

I try to insert data from an obscure source, that I have structured into a datatable,
into an existing database table using ODBC.

I was inspired by this :
http://www.vbforums.com/showthread.php?t=532088 (at the bottom : "Took me way too long, but i finally got it")

So I came up with this :

Dim dt As DataTable = Data.Clone
        Dim r2 As DataRow
        For Each r As DataRow In Data.Rows  'to make sure every row is newly added
            r2 = dt.NewRow
            r2.ItemArray = r.ItemArray
            dt.Rows.Add(r2)
        Next
 
        Dim ODBCDataAdapter As New OdbcDataAdapter
        ODBCDataAdapter.InsertCommand = New OdbcCommand("")
        Dim val As String = ""
        Dim prmODBC As OdbcParameter
        Dim t As OdbcType
        For Each c As DataColumn In dt.Columns
            ODBCDataAdapter.InsertCommand.CommandText &= "," & c.ColumnName
            val &= ",?" & c.ColumnName
            Select Case c.DataType.FullName
                Case GetType(Short).FullName
                    t = OdbcType.SmallInt
                Case GetType(Integer).FullName
                    t = OdbcType.Int
                Case GetType(Long).FullName
                    t = OdbcType.BigInt
                Case GetType(String).FullName
                    t = OdbcType.VarChar
                Case GetType(Boolean).FullName
                    t = OdbcType.Bit
                Case Else
                    t = Nothing
            End Select
            If t = OdbcType.VarChar Then
                prmODBC = ODBCDataAdapter.InsertCommand.Parameters.Add("?" & c.ColumnName, t, 50)
            Else
                prmODBC = ODBCDataAdapter.InsertCommand.Parameters.Add("?" & c.ColumnName, t)
            End If
            prmODBC.SourceVersion = DataRowVersion.Current
            prmODBC.SourceColumn = c.ColumnName
        Next
        ODBCDataAdapter.InsertCommand.CommandText = "Insert into " & Data.TableName & "(" & ODBCDataAdapter.InsertCommand.CommandText.Substring(1) & ") VALUES (" & val.Substring(1) & ")"
 
==> in the debugger I check CommandText :
"Insert into Venice_APR(YR,SysNum,DocNum,SupNum,AccYear,Book,AutoPay) VALUES (?YR,?SysNum,?DocNum,?SupNum,?AccYear,?Book,?AutoPay)"
 
'then I continue :
 
            ODBCDataAdapter.Update(dt)

..... and get the error :
You have an error in your SQL syntax; check the manual for the right syntax to use near 'Book,1AutoPay)' at line 1

This I do not understand ...
Something wrong with the Book column ? defined as varchar(3)

The "data" datatable with the data has all the right data in the correct form,
I am sure of dat, because up to now I have obtained the wanted functionality in a different way :
looping through the rows, I issue separate insert-statements.
But the performance is rather poor, so I am exploring better ways to reach my goal ...

Thanks for any help offered !

Regards,
Jan (Belgium)