473,224 Members | 1,343 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Q: Datatables, Datasets and updating

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

Jan 28 '06 #1
4 2340
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...ty-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

Jan 28 '06 #2
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" <da******@nospam.nospam> wrote in message
news:5D**********************************@microsof t.com...
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...ty-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

Jan 29 '06 #3
You would have to create custom Update commands in your DataAdapter to handle
any concurrency issues. For example, if you're updating one field out of 12
in a table, you would include all eleven other fields in your where clause,
validating that they still had the original values.

But you are raising an important question. Most developers I know don't use
the automatic updating features of a DataSet and DataAdapter. Most I know
write their own update code.

And if you are referring to inserts only, where you created your "primary
key" value in table A and table B and now you are concerned that someone else
already put entries in the tables using the values you assigned your primary
key in your own app, it is the same thing as I mention ed the first time,
just a slightly different twist.
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Geoff" wrote:
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" <da******@nospam.nospam> wrote in message
news:5D**********************************@microsof t.com...
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...ty-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


Jan 30 '06 #4
Many thanks Dale

"Dale" <da******@nospam.nospam> wrote in message
news:0E**********************************@microsof t.com...
You would have to create custom Update commands in your DataAdapter to
handle
any concurrency issues. For example, if you're updating one field out of
12
in a table, you would include all eleven other fields in your where
clause,
validating that they still had the original values.

But you are raising an important question. Most developers I know don't
use
the automatic updating features of a DataSet and DataAdapter. Most I know
write their own update code.

And if you are referring to inserts only, where you created your "primary
key" value in table A and table B and now you are concerned that someone
else
already put entries in the tables using the values you assigned your
primary
key in your own app, it is the same thing as I mention ed the first time,
just a slightly different twist.
--
Dale Preston
MCAD C#
MCSE, MCDBA
"Geoff" wrote:
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" <da******@nospam.nospam> wrote in message
news:5D**********************************@microsof t.com...
> 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...ty-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
>>
>>
>>
>>


Jan 30 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
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...
2
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...
2
by: Z D | last post by:
Hello, I'm currently using Remoting (HTTP/Binary) to remote a simple object. Everything is working fine except for one function that returns an arraylist of datatables. When I call this...
5
by: Christopher Weaver | last post by:
I'm confused about the relationships between these objects. I've written this: DataSet dsTaskActivities = new DataSet("TA"); DataTable dtTask = dsTaskActivities.Tables.Add("Tasks"); DataTable...
1
by: psb | last post by:
I thought this was weird?? is this a bug in framework 1.0??? (1.0 is the version I am running against) --------------------------- dim dtAll as new datatable dim dtTmp as datatable dtTmp =...
4
by: John Dalberg | last post by:
I noticed the starterkits timetracker & issue tracker load data from a database into custom collections (arraylists) which bind to a datagrid. What are the advantages of using custom collections...
3
by: cj | last post by:
I've used datatables and datasets before. Datasets being able to hold more than one table and datatables being only one table. My mind keeps coming up with recordsets. I can't remember how they...
0
by: Teo | last post by:
Hey guys!! I am looking at a user friendly tutorial on how to use Dataadapters, datatables and datasets to fill datagrids, etc. I am confused on using them a lot. Now I mostly use Datareaders and...
0
by: kevin.jennings | last post by:
Hi! I'm an "old-school" programmer used to dealing with data one record at a time (using old RPG code like 'chain' and 'read' statements). I'm not used to dealing with huge chunks of data at one...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.