Join DataTables in same dataset

[don]

I have written a program to hold a sales contact db. It is written in c#
express.

an update is sent from head office in xl format, i need to import only the
new rows to an access database.

I have gotten this far:

a separate OleDbconnection to each source, which reads the entire data
source (select *) into the same dataset, but different tables.

from here i want to be able to pull the new rows from the xl table and
append it to the access table.

When I try to create a relation between the two table it says "This
constraint cannot be enabled as not all values have corresponding parent
values."
*******************************************************************************************************************
DataColumn parentColumn =
accessdata.Tables["gnwestcustomers"].Columns["Customer Number"];

DataColumn childColumn = accessdata.Tables["Table"].Columns["Customer
Number"];

DataRelation relExcel;

relExcel = new DataRelation("RelateExcel", parentColumn, childColumn);


accessdata.Relations.Add(relExcel);



***************************************************************************************************************************************

I can do this all in MSAccess easily, but the user needs to be able to do it
in the field....come on people sales person here.......not technically
enabled in the computer department if you know what i mean...



any help appreciated.



Thanks in advance.

 

[ssamuel]

Don,

As far as the framework is concerned, any two given DataTable objects
have different schemas. Even if they look the same to you, they're not
to the framework.

Your best bet here is probably to Merge() one DataTable into the other.
The Merge() process will deal with the issues like, "what if the
schemas are different," or, "what if the columns are in a different
order," and give you fine-grained control of how to deal with that. If
your assumption that the schemas are identical is correct, you'll get
no exceptions.

Once the data is merged, you'll have to separate what goes where. It
may be useful to add a source column (pre-merge) to each table that
contains a static value based on where it came from. Then it's much
easier to un-munge later.


Stephan

 

[don]

Thanks for the reply, I won't need to "unmerge" the data later. The Excel
sheet has all the contacts that currently reside in the DB plus new ones.
All I want to do is take the new customers and append to the database in
access.

Seems simple I want to take data from a DataTable and put it into an
existing access table. Even if i do merge the data in the c# project i
still have to get the dataset to update back to the database.

Don,

As far as the framework is concerned, any two given DataTable objects
have different schemas. Even if they look the same to you, they're not
to the framework.

Your best bet here is probably to Merge() one DataTable into the other.
The Merge() process will deal with the issues like, "what if the
schemas are different," or, "what if the columns are in a different
order," and give you fine-grained control of how to deal with that. If
your assumption that the schemas are identical is correct, you'll get
no exceptions.

Once the data is merged, you'll have to separate what goes where. It
may be useful to add a source column (pre-merge) to each table that
contains a static value based on where it came from. Then it's much
easier to un-munge later.


Stephan

I have written a program to hold a sales contact db. It is written in c#
express.

an update is sent from head office in xl format, i need to import only
the
new rows to an access database.

I have gotten this far:

a separate OleDbconnection to each source, which reads the entire data
source (select *) into the same dataset, but different tables.

from here i want to be able to pull the new rows from the xl table and
append it to the access table.

When I try to create a relation between the two table it says "This
constraint cannot be enabled as not all values have corresponding parent
values."
*******************************************************************************************************************
DataColumn parentColumn =
accessdata.Tables["gnwestcustomers"].Columns["Customer Number"];

DataColumn childColumn = accessdata.Tables["Table"].Columns["Customer
Number"];

DataRelation relExcel;

relExcel = new DataRelation("RelateExcel", parentColumn, childColumn);


accessdata.Relations.Add(relExcel);



***************************************************************************************************************************************

I can do this all in MSAccess easily, but the user needs to be able to do
it
in the field....come on people sales person here.......not technically
enabled in the computer department if you know what i mean...



any help appreciated.



Thanks in advance.

 

[don]

I figured it out:

I needed to import an Excel spreadsheet into an access database. Here is
what i did:

..) create OleDbConnection to the Access DataBase.
..) used OleDbCommand to create a copy of the "customers" table in the access
DB.
..) used another OleDbCommand to delete the contents of the table in the DB.
..) create OleDbDataAdapter, with OleDbCommandBuilder associated to it,
Selected the empty table into a DataTable called 'blankTable'.
..) create OleDbConnection to the Excel file and copied contents to
'blankTable'.
..) run a foreach loop using DataRow, to change the RowState of each row in
'blankTable' to "Added".
..) called the GetInsertCommand for OleDbCommand builder.
..) called OleDbDataAdapter.update.

All rows in the DataTable are seen as "Added" and are therefore copied back
to the blank table in the database.
From there simply combined a SELECT INTO statement with LEFT JOIN to grab
the records that were unmatched in the original table and append them.

Thanks for the reply, I won't need to "unmerge" the data later. The
Excel sheet has all the contacts that currently reside in the DB plus new
ones. All I want to do is take the new customers and append to the
database in access.

Seems simple I want to take data from a DataTable and put it into an
existing access table. Even if i do merge the data in the c# project i
still have to get the dataset to update back to the database.

Don,

As far as the framework is concerned, any two given DataTable objects
have different schemas. Even if they look the same to you, they're not
to the framework.

Your best bet here is probably to Merge() one DataTable into the other.
The Merge() process will deal with the issues like, "what if the
schemas are different," or, "what if the columns are in a different
order," and give you fine-grained control of how to deal with that. If
your assumption that the schemas are identical is correct, you'll get
no exceptions.

Once the data is merged, you'll have to separate what goes where. It
may be useful to add a source column (pre-merge) to each table that
contains a static value based on where it came from. Then it's much
easier to un-munge later.


Stephan

I have written a program to hold a sales contact db. It is written in
c#
express.

an update is sent from head office in xl format, i need to import only
the
new rows to an access database.

I have gotten this far:

a separate OleDbconnection to each source, which reads the entire data
source (select *) into the same dataset, but different tables.

from here i want to be able to pull the new rows from the xl table and
append it to the access table.

When I try to create a relation between the two table it says "This
constraint cannot be enabled as not all values have corresponding parent
values."
*******************************************************************************************************************
DataColumn parentColumn =
accessdata.Tables["gnwestcustomers"].Columns["Customer Number"];

DataColumn childColumn = accessdata.Tables["Table"].Columns["Customer
Number"];

DataRelation relExcel;

relExcel = new DataRelation("RelateExcel", parentColumn, childColumn);


accessdata.Relations.Add(relExcel);



***************************************************************************************************************************************

I can do this all in MSAccess easily, but the user needs to be able to
do it
in the field....come on people sales person here.......not technically
enabled in the computer department if you know what i mean...



any help appreciated.



Thanks in advance.