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.Tabl es["gnwestcustomer s"].Columns["Customer Number"];
DataColumn childColumn = accessdata.Tabl es["Table"].Columns["Customer
Number"];
DataRelation relExcel;
relExcel = new DataRelation("R elateExcel", parentColumn, childColumn);
accessdata.Rela tions.Add(relEx cel);
*************** *************** *************** *************** *************** *************** *************** *************** ***************
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. 3 3065
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 wrote:
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.Tabl es["gnwestcustomer s"].Columns["Customer Number"];
DataColumn childColumn = accessdata.Tabl es["Table"].Columns["Customer
Number"];
DataRelation relExcel;
relExcel = new DataRelation("R elateExcel", parentColumn, childColumn);
accessdata.Rela tions.Add(relEx cel);
*************** *************** *************** *************** *************** *************** *************** *************** ***************
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.
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.
"ssamuel" <ss*****@gmail. comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .
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 wrote:
>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.Tab les["gnwestcustomer s"].Columns["Customer Number"];
DataColumn childColumn = accessdata.Tabl es["Table"].Columns["Customer Number"];
DataRelation relExcel;
relExcel = new DataRelation("R elateExcel", parentColumn, childColumn);
accessdata.Rel ations.Add(relE xcel); ************** *************** *************** *************** *************** *************** *************** *************** *************** *
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.
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 OleDbDataAdapte r, with OleDbCommandBui lder 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 GetInsertComman d for OleDbCommand builder.
..) called OleDbDataAdapte r.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.
"don" <do********@sha w.cawrote in message
news:huM6h.3183 21$1T2.75467@pd 7urf2no...
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.
"ssamuel" <ss*****@gmail. comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .
>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 wrote:
>>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.Ta bles["gnwestcustomer s"].Columns["Customer Number"];
DataColumn childColumn = accessdata.Tabl es["Table"].Columns["Customer Number"];
DataRelatio n relExcel;
relExcel = new DataRelation("R elateExcel", parentColumn, childColumn);
accessdata.Re lations.Add(rel Excel); ************* *************** *************** *************** *************** *************** *************** *************** *************** **
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: William Ryan |
last post by:
At the risk of sounding like a Big 5 consultant, "It
depends".
1) Strongly typed datasets rock, they are faster than
untyped, use intellisense... but your reason for
wanting to use them is important. I use them every day
of my life, but I also don't use them every day of my
life and there are reasons for both.
2) How much of your process is dependent on reads vs.
|
by: Jade |
last post by:
Hi,
I just wanted to ask a quick question regarding datasets.
I am creating 3 tables using a dataadapter. what i want
to know is that is the relationship created between these
datatables automatically?? Will the integrity rules
automatically be enforced or do i need to recode this in
vb.net?
Also do the datatables created have the same schema as my
|
by: Scott Tenorman |
last post by:
I am trying to "join" two existing DataTable objects based on related
information without having to request the data from SQL again. Lets
just say the DataTables are contained in a single DataSet. I am
struggling with the manipulation of data once it is all contained
within tables inside a dataset. I know a dataset has many features to
access data but I am finding them very troublesome.
|
by: Gene Ariani |
last post by:
I have one dataset that contains two DataTables:
DataTable1:
Customer_ID
1
|
by: DraguVaso |
last post by:
Hi,
For my VB.NET application I have the following situation:
2 tables on my SQL Server: tblAccounts and tblRules. For each Account there
are many Rules (so tblRules is linked to my tblAccounts by the Account). In
the tblAccounts thee is a field Company which occurs many times (there is
more than one Account for each Company:).
Whet I want to do on my Fom is this: I have a combobox with all my company's
in it. When I choce a Company...
| |
by: Geoff |
last post by:
Hi
I'm hoping somebody can help me with the following problem that has occurred
to me.
Suppose I have two tables in an SQL Server database. Let's call these tables
A and B. Assume that A has two fields: a primary key and another holding a
string. In table B there are three fields: a primary key, a foreign key
(which links to the primary key in A) and other field holding a string.
|
by: hangdee |
last post by:
I have 2 datatables (get from database) inside a dataset.
How can I create another datatable which is a result of
Select tb1.xxx, tb2.yyy from table1 as tb1 left join table2 as tb2 on tb1.id
= tb2.t1id
Is there any sample code for it?
|
by: =?Utf-8?B?UHVjY2E=?= |
last post by:
Hi, I'm using vs2005 and .net 2.0
I used 2 DirectorySearchers to retrieve data from Active Directory and
placed them in 2 dataset tables. I need to perform the SQL equuvakebt of
join operation to create a 3rd dataset table that I will then pass to Crystal
Report.
1. Sometimes there could be 100,000 records or more per table. Is this the
most time efficient way to do it?
2. I tried using the datarelation and looping through each...
|
by: StefanPienaar |
last post by:
Hi Guys
Is there any way in c# (or vb.net) to extract a datatable of data from a dataset with multiple datatables which has relationships set up (containing combined data from the datatables)?
I currently have 2 datatables in the dataset. The first one contains the header records with columns such as Id, DateCreated, DateModified, etc
The second datatable contains the detail records and has columns such as Id, BatchId (which links to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |