copy dataset (origin oracle) to access database

[Jeroen Pot]

Hi,

I have a dataset "Mydataset" which contains of a few fields. (lets say
dept_id and dept_name)
A user can select several of these, and i want to export this to an access
database.

I have a database with one table (dept) and identical colums as the dataset.
But i can't figure out how to get the dataset into the database.

Here is what i've tried:

// This fill the dataset with records.
Uitwissel ldtsUitwissel =
(Uitwissel)selectie.GetSelectieExport(selectie.Use rnaam, i_count);
// to check my dataset i tried:
ldtsUitwissel.WriteXml("c:\myfile.xml");

// Get a new destination
lstrDestination = pstrPath + "\\Import\\" + Guid.NewGuid().ToString() +
".mdb";

// Pre made access database with existing table
string lstrSrc=pstrPath + "\\Import\\EmptyExchange.mdb";

// Copy the file
File.Copy(lstrSrc,lstrDestination,false);

OleDbConnection lodcConn = new OleDbConnection();
lodcConn.ConnectionString =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source={0}",
lstrDestination);
lodcConn.Open();

OleDbDataAdapter lodaAcces = new OleDbDataAdapter(@"Select dept_id,
dept_name from dept", lodcConn);

lodaAcces.InsertCommand = new OleDbCommand();
lodaAcces.InsertCommand.Connection = lodcConn;

lodaAcces.InsertCommand.CommandText = @"INSERT INTO Dept (dept_id,
dept_name) VALUES (?, ?)";
lodaAcces.InsertCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("dept_id",
System.Data.OleDb.OleDbType.Integer, 0, "dept_id"));
lodaAcces.InsertCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("dept_name",
System.Data.OleDb.OleDbType.VarWChar, 60, "dept_name"));

lodaAcces.TableMappings.AddRange(new System.Data.Common.DataTableMapping[]
{
new System.Data.Common.DataTableMapping("Table", "BeverExport", new
System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("dept_id", "dept_id"),
new System.Data.Common.DataColumnMapping("dept_name", "dept_name"),
})
});
lodaAcces.Update(ldtsUitwissel, "Dept");

if i open the access database i stil have an empty table dept
if i open the file c:\myfile.xml it's filled

Who can fix this? (or has an entire new idea to copy the dataset)

[William Ryan eMVP]

http://www.knowdotnet.com/articles/datasetmerge.html

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
"Jeroen Pot" <jm****@quicknet.nl> wrote in message
news:c4***************************@news.multikabel .nl...

Hi,

I have a dataset "Mydataset" which contains of a few fields. (lets say
dept_id and dept_name)
A user can select several of these, and i want to export this to an access
database.

I have a database with one table (dept) and identical colums as the dataset. But i can't figure out how to get the dataset into the database.

Here is what i've tried:

// This fill the dataset with records.
Uitwissel ldtsUitwissel =
(Uitwissel)selectie.GetSelectieExport(selectie.Use rnaam, i_count);
// to check my dataset i tried:
ldtsUitwissel.WriteXml("c:\myfile.xml");

// Get a new destination
lstrDestination = pstrPath + "\\Import\\" + Guid.NewGuid().ToString() +
".mdb";

// Pre made access database with existing table
string lstrSrc=pstrPath + "\\Import\\EmptyExchange.mdb";

// Copy the file
File.Copy(lstrSrc,lstrDestination,false);

OleDbConnection lodcConn = new OleDbConnection();
lodcConn.ConnectionString =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Da ta Source={0}",
lstrDestination);
lodcConn.Open();

OleDbDataAdapter lodaAcces = new OleDbDataAdapter(@"Select dept_id,
dept_name from dept", lodcConn);

lodaAcces.InsertCommand = new OleDbCommand();
lodaAcces.InsertCommand.Connection = lodcConn;

lodaAcces.InsertCommand.CommandText = @"INSERT INTO Dept (dept_id,
dept_name) VALUES (?, ?)";
lodaAcces.InsertCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("dept_id",
System.Data.OleDb.OleDbType.Integer, 0, "dept_id"));
lodaAcces.InsertCommand.Parameters.Add(new
System.Data.OleDb.OleDbParameter("dept_name",
System.Data.OleDb.OleDbType.VarWChar, 60, "dept_name"));

lodaAcces.TableMappings.AddRange(new System.Data.Common.DataTableMapping[]
{
new System.Data.Common.DataTableMapping("Table", "BeverExport", new
System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("dept_id", "dept_id"),
new System.Data.Common.DataColumnMapping("dept_name", "dept_name"),
})
});
lodaAcces.Update(ldtsUitwissel, "Dept");

if i open the access database i stil have an empty table dept
if i open the file c:\myfile.xml it's filled

Who can fix this? (or has an entire new idea to copy the dataset)