RE: Q: Datatables, Datasets and updating

The problem seems to be that you have defined Primary Key to mean Identity
Column. Your question demonstrates one of the multitude of reasons why
identity columns are not generally suitable as primary keys. If your primary
keys were real attributes of your data, for instance SSN or "Orange" or
something like that, then you could always identify which item from table B
belongs to which item in table B.

You might like some of the heated debate on the subject on my blog at
http://www.dalepreston.com/Blog/2005/08/identity-crisis.html.

If you really must use an artificial key for your primary key, then you will
have to handle the database update yourself, inserting a row from table A,
returning SCOPE_IDENTITY after each insert, and then inserting the child rows
from table B substituting the temporary key you used in the DataSet with the
value of SCOPE_IDENTITY.

Then you can update the data in your DataSet with the SCOPE_IDENTITY value
to keep everything synchronized or just read the data back from the database
to update the DataSet.

HTH
--
Dale Preston
MCAD C#
MCSE, MCDBA


"Geoff" wrote:

> 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.
>
> Suppose I load these tables into a DataSet. I populate table A with some
> rows. For each of these rows I create some rows in B which are linked by the
> foreign key to A i.e. there is a one to many relationship between A and B.
> Once this is done, I update the DataSet via a DataAdaptor such that these
> tables are transferred to the SQL database.
>
> My question is this. As far as I can see, the values in the primary key of A
> and the corresponding keys in B will not necessarily be the same in the
> DataSet and the SQL database! Do you see what I mean? I can't see how the
> relationship specified is still valid after the Update.
>
> Can anybody explain this to me?
>
> Thanks in advance
>
> Geoff
>
>
>
>
.

Relevant Pages

  • Re: Q: Datatables, Datasets and updating
    ... I have two tables A and B in the SQL database. ... ID_A (the primary key of A) and NAME_A (a string ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Q: Datatables, Datasets and updating
    ... the automatic updating features of a DataSet and DataAdapter. ... > transfer the data inputted into the SQL database. ... >> The problem seems to be that you have defined Primary Key to mean Identity ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Updating the SQL key value
    ... before creating the database, by the time I've come to pick a primary key, ... articles, each of which must be issued under a particular licence (e.g. ... GPL GNU General Public Licence http://www.gnu ... ...
    (comp.lang.php)
  • Re: Concatenate and Null Values -- Features
    ... Tony Toews dislikes cascade deletes as well as cascade updates, ... fence regarding the use of natural versus surrogate (autonumber) keys. ... Database Normalization Tips ... For optimal database design and performance, the primary key of a table ...
    (microsoft.public.access.reports)