I am a .NET newbie and I'm surprised to find almost no posts on this question; only a couple that weren't answered. I hope some kind soul answers this post.

I have a DataTable whose rows are in various RowStates. I want to copy the entire DataTable to a table in an external MS Access database accessed via an OleDbDataAdapter.

Using the DataAdapter.Update method doesn't work unless all the rows are in the Added state, which isn't the case. Since the RowState property is read-only, I can't assign this myself.

I thought using ExecuteNonQuery to do an INSERT of each DataTable row. But since MS Access doesn't support multiple SQL statements in ExecuteNonQuery, I would need to code up a loop doing one ExecuteNonQuery per row. I am concerned this will be too inefficient.

I am currently solving this problem by using the DataTable.Clone method to make a copy of the DataTable's structure, and then using LoadDataRow in a loop to add each row to the table copy. I then pass the copy to DataAdapter.Update.

All of these approaches seem like a kludge to me. Is there a better way?

Thanks for the help!

Dave

The procedure is to call the DataAdapter's update method.  The general
procedure is as follows:

To get the data in the first place...

Create a connection object (OleDBConection) and configure its connection
string
Create a command object (OledDBCommand) and configure its commandText
property (a SQL SELECT statement)
Create a DataAdapter object (OleDBDataAdapter)
Create a DataSet object (DataSet)
Call the .fill method of the DataAdapter and populate the DataSet with the
data.
A DataSet will now contain a DataTable with a copy of the original data.
Modify the data as you need to (the DataAdapter will know what records have
been modified or not)
Call the DataAdapter's .Update method (you can write your own update command
for the DataAdapter or configure the DataAdapter to create its own).

news:7DFB64BA-87EA-4954-8F91-8F3C95EE9ACA@microsoft.com...
question; only a couple that weren't answered. I hope some kind soul answers
this post.
entire DataTable to a table in an external MS Access database accessed via
an OleDbDataAdapter.
in the Added state, which isn't the case. Since the RowState property is
read-only, I can't assign this myself.
since MS Access doesn't support multiple SQL statements in ExecuteNonQuery,
I would need to code up a loop doing one ExecuteNonQuery per row. I am
concerned this will be too inefficient.
make a copy of the DataTable's structure, and then using LoadDataRow in a
loop to add each row to the table copy. I then pass the copy to
DataAdapter.Update.

Sorry I don't think I stated my problem clearly enough.

I have a DataTable that is FILL'ed from Database#1. As the application is run, the data is changed, and the Update method is used to write the changes back to Database #1.

At some point, I want to write the contents of this DataTable to Database#2. The initial contents of this table in Database#2 is empty. At the time that I want to do this, all the rows in my table have had AcceptChanges called on them.

If I open a DataConnection to Database#2, create a DataAdapter, and call its Update method as you suggest, nothing happens because none of my table's rows are in the Added state.

What is the best way to copy this data to Database#2?

Thanks...

Dave

Hi,

A sollution might be to insert the records from initial DataTable into a new
DataTable, the DataRows states will be 'Added' and passing this new
DataTable to the DataAdapter should insert the records into Database#2.

Hope this helps

--
Chris Taylor
http://dotnetjunkies.com/WebLog/chris.taylor/

news:9DBD809F-2C06-451A-A4AF-BB65C94EECF4@microsoft.com...
run, the data is changed, and the Update method is used to write the changes
back to Database #1.
Database#2. The initial contents of this table in Database#2 is empty. At
the time that I want to do this, all the rows in my table have had
AcceptChanges called on them.
its Update method as you suggest, nothing happens because none of my table's
rows are in the Added state.

Hi Dave,

Dataset 2 does need the shema from database 2.
If you want to update it to database2.

You can get that with a sqldataAdapter.fillschema

I hope this helps?

Cor

Thanks for the reply, Chris.

If you go back to my original post that started this thread you
will see that is exactly what I am doing. My original questions
(still not answered) were:

a) Is it better to make the DataTable copy as you suggest, or
b) Is it better use SQL INSERTs?, and
c) Is there a better alternative to (a) or(b)?

Below is my code for (a) and (b). I am wondering if the underlying
implementation of Update in approach (a) could be more effecient
(i.e. is it implemented in Native code?) than the loop of INSERTs
in approach (b) so as to make up the overhead of copying the table?
Or is there some still better approach not yet discussed???

Any comments would be appreciated!

Thanks...

Dave

// Approach (a):
DataTable copyTable = origTable.Clone();
copyTable.BeginLoadData();
foreach (DataRow row in origTable.Rows)
{
  copyTable.LoadDataRow(row.ItemArray, false);

copyTable.EndLoadData();
dataAdapter.Update(copyTable);

// Approach (b):
OleDbCommand insertCommand = dataAdapter.InsertCommand;
foreach (DataRow row in origTable.Rows)
{
  foreach (OleDbParameter param in insertCommand.Parameters)
  {
    param.Value = row[param.ParameterName];
  }
  insertCommand->ExecuteNonQuery();

     Hi,

     A sollution might be to insert the records from initial DataTable into a new
     DataTable, the DataRows states will be 'Added' and passing this new
     DataTable to the DataAdapter should insert the records into Database#2.

     Hope this helps

     --
     Chris Taylor
     http://dotnetjunkies.com/WebLog/chris.taylor/
     "Dave" <anonym...@discussions.microsoft.com> wrote in message
     news:9DBD809F-2C06-451A-A4AF-BB65C94EECF4@microsoft.com...
     > Sorry I don't think I stated my problem clearly enough.
     >> I have a DataTable that is FILL'ed from Database#1. As the application is
     run, the data is changed, and the Update method is used to write the changes
     back to Database #1.
     >> At some point, I want to write the contents of this DataTable to
     Database#2. The initial contents of this table in Database#2 is empty. At
     the time that I want to do this, all the rows in my table have had
     AcceptChanges called on them.
     >> If I open a DataConnection to Database#2, create a DataAdapter, and call
     its Update method as you suggest, nothing happens because none of my table's
     rows are in the Added state.
     >> What is the best way to copy this data to Database#2?
     >> Thanks...
     >> Dave