DataSet in new table of a Access database

  • Thread starter Thread starter Marcel Hug
  • Start date Start date
M

Marcel Hug

Hi all !
I have datas in a dataset. I would like to save them in a new table
(just created). Do I have to run trought all recordsets with an Insert
into statement ? (it takes very long)

Thanks
 
Marcel,

You can just create a OleDbDataAdapter with the insert statement
attached to the InsertCommand property. Then, you can just pass the data
set in and it should update the table.

Hope this helps.
 
Hi Nicholas
You can just create a OleDbDataAdapter with the insert statement
attached to the InsertCommand property. Then, you can just pass the data
set in and it should update the table.
Click to expand...

I tried this, but it does not work....! :-((((

public void CreateNewBackup(DataSet backupDataSet, BackupTable backupTable)
{
try
{
string strNewBackupTable = this.CreateNewBackupTable(backupTable);

if(this.dbConnection.State == ConnectionState.Closed)
{
this.dbConnection.Open();
}

OleDbCommand dbCommand = new OleDbCommand("INSERT INTO " +
strNewBackupTable + " (Entry, DateMain, Issued, LastUpdt, Modified,
Version, VersionText, VSTATUS) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
this.dbConnection);
dbCommand.Parameters.Add("Entry", "ENTRY");
dbCommand.Parameters.Add("DateMain", "DATEMAIN");
dbCommand.Parameters.Add("Issued", "Issued");
dbCommand.Parameters.Add("LastUpdt", "LASTUPDT");
dbCommand.Parameters.Add("Modified", "Modified");
dbCommand.Parameters.Add("Version", "VERSION");
dbCommand.Parameters.Add("VersionText", "VersionText");
dbCommand.Parameters.Add("VSTATUS", "VSTATUS");

this.dbAdapter.InsertCommand = dbCommand;
this.dbAdapter.TableMappings.Add(strNewBackupTable,
backupDataSet.Tables[0].TableName);
this.dbAdapter.Fill(backupDataSet);
}
finally
{
this.dbConnection.Close();
}
}
 
Marcel,

You have to be a little more specific when you say it doesn't work.
What exactly happens?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Marcel Hug said:
Hi Nicholas
You can just create a OleDbDataAdapter with the insert statement
attached to the InsertCommand property. Then, you can just pass the data
set in and it should update the table.
Click to expand...

I tried this, but it does not work....! :-((((

public void CreateNewBackup(DataSet backupDataSet, BackupTable
backupTable)
{
try
{
string strNewBackupTable = this.CreateNewBackupTable(backupTable);

if(this.dbConnection.State == ConnectionState.Closed)
{
this.dbConnection.Open();
}

OleDbCommand dbCommand = new OleDbCommand("INSERT INTO " +
strNewBackupTable + " (Entry, DateMain, Issued, LastUpdt, Modified,
Version, VersionText, VSTATUS) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
this.dbConnection);
dbCommand.Parameters.Add("Entry", "ENTRY");
dbCommand.Parameters.Add("DateMain", "DATEMAIN");
dbCommand.Parameters.Add("Issued", "Issued");
dbCommand.Parameters.Add("LastUpdt", "LASTUPDT");
dbCommand.Parameters.Add("Modified", "Modified");
dbCommand.Parameters.Add("Version", "VERSION");
dbCommand.Parameters.Add("VersionText", "VersionText");
dbCommand.Parameters.Add("VSTATUS", "VSTATUS");

this.dbAdapter.InsertCommand = dbCommand;
this.dbAdapter.TableMappings.Add(strNewBackupTable,
backupDataSet.Tables[0].TableName);
this.dbAdapter.Fill(backupDataSet);
}
finally
{
this.dbConnection.Close();
}
}
Click to expand...
 
Nicholas
You have to be a little more specific when you say it doesn't work.
What exactly happens?
Click to expand...

Hmm is a little bit difficult. After creating the new table exists. Then
i try to insert the datas of the dataset into the new table. in the
datatable of the dataset are more columns then in the new table. This i
would like to fit by the InsertCommand.
After the Update (or fill) command, the new table is still empty. No
exception or something.
That's why i think it is difficult...

Thanks and a happy new year
 
Marcel,

How are you getting the rows into the dataset in the first place? You
have to add them manually so that the data adapter sees that they have been
added.
 
Nicholas,
How are you getting the rows into the dataset in the first place? You
have to add them manually so that the data adapter sees that they have been
added.
Click to expand...

I have to databases (DB1 and DB2). The DB2 is my "backup" DB. I get the
datas from The DB1 with a OleDBDataAdapter to the Dataset and would like
to write some of them into the DB2 with an other OleDBAdapter.

What do you mean with "add manually" ? Is my solution not running ? What
do you suggest a best solution ?

Thanks + Happy New Year
 
Marcel,

Each data row in the dataset has a state associated (is it added,
edited, deleted, etc, etc). The data adapter looks at this state to
determine if it should update the row when cycling through the rows.

When you initially select the rows from the table, the status is
considered unchanged. If you pass this back to the adapter without doing
anything to it, it doesn't know the rows should be added.

In .NET 2.0, you can cycle through the rows in your data table, and call
the SetAdded method on them to set the flag for the state to added.

In .NET 1.1 and before, what you will have to do is create a copy of the
data table (an empty one), and manually add the rows to the new data table,
so that the added flag is set, and then pass that data table to the data
adatper (or the data set with the new data table).
 
Nicholas,

At frist lot of thanks for your help. Now it works ! :-)))
I have solved my problem like this.
First I have done a Select-Command of the new backup-table, to get a
empty datatable with the right schema.

then i went through my datas in the dataset and added them into the
created datatable.

After this i created a OleDbCommandBuilder to create the insert command
and then i updated the database with the datatable and this insert command.

This works fine and fast.

But now I have a little question.
Before I used the command builder, I wrote the SQL-Statement manually.
But with the manually created oledbcommand it was not working. also if I
took the same sql-statement.
But i can not explain why ????



Thanks
Regards

Marcel
 
Back
Top