SQL Server 2000 / Dataset /Relations Updating

  • Thread starter Thread starter Mr Newbie
  • Start date Start date
M

Mr Newbie

HI People,

Thanks to all who helped me earlier on the subject of @@IDentity. However, I
seem to have hit another snagette!

My DataSet contains two tables from the SQL Server. lets say Master and
Details. The way I have it set up is that a relationship exists in the
DataSet between the two to force referential integrety etc.

I am now able to a master record in the master table and add records to the
details table.

When I come to update, the server does return the @@IDentity and the record
in the dataset is updated accorningly.

However, the data in the details table maintains its -1,-2,-3 numers etc.
and this will not update and create in the SQL Server.

Is this the wrong approach, it there a better cleaner approach ?
 
Mr Newbie,

I did not want to write this anymore, however the posibilitiy that you
insert with a datatable 200 records is obvious. You get back the last
identity, and what if another one has in the same time as well 200 rows to
the same table with an autoincrement key.

Your seed that you use is normal starting at -1 with a set up by -1 too, to
get all the new idenitys back you have to do a complete new fill, however
which was it and are you sure that somebody did insert more rows. I never
went any further in this problematic with the autoincrement key in releation
to a datatable (with a simple single row insert it is of course easy).

This assuming that a primary key from your details is really a complete
primary key field and not made as sub part from your master key (but than it
is even easier to number it yourself).

You have already my answer what is probably easier.

:-)

Cor
 
No Cor, I take your point.

I think you have misundertood my problem. Perhaps I can ask the question
more simply !

(((((LOCAL DATASET))))
MASTER <-------->[ RELATION ] <-----> DETAILS

"I do the following"

1.) Create a master record in the dataset ( Initially uses the -1 for the
ID )
2.) Create detail records which their foreign keys are all set to -1
3.) Update the SQL Database for the Master Table Only ( All works OK ID's
are correct in the Master Table,)

At this point I thought that because of the cascading relation, the child
records should update, they do not !?!

Do I have to create child rows in the Details table in a particular way ?

--
Best Regards

The Inimitable Mr Newbie º¿º
 
I am not sure if datasets support cascading relations like that. I mean,
let's say you have 10 master rows in a dataset, and 20 child rows. You just
inserted all of them. How would the dataset know which child rows belong to
which master rows? I don't think it could. So even if you have just 1
master row, you could say you expect the dataset to assume all the child
rows belong to it, but I don't think it works like that.

I recommend you use GUIDs for your primary key, which would mean you can
assign those in your dataset, right as you are inserting the rows, and not
worry about any of this.

Mr Newbie said:
No Cor, I take your point.

I think you have misundertood my problem. Perhaps I can ask the question
more simply !

(((((LOCAL DATASET))))
MASTER <-------->[ RELATION ] <-----> DETAILS

"I do the following"

1.) Create a master record in the dataset ( Initially uses the -1 for the
ID )
2.) Create detail records which their foreign keys are all set to -1
3.) Update the SQL Database for the Master Table Only ( All works OK
ID's are correct in the Master Table,)

At this point I thought that because of the cascading relation, the child
records should update, they do not !?!

Do I have to create child rows in the Details table in a particular way ?

--
Best Regards

The Inimitable Mr Newbie º¿º

----------------------------------------------------


Cor Ligthert said:
Mr Newbie,

I did not want to write this anymore, however the posibilitiy that you
insert with a datatable 200 records is obvious. You get back the last
identity, and what if another one has in the same time as well 200 rows
to the same table with an autoincrement key.

Your seed that you use is normal starting at -1 with a set up by -1 too,
to get all the new idenitys back you have to do a complete new fill,
however which was it and are you sure that somebody did insert more rows.
I never went any further in this problematic with the autoincrement key
in releation to a datatable (with a simple single row insert it is of
course easy).

This assuming that a primary key from your details is really a complete
primary key field and not made as sub part from your master key (but than
it is even easier to number it yourself).

You have already my answer what is probably easier.

:-)

Cor
Click to expand...
Click to expand...
 
I recommend you use GUIDs for your primary key, which would mean you can
assign those in your dataset, right as you are inserting the rows, and not
worry about any of this.
Click to expand...

aaahrghhhhh :-(

and now the data integrity depends to the inserting program !!! ,,,, do
you have a Reall DBA at your company ? well you would be his worst
nightmare

Data integrity should be taken care off in the database ,


so the aproach is totally wrong , from a DB perspective ( post this in the
sql group and you will be thrown in tar and feathers ) :-)


regards

Michel Posseth


Marina said:
I am not sure if datasets support cascading relations like that. I mean,
let's say you have 10 master rows in a dataset, and 20 child rows. You
just inserted all of them. How would the dataset know which child rows
belong to which master rows? I don't think it could. So even if you have
just 1 master row, you could say you expect the dataset to assume all the
child rows belong to it, but I don't think it works like that.

I recommend you use GUIDs for your primary key, which would mean you can
assign those in your dataset, right as you are inserting the rows, and not
worry about any of this.

Mr Newbie said:
No Cor, I take your point.

I think you have misundertood my problem. Perhaps I can ask the question
more simply !

(((((LOCAL DATASET))))
MASTER <-------->[ RELATION ] <-----> DETAILS

"I do the following"

1.) Create a master record in the dataset ( Initially uses the -1 for the
ID )
2.) Create detail records which their foreign keys are all set to -1
3.) Update the SQL Database for the Master Table Only ( All works OK
ID's are correct in the Master Table,)

At this point I thought that because of the cascading relation, the child
records should update, they do not !?!

Do I have to create child rows in the Details table in a particular way ?

--
Best Regards

The Inimitable Mr Newbie º¿º

----------------------------------------------------


Cor Ligthert said:
Mr Newbie,

I did not want to write this anymore, however the posibilitiy that you
insert with a datatable 200 records is obvious. You get back the last
identity, and what if another one has in the same time as well 200 rows
to the same table with an autoincrement key.

Your seed that you use is normal starting at -1 with a set up by -1 too,
to get all the new idenitys back you have to do a complete new fill,
however which was it and are you sure that somebody did insert more
rows. I never went any further in this problematic with the
autoincrement key in releation to a datatable (with a simple single row
insert it is of course easy).

This assuming that a primary key from your details is really a complete
primary key field and not made as sub part from your master key (but
than it is even easier to number it yourself).

You have already my answer what is probably easier.

:-)

Cor

"Mr Newbie" <[email protected]> schreef in bericht
HI People,

Thanks to all who helped me earlier on the subject of @@IDentity.
However, I seem to have hit another snagette!

My DataSet contains two tables from the SQL Server. lets say Master and
Details. The way I have it set up is that a relationship exists in the
DataSet between the two to force referential integrety etc.

I am now able to a master record in the master table and add records to
the details table.

When I come to update, the server does return the @@IDentity and the
record in the dataset is updated accorningly.

However, the data in the details table maintains its -1,-2,-3 numers
etc. and this will not update and create in the SQL Server.

Is this the wrong approach, it there a better cleaner approach ?
Click to expand...
Click to expand...
Click to expand...
 
Marina,

I have 2 problems with using a GUID as a primary key instead of letting the
database generate the key:

1. A GUID is not guaranteed to be unique, but a database-generated IDENTITY
or AUTONUMBER value is guaranteed to be unique within the database.

2. Using a GUID, only data from the application that generates the GUID can
be entered into the database. But it is often necessary that data be entered
from a wide variety of sources, from bulk inserts, CSV data from another
system, by the DBA through database utilities, etc.

Kerry Moorman


Marina said:
I am not sure if datasets support cascading relations like that. I mean,
let's say you have 10 master rows in a dataset, and 20 child rows. You just
inserted all of them. How would the dataset know which child rows belong to
which master rows? I don't think it could. So even if you have just 1
master row, you could say you expect the dataset to assume all the child
rows belong to it, but I don't think it works like that.

I recommend you use GUIDs for your primary key, which would mean you can
assign those in your dataset, right as you are inserting the rows, and not
worry about any of this.

Mr Newbie said:
No Cor, I take your point.

I think you have misundertood my problem. Perhaps I can ask the question
more simply !

(((((LOCAL DATASET))))
MASTER <-------->[ RELATION ] <-----> DETAILS

"I do the following"

1.) Create a master record in the dataset ( Initially uses the -1 for the
ID )
2.) Create detail records which their foreign keys are all set to -1
3.) Update the SQL Database for the Master Table Only ( All works OK
ID's are correct in the Master Table,)

At this point I thought that because of the cascading relation, the child
records should update, they do not !?!

Do I have to create child rows in the Details table in a particular way ?

--
Best Regards

The Inimitable Mr Newbie º¿º

----------------------------------------------------


Cor Ligthert said:
Mr Newbie,

I did not want to write this anymore, however the posibilitiy that you
insert with a datatable 200 records is obvious. You get back the last
identity, and what if another one has in the same time as well 200 rows
to the same table with an autoincrement key.

Your seed that you use is normal starting at -1 with a set up by -1 too,
to get all the new idenitys back you have to do a complete new fill,
however which was it and are you sure that somebody did insert more rows.
I never went any further in this problematic with the autoincrement key
in releation to a datatable (with a simple single row insert it is of
course easy).

This assuming that a primary key from your details is really a complete
primary key field and not made as sub part from your master key (but than
it is even easier to number it yourself).

You have already my answer what is probably easier.

:-)

Cor

"Mr Newbie" <[email protected]> schreef in bericht
HI People,

Thanks to all who helped me earlier on the subject of @@IDentity.
However, I seem to have hit another snagette!

My DataSet contains two tables from the SQL Server. lets say Master and
Details. The way I have it set up is that a relationship exists in the
DataSet between the two to force referential integrety etc.

I am now able to a master record in the master table and add records to
the details table.

When I come to update, the server does return the @@IDentity and the
record in the dataset is updated accorningly.

However, the data in the details table maintains its -1,-2,-3 numers
etc. and this will not update and create in the SQL Server.

Is this the wrong approach, it there a better cleaner approach ?
Click to expand...
Click to expand...
Click to expand...
 
Make sure that in you Insert Command parameters collection you have the
source column for your identity field assigned to @RETURN_VALUE. I
have successfully done what you are attempting.
 
Kerry,

Do you know the change that a Guid will be not unique, it is probably a
lower change than that you will win all lotteries in the world in one day,
which is not guaranteed that it will not happen.

I hope for you that it will happen, however I give you a low change.

Just my thought,

Cor
 
Cor,

I knew you would respond to my remarks on GUIDs if you saw them, so I should
have placed a bet on it!

I used to be a big believer in the near uniqueness of .Net GUIDs until I did
a bit of reading on the subject a few months ago. Now I don't have much faith
in them.

But in addition to avoiding GUIDs because of the uniqueness problem, I don't
like them for primary keys in database tables because it makes it difficult
to enter data into the table without going through the application that
creates the GUID.

But from reading many of your posts, I know that you feel that the benefits
outweigh the drawbacks. And that you are no fan of the database
auto-generating primary key values. It is often a pain dealing with
auto-generated key values, but on balance I prefer that to application GUIDs.

Kerry Moorman
 
Kerry,
I knew you would respond to my remarks on GUIDs if you saw them, so I
should
have placed a bet on it!
Click to expand...

LOL,

:-)

The rest is as well agreed and understand by me, as you have maybe seen in
other post from me. There is in my opinion never a "Best" method.

:-)

Cor
 
Back
Top