How do I copy access datatable from one dataset to another

Question

I have two datasets (ds1 and ds2). Table1 belongs to ds1. I'd like to copy it (and rename it) and add it to ds2. When I run my program, there is no errors, but there is no dt2 in database2.mdb.

DataTable dt1 =ds1.Table1;
DataTable dt2 = new DataTable();

dt2 = dt1.Copy();

ds2.Tables.Add(dt2);
ds2.AcceptChanges();

Kirtan Patel · Answer

Hi Friend it Will Still Work Beacuse when we call ds.Tables.Add() method it will add new table so it is not metter that out destination dataset is empty or not it will add new table with DataSet ds2 = new DataSet (); ds2.Tables.Add(ds.Tables[0].Copy()); // Just Add One Data Grid View to test that data have been Copied or Not dataGridView2.DataSource = ds2.Tables[0]; though if you still having problem dot hesitate to ask or mail me the Full code(with database ) in zip you are using at kirtan007@gmail.com Happy coding :)

Miodrag Grahovac · Answer

You could ignore Relacije.mdb, and everything connecting with it. It supposed to be destination database, but when I implemented code which suggested @kirten, there was "NewDabase.mdb", so I haven't changed name. The whole idea of project is that user chooses two datatables from busic.mdb (selected from listBox1, and copied to listBox2), and I have to make mew database which contains choosen tables named Table1, and Table2. It sounds not to complicated, but I am new in c#, and just have stacked. private void btnDabog_Click(object sender, EventArgs e) { DataSet busicDataSet = new DataSet(); DataSet relacijeDataSet1 = new DataSet(); con = new System.Data.OleDb.OleDbConnection(); con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:/busic.mdb"; con.Open(); MessageBox.Show("busic OPEN"); string sql = "SELECT * From Vatin_Vrsac"; da = new System.Data.OleDb.OleDbDataAdapter(sql, con); da.Fill(busicDataSet, "Vatin_Vrsac"); conR = new System.Data.OleDb.OleDbConnection(); conR.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:/Relacije.mdb"; conR.Open(); MessageBox.Show("Relacije OPEN"); string sqlR = "SELECT * From TableBla"; daR = new System.Data.OleDb.OleDbDataAdapter(sqlR, conR); daR.Fill(relacijeDataSet1, "TableBla"); DataTable copyTable1 = new DataTable(); copyTable1 = busicDataSet.Tables["Vatin_Vrsac"].Copy(); DataTable dt1 = busicDataSet.Tables["Vatin_Vrsac"]; DataTable dt2 = new DataTable(); dt2 = dt1.Copy(); relacijeDataSet1.Tables.Add(dt2); dataGridView1.DataSource = relacijeDataSet1.Tables[1]; relacijeDataSet1.AcceptChanges(); daR.Fill(relacijeDataSet1, "dt2"); //tip : Before Creating New File First Check if Database Exist by System.IO.File.Exsist() so there //will be no error CatalogClass cat = new CatalogClass(); cat.Create(@"Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:/NewDatabase.mdb"); //Create table in New Created Database OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:/NewDatabase.mdb"); connection.Open(); OleDbCommand olecom = new OleDbCommand("create table TestTable([Id] Number,[Username] Text,[Password] Text)", connection); //OleDbCommand olecom = new OleDbCommand("SELECT * INTO TestTable FROM Vatin_Vrsac", connection); //OleDbCommand olecom = new OleDbCommand("SELECT Table1 INTO Table1 IN 'NewDatabase.mdb' FROM Table1", connection); //OleDbCommand olecom = new OleDbCommand("SELECT * INTO TestTable In 'D:/NewDatabase.mdb' FROM TestTable", connection); olecom.ExecuteNonQuery(); // Iterate through each row and Write it in table /* foreach (DataRow r in ds2.Tables[0].Rows) { int id = Convert.ToInt32(r["id"]); string uname = r["Username"].ToString(); string pass = r["Password"].ToString(); olecom = new OleDbCommand("insert into TestTable([Id],[Username],[Password]) values(@Id,@Username,@Password)", connection); olecom.Parameters.AddWithValue("@Id", id); olecom.Parameters.AddWithValue("@Username", uname); olecom.Parameters.AddWithValue("@Password", pass); olecom.ExecuteNonQuery(); } */ //Close the Connection connection.Close(); con.Close(); con.Dispose(); conR.Close(); conR.Dispose(); }

Master Billa · Answer

Hi Could send you me your code here , i will work for and send you thank you

Miodrag Grahovac · Answer

@Master Bila and @Kirten, thank you very much for helping me, but I can't solve my problem because I'd like user to choose tables from database, so I don't know what are the columns names, and can't create table in that way. Should I kill myself? OleDbCommand olecom = new OleDbCommand("create table TestTable([Id] Number,[Username] Text,[Password] Text)", connection); This works like a charm, but instead of creating new Test Table, I need my copied table. I have tried SELECT INTO statement, but allways get errors.

Kirtan Patel · Answer

//Copy Dataset DataSet ds2 = new DataSet (); ds2.Tables.Add(ds.Tables[0].Copy()); //Create new Database File //tip : Before Creating New File First Check if Database Exist by System.IO.File.Exsist() so there //will be no error CatalogClass cat = new CatalogClass (); cat.Create( @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\NewDatabase.mdb" ); //Create table in New Created Database OleDbConnection connection = new OleDbConnection ( @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=D:\NewDatabase.mdb" ); connection.Open(); OleDbCommand olecom = new OleDbCommand ( "create table TestTable([Id] Number,[Username] Text,[Password] Text)" , connection); olecom.ExecuteNonQuery(); // Iterate through each row and Write it in table foreach ( DataRow r in ds2.Tables[0].Rows) { int id = Convert .ToInt32(r[ "id" ]); string uname = r[ "Username" ].ToString(); string pass = r[ "Password" ].ToString(); olecom = new OleDbCommand ( "insert into TestTable([Id],[Username],[Password]) values(@Id,@Username,@Password)" , connection); olecom.Parameters.AddWithValue( "@Id" , id); olecom.Parameters.AddWithValue( "@Username" , uname); olecom.Parameters.AddWithValue( "@Password" , pass); olecom.ExecuteNonQuery(); } //Close the Connection connection.Close();

Master Billa · Answer

Hi You need make your code one this way DataSet ds1 ; // your source dataset DataSet ds2; // this is your destination Note:Please create instance two both data set ds2.Merge(ds1.Tables[0]); //This is easy and cool thank you

Miodrag Grahovac · Answer

Thanks, this is perfect, but I still haven't my copied table in my new database. How could I attach it?

Kirtan Patel · Answer

Open a new Visual C# .NET console application. In Solution Explorer , right-click the References node and select Add Reference . On the COM tab, select Microsoft ADO Ext. 2.7 for DDL and Security , click Select to add it to the Selected Components , and then click OK . Creating Database File is Easy but after that addind Table is some long procedure then SQL Server Here is Code How you wil Create Access Database File Programatically .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: Consolas, "Courier New", Courier, Monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } using System; using ADOX; ADOX.CatalogClass cat = new ADOX.CatalogClass(); cat.Create( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=D:\AccessDB\NewMDB.mdb;" + "Jet OLEDB:Engine Type=5" ); cat = null ; MessageBox.Show("Database Created");

Kirtan Patel · Answer

Hi , Creating new Data Base file in Access is some cumbersome process while its not case with sql Server in Sql Server we can create database by just passing Query and Execute it by Giving Proper Credentials :) though friend i will let you know when i found somthing interesting short coding for that task also :)

Miodrag Grahovac · Answer

Thank you, I'll try it, but I have only hoped that there is some faster solution.

Kirtan Patel · Answer

Hi Miodrag Take a look at here for creating Access database . http://blog.jrpsoftware.com/PermaLink,guid,edf0af2e-838a-4781-9543-13b22dee0ae9.aspx Happy Coding :)

Miodrag Grahovac · Answer

Thank you Kirtan, it works!!! Could you, please, help me to solve the whole problem? How can I make database2.mdb from that new dataset (ds2)? I need something like: [code]con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=D:/database2.mdb";[/code]

Miodrag Grahovac · Answer

@ Master Billa When I am using that code, there is error message: Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} DataTable already belongs to another DataSet , so I tried to use copy method: ds2 . Tables . Add(ds1 . Tables[0].Copy()); but, still nothing is copied to my destination database.

Miodrag Grahovac · Answer

@ Kirtan I have problem, because ds2 is still empty dataset. How can I use table that doesn't exist?

Master Billa · Answer

Hi I think , you can do in easy way Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;} DataSet ds1; DataSet ds2; ds2 . Tables . Add(ds1 . Tables[0]); // this is add first table to ds2 table collection. thank you

Kirtan Patel · Answer

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: Consolas, "Courier New", Courier, Monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Hi its lil bit tricky then normally copy datatable to dataset ,Here is solution to your problem . Most of code commented so you cn understand it better :) DataSet dsSource = new DataSet(); DataSet dsDestination = new DataSet(); //Give table Name You are Copying its Important dsSource.Tables[0].TableName = "NewTableName" ; dsDestination.Tables.Add(dsSource.Tables[0].Copy()); dsDestination.Tables[0].Tablename = "SomeTable" ; //If you want to rename it Here dsDestination.Tables[1].Tablename = "NewTableName" ;

How do I copy access datatable from one dataset to another

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (16)