RE: Save a datatable to a new Access database

I've now realised it is because of the rowstate of imported data.

I know it's a long shot, but is there any way of applying something like
SetAdded() to all rows in a datatable at once, so I don't have to iterate
through the rows?

"Grinning Crow" wrote:

Hi

Using ADO.Net on .Net 2.0

I want to save a datatable to an access database as a new table. I have the
following code:

Function NewTable(ByVal strTableName As String, ByVal dtData As
DataTable) As Boolean
'takes the data from a datatable and puts it in the database as a
new table
'imports all fields as text
Dim commX As New OleDbCommand
Dim adptX As OleDbDataAdapter
Dim cbdX As OleDbCommandBuilder
Dim dcX As DataColumn
Dim strSQL As String

NewTable = False

'if the connection is closed then open it
If Connection.State <> ConnectionState.Open Then
Connection.Open()
End If

'initialise the sql statement
strSQL = "CREATE TABLE [" & strTableName & "] ("

'add the field names and types to the sql statement
For Each dcX In dtData.Columns
strSQL = strSQL & "[" & dcX.ColumnName & "] TEXT(255), "
Next

'remove the final comma and space and add the closing bracket
strSQL = strSQL.Substring(0, strSQL.Length - 2) & ")"

With commX
'if a table of the same name exists then delete it
If TableExists(strTableName) Then
.CommandText = "drop table [" & strTableName & "]"
.Connection = Connection
.ExecuteNonQuery()
End If
'create the new table
.CommandText = strSQL
.Connection = Connection
.ExecuteNonQuery()
End With

'associate the dataadapter with the newly created, empty table
adptX = New OleDbDataAdapter("select * from [" & strTableName & "]",
Connection)

cbdX = New OleDbCommandBuilder(adptX)

Console.WriteLine(adptX.Update(dtData))

NewTable = True

End Function

This works fine if I pass the function a table I have knocked up in memory
from scratch and added rows to it, but when doing what I actually want to
achieve, importing many different excel workbooks, book by book, *** by
***, it creates the table but doesn't insert the data.

Am I missing something obvious here?

Thanks for any help you can give.

.