Problem copying dataset from one Database to another

Problem copying dataset from one Database to another

• Nishant

  HI,

  This is happening because all rows of your DataSet/DataTable are marked unchaged thus the adapter doesn't do anything with it. You cannot manually change the Rowstate of the Rowso a workaround could be is to manually add the rows in your DataTable. YOu can achieve this by using a DataReader, if you do this all of your rows would be marked new and upon calling the update it would add it to your new database.

   

  cheers,

  Paul June A. Domag

  
  
  

• Merco

  Paul,

  i tried adding this to my code:

  DataSet ds1=new DataSet();

  DataSet ds=new DataSet();

  ds=getDataR(); //returns dataset

  DataTable empTable = ds1.Tables.Add("Employees");

  DataColumn pkCol = empTable.Columns.Add("EmployeeID", typeof(Int32));

  empTable.Columns.Add("LastName", typeof(string));

  empTable.Columns.Add("FirstName", typeof(string));

  empTable.PrimaryKey = new DataColumn[] {pkCol};

  for(int i=0;i<ds.Tables[0].Rows.Count;i++)

  {

  empTable.Rows.Add(ds.Tables[0].Rows.ItemArray);

  }

  da1.TableMappings.Add("Employees",ds1.Tables[0].TableName);

  da1.Update(ds1);

  But i get following error:

  Update enable to find TableMapping['Table'] or DataTable 'Table'

  
  

• johnpauth

  still the original error persists:

  Update enable to find TableMapping['Table'] or DataTable 'Table'

  
  

• 9_Phill

  Paul,

  i tried adding this to my code:

  DataSet ds1=new DataSet();

  DataSet ds=new DataSet();

  ds=getDataR(); //returns dataset

  DataTable empTable = ds1.Tables.Add("Employees");

  DataColumn pkCol = empTable.Columns.Add("EmployeeID", typeof(Int32));

  empTable.Columns.Add("LastName", typeof(string));

  empTable.Columns.Add("FirstName", typeof(string));

  empTable.PrimaryKey = new DataColumn[] {pkCol};

  for(int i=0;i<ds.Tables[0].Rows.Count;i++)

  {

  empTable.Rows.Add(ds.Tables[0].Rows.ItemArray);

  }

  da1.TableMappings.Add("Employees",ds1.Tables[0].TableName);

  da1.Update(ds1);

  But i get following error:

  Update enable to find TableMapping['Table'] or DataTable 'Table'

  
  

• Stonehaven

  Hi,

  Sorry, but I didn't really tried it. Kinda did it in my head. I don't have a VS2005 installation currently in this comp but I think this should work:

  DataRow dr;

  for(int i=0;i<ds.Tables[0].Rows.Count;i++)

  {

  dr = empTable.NewRow();

  dr["EmployeeID"] = ds.Tables[0].Rows [ i ] ["EmployeeID"];

  dr["LastName"] = ds.Tables[0].Rows[ i ]["LastName"];

  dr["FirstName"] = ds.Tables[0].Rows[ i ]["FirstName"];

  empTable.Rows.Add(dr);

  }

  da1.Update(ds1);

   

  cheers,

  Paul June A. Domag

  
  
  

• BennyT

  That one gave me compile error, so i modified it this way:

  for(int i=0;i<ds.Tables[0].Rows.Count;i++)

  {

  DataRow dr = empTable.NewRow();

  dr["EmployeeID"] = ds.Tables[0].Rows [ i ] ["EmployeeID"];

  dr["LastName"] = ds.Tables[0].Rows[ i ]["LastName"];

  dr["FirstName"] = ds.Tables[0].Rows[ i ]["FirstName"];

  empTable.Rows.Add(dr);

  }

  da1.Update(ds1);

  Still same Error    again inside the loop "dr" won't get overwritten

  
  

• Roger One

  Hi,

  Could you not use a Dataset Instead use a DataTable since were dealing with a single table. Also try naming the datatable upon creating:

  DataTable dt = new DataTable("Employees");

  Also, upon reviewing your code I couldn't seem to find any implementation of the INSERT command of the DataAdapter. Implement it if you want the newly added tables to be appended into your database.

  adapter.InsertCommand = new OleDbCommand("INSERT INTO Employee VALUES(@Name)", connection);
  adapter.InsertCommand.Parameters.Add("@Name", ...);

   

  cheers,

  Paul June A. Domag

  
  
  

• John Basedow

  Hi,

  Try modifying it this way:

  DataSet ds1=new DataSet();

  DataSet ds=new DataSet();

  ds=getDataR(); //returns dataset

  DataTable empTable = ds1.Tables.Add("Employees");

  DataColumn pkCol = empTable.Columns.Add("EmployeeID", typeof(Int32));

  empTable.Columns.Add("LastName", typeof(string));

  empTable.Columns.Add("FirstName", typeof(string));

  empTable.PrimaryKey = new DataColumn[] {pkCol};

  for(int i=0;i<ds.Tables[0].Rows.Count;i++)

  {

  DataRow dr = empTable.NewRow();
  dr["EmployeeId"] = ds.Tables[0]["EmployeeId"];
  // also give values to other fields
  empTable.Rows.Add(dr);

  }

  da1.Update(ds1);

  My code edit is inside the loop.

   

  cheers,

  Paul June A. Domag 

  
  
  

• Sasanka Pinidiya

  Paul Domag wrote:

  Hi, Could you not use a Dataset Instead use a DataTable since were dealing with a single table. Also try naming the datatable upon creating: DataTable dt = new DataTable("Employees");

  Thanks Paul,

  passing datatable works just fine then passing dataset. For time being my problem is solved. but still i wonder how to do it in case of multiple tables.

  Paul Domag wrote:

  Also, upon reviewing your code I couldn't seem to find any implementation of the INSERT command of the DataAdapter. Implement it if you want the newly added tables to be appended into your database. adapter.InsertCommand = new OleDbCommand("INSERT INTO Employee VALUES(@Name)", connection); adapter.InsertCommand.Parameters.Add("@Name", ...);  cheers, Paul June A. Domag

  for this i was using "SqlClient.SqlCommandBuilder cb = new SqlClient.SqlCommandBuilder(da1); "

  
  
• Problem copying dataset from one Database to another