Re: Q: Datatables, Datasets and updating

Hi Dale

Err, I think I haven' explained myself too well. I'll try again:

I have two tables A and B in the SQL database.

Table A has two fields: ID_A (the primary key of A) and NAME_A (a string
field).

Table B has three fields: ID_B (the primary key of B), FK_A (a link to a
primary key value in A) and NAME_B (a string field).

I load these tables into a DataSet. I then add values to table A. I also add
values into B; where the values of FK_A in B have corresponding values in
ID_A.

I call the update event of the DataSet (or corresponding DataTables) to
transfer the data inputted into the SQL database.

As far as I can see, adding (updating) entries in both tables of the SQL
database create, NOT NECESSARILY THE SAME, values in the ID_A field e.g.
what if another user has added rows during the time the disconnected data
has been modified. So my question is, how do the values in ID_A and FK_A
have matching values AFTER they have been updated to the database.

I'm obviously missing something because this type of scenario is so likely
to happen in writting database applications.

However, just in case I have got the wrong end of the stick with your kind
response, I'd be interested to see some example code for the returing of
SCOPE_IDENTITY.

Many thanks in advance

Geoff

"Dale" <dale0973@xxxxxxxxxxxxx> wrote in message
news:5D4EEB1C-BFD0-40F9-943A-048A7FEBB3B5@xxxxxxxxxxxxxxxx
> 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: 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)
  • Re: Option groups, radio buttons and reports...oh my!!
    ... designing a 'questionnaire' database of using separate columns for the ... referencing the primary key of the Questions and Answers table. ... composite primary key of QuestionAnswers. ... a composite one of RespondentID and QuestionID; ...
    (microsoft.public.access.gettingstarted)