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
4  2373 
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
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******@nospa m.nospam> wrote in message
news:5D******** *************** ***********@mic rosoft.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
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******@nospa m.nospam> wrote in message
news:5D******** *************** ***********@mic rosoft.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
Many thanks Dale
"Dale" <da******@nospa m.nospam> wrote in message
news:0E******** *************** ***********@mic rosoft.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******@nospa m.nospam> wrote in message
news:5D******** *************** ***********@mic rosoft.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
>>
>>
>>
>>
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: 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 function, nothing is returned (ie length of array =0).
However, if I comment out my config file so that the DLL is run locally
instead of remoting it, everything works fine!!
|
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 dtActivity = dsTaskActivities.Tables.Add("Activity");
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = odbcIB;
cmd.CommandText = "SELECT * FROM \"tblTasks\"";
odbcDA_TaskActivities.SelectCommand = cmd;
|
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 = LoadDataTable("select top 100 from table...")
for i=0 to dtTmp.rows.count -1
''''ERROR ON THIS LINE'''
dtAll.Rows.Add(dtTmp.rows(i))
| | |
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 over simpler objects
like datareaders or datatables?
John Dalberg
|
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 fit into the picture.
I'm going to be reading some records from a table in a sql db.
|
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
Nonqueries to enter and retrieve data in and out of DB.
Please help.
Thanks,
Teo
|
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
time. Nowadays, however, it seems that the modern languages like Java
and C# steer you towards working with data in a 'disconnected'
fashion, i.e. loading records into datatables and datasets then using
dataviews to look at them how you like.
...
|
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: 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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
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...
| | |
