471,315 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,315 software developers and data experts.

DataSet in new table of a Access database

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
Dec 31 '05 #1
8 1904
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Marcel Hug" <ma**********@ch.abb.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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

Dec 31 '05 #2
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.


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();
}
}
Dec 31 '05 #3
Marcel,

You have to be a little more specific when you say it doesn't work.
What exactly happens?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Marcel Hug" <ma**********@ch.abb.com> wrote in message
news:Od**************@TK2MSFTNGP09.phx.gbl...
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.


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();
}
}

Dec 31 '05 #4
Nicholas
You have to be a little more specific when you say it doesn't work.
What exactly happens?


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
Dec 31 '05 #5
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 Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Marcel Hug" <ma**********@ch.abb.com> wrote in message
news:uN**************@TK2MSFTNGP12.phx.gbl...
Nicholas
You have to be a little more specific when you say it doesn't work.
What exactly happens?


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

Dec 31 '05 #6
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.


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
Jan 1 '06 #7
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 Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Marcel Hug" <ma**********@ch.abb.com> wrote in message
news:eE**************@tk2msftngp13.phx.gbl...
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.


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

Jan 1 '06 #8
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
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).

Jan 1 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Simon Harvey | last post: by
1 post views Thread by Jeroen Pot | last post: by
1 post views Thread by Arpan | last post: by
13 posts views Thread by Lars Netzel | last post: by
2 posts views Thread by Carl Summers | last post: by
3 posts views Thread by don | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.