SQL Server 2000 / Dataset /Relations Updating

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 ?



--
Best Regards

The Inimitable Mr Newbie º¿º

re: SQL Server 2000 / Dataset /Relations Updating

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" <here@now.com> schreef in bericht
news:eensDXs6FHA.252@TK2MSFTNGP15.phx.gbl...[color=blue]
> 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 ?
>
>
>
> --
> Best Regards
>
> The Inimitable Mr Newbie º¿º
>[/color]

re: SQL Server 2000 / Dataset /Relations Updating

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 [MVP]" <notmyfirstname@planet.nl> wrote in message
news:uk%23G7es6FHA.4012@TK2MSFTNGP14.phx.gbl...[color=blue]
> 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" <here@now.com> schreef in bericht
> news:eensDXs6FHA.252@TK2MSFTNGP15.phx.gbl...[color=green]
>> 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 ?
>>
>>
>>
>> --
>> Best Regards
>>
>> The Inimitable Mr Newbie º¿º
>>[/color]
>
>[/color]

re: SQL Server 2000 / Dataset /Relations Updating

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" <here@now.com> wrote in message
news:e3vEsls6FHA.4012@TK2MSFTNGP14.phx.gbl...[color=blue]
> 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 [MVP]" <notmyfirstname@planet.nl> wrote in message
> news:uk%23G7es6FHA.4012@TK2MSFTNGP14.phx.gbl...[color=green]
>> 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" <here@now.com> schreef in bericht
>> news:eensDXs6FHA.252@TK2MSFTNGP15.phx.gbl...[color=darkred]
>>> 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 ?
>>>
>>>
>>>
>>> --
>>> Best Regards
>>>
>>> The Inimitable Mr Newbie º¿º
>>>[/color]
>>
>>[/color]
>
>[/color]

re: SQL Server 2000 / Dataset /Relations Updating

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" wrote:
[color=blue]
> 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" <here@now.com> wrote in message
> news:e3vEsls6FHA.4012@TK2MSFTNGP14.phx.gbl...[color=green]
> > 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 [MVP]" <notmyfirstname@planet.nl> wrote in message
> > news:uk%23G7es6FHA.4012@TK2MSFTNGP14.phx.gbl...[color=darkred]
> >> 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" <here@now.com> schreef in bericht
> >> news:eensDXs6FHA.252@TK2MSFTNGP15.phx.gbl...
> >>> 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 ?
> >>>
> >>>
> >>>
> >>> --
> >>> Best Regards
> >>>
> >>> The Inimitable Mr Newbie º¿º
> >>>
> >>
> >>[/color]
> >
> >[/color]
>
>
>[/color]

re: SQL Server 2000 / Dataset /Relations Updating

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.


Mr Newbie wrote:[color=blue]
> 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 ?
>
>
>
> --
> Best Regards
>
> The Inimitable Mr Newbie º¿º[/color]

re: SQL Server 2000 / Dataset /Relations Updating

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

re: SQL Server 2000 / Dataset /Relations Updating

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



"Cor Ligthert [MVP]" wrote:
[color=blue]
> 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
>
>
>[/color]

re: SQL Server 2000 / Dataset /Relations Updating

[color=blue]
> 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.[/color]

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" <someone@nospam.com> schreef in bericht
news:%2396AY$s6FHA.2576@TK2MSFTNGP09.phx.gbl...[color=blue]
>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" <here@now.com> wrote in message
> news:e3vEsls6FHA.4012@TK2MSFTNGP14.phx.gbl...[color=green]
>> 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 [MVP]" <notmyfirstname@planet.nl> wrote in message
>> news:uk%23G7es6FHA.4012@TK2MSFTNGP14.phx.gbl...[color=darkred]
>>> 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" <here@now.com> schreef in bericht
>>> news:eensDXs6FHA.252@TK2MSFTNGP15.phx.gbl...
>>>> 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 ?
>>>>
>>>>
>>>>
>>>> --
>>>> Best Regards
>>>>
>>>> The Inimitable Mr Newbie º¿º
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

re: SQL Server 2000 / Dataset /Relations Updating

Kerry,
[color=blue]
>
> I knew you would respond to my remarks on GUIDs if you saw them, so I
> should
> have placed a bet on it!
>[/color]

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