Cannot add DataTable into MS Access table

Question

I have a DataTable with about 6000 rows created from a query to SQL Server DB. Now I try to add the data into a MS-Access table. Everything seems to work. No exceptions are raised but no data is a being added. Here is the code I'm using:

DataTable t = new DataTable();
// fill data table


// Create DB - works!
var Name = DateTime.Now.ToString("H_mm_ss");

string strAccessConnectionString = @"Provider = Microsoft.Jet.OLEDB.4.0;" +
                                    @"Data Source=" + "C:\\chhenning\\" + Name + ".mdb;";

ADOX.CatalogClass Catalog = new ADOX.CatalogClass();
Catalog.Create(strAccessConnectionString);

OleDbConnection accessConnection = new OleDbConnection(strAccessConnectionString);
accessConnection.Open();

// Create Table - works!
OleDbCommand Command = accessConnection.CreateCommand();

Command.CommandText = "Create Table Test( "
    + " ID_ int not null "
    + " , Year_ int not null "
    + " , Value_ float not null )";

Command.ExecuteNonQuery();

// Add data into table - does not work!
var adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand("select * from Test", accessConnection);

var cbr = new OleDbCommandBuilder(adapter);
cbr.GetInsertCommand();

var Rows = adapter.Update(t);

I have made sure that my DataTable has data in and that both the DataTable and MS-Access have the same columns with the same data types.

Can someone spot what wrong with code? What are the steps I do to investigate the problem further?

Steve · Accepted Answer · 2013-05-31 15:41:54Z

3

The adapter.Update(table) method works looking at the RowState of the rows in the DataTable.
If the rows have RowState == DataRowState.Unchanged, the method will not perform any update.

I suppose that you load the datatable from the SqlServer database without making any change and thus the RowState is Unchanged for every row. Try to loop on the Rows and call

foreach(DataRow r in t.Rows)
    r.SetAdded();

This will force the RowState on each column to Added and then the InsertCommand on the DataAdapter will execute the insert

edited May 31, 2013 at 15:41

answered May 31, 2013 at 15:11

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chhenning Over a year ago

Thant seems to move me a step further. Now I get a InvalidOperationException. "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." Do I need to a primary key?

Steve Over a year ago

Yes, the CommandBuilder needs it. Or write your own InsertCommand for the DataAdapter

chhenning Over a year ago

Ok, I added the primary key to my first column and when I update the return value of adapter.Update() is correct. But when I open the mdb file in MS-Access there still is no data.

Steve Over a year ago

My fault, you need SetAdded to add not SetModified

Collectives™ on Stack Overflow

Cannot add DataTable into MS Access table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related