473,725 Members | 2,039 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 º¿º
Nov 23 '05 #1
9 1724
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" <he**@now.com > schreef in bericht
news:ee******** *****@TK2MSFTNG P15.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 º¿º

Nov 23 '05 #2
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]" <no************ @planet.nl> wrote in message
news:uk******** ********@TK2MSF TNGP14.phx.gbl. ..
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" <he**@now.com > schreef in bericht
news:ee******** *****@TK2MSFTNG P15.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 º¿º


Nov 23 '05 #3
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" <he**@now.com > wrote in message
news:e3******** ******@TK2MSFTN GP14.phx.gbl...
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]" <no************ @planet.nl> wrote in message
news:uk******** ********@TK2MSF TNGP14.phx.gbl. ..
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" <he**@now.com > schreef in bericht
news:ee******** *****@TK2MSFTNG P15.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 º¿º



Nov 23 '05 #4
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:
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" <he**@now.com > wrote in message
news:e3******** ******@TK2MSFTN GP14.phx.gbl...
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]" <no************ @planet.nl> wrote in message
news:uk******** ********@TK2MSF TNGP14.phx.gbl. ..
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" <he**@now.com > schreef in bericht
news:ee******** *****@TK2MSFTNG P15.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 º¿º



Nov 23 '05 #5
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:
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 º¿º


Nov 23 '05 #6
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
Nov 23 '05 #7
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:
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

Nov 23 '05 #8
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.
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" <so*****@nospam .com> schreef in bericht
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..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" <he**@now.com > wrote in message
news:e3******** ******@TK2MSFTN GP14.phx.gbl...
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]" <no************ @planet.nl> wrote in message
news:uk******** ********@TK2MSF TNGP14.phx.gbl. ..
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" <he**@now.com > schreef in bericht
news:ee******** *****@TK2MSFTNG P15.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 º¿º



Nov 23 '05 #9
Kerry,

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


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
Nov 23 '05 #10

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

Similar topics

0
1603
by: Joe Sullivan | last post by:
Using C# in Visual Studios .NET, I have created a Dataset with 2 tables (created from an SQL Data Adapter). These 2 tables are loaded from 2 views in SQL Server 2000. I am trying to create a relationship between these 2 tables in the DataSet, but I keep getting an error at runtime when executing the Fill procedure of the data adapter: "Additional information: Failed to enable constraints. One or more rows contain values violating...
16
22506
by: Michael Walton | last post by:
I am trying to write some code that inserts or updates a text field in my SQL Server 2000 database. The SQL statement is created using a submit from a form, and the text is coming from a <textarea>, and therefore being placed into the SQL statement via a Request("field"). However, due to limitations in SQL Server 2000 and text fields, I can not use a simple Update or Insert command with text over 8000 bytes. Does anybody have any...
0
1171
by: Zikica Jevtic | last post by:
I have installed Microsoft Visaual Studio .NET Architect. I have big problem when I tray to connect to SQL Server 2000 data table using SqlDataAdapter: 1) I use SqlDataAdapter wizard to connect to one table on SQL Server 2000. I make new connection and test it. That works. I use Query Builder to make SELECT SQL statement (just simple like
2
8334
by: fig000 | last post by:
Hi, I have an application that's running fine on development servers (web and database-sql server 2000). I'm updating a record through a third party component but I don't think the component is the problem. What's happening is that I'm updating fields that are part of view. I'm only updating fields in one table of the view and this works fine in the development environment.
4
1586
by: Ludovic Lemarinel | last post by:
Bonjour, J'ai developpé une application sous VB.Net qui utilise un datagrid . Ce datagrid utilise un dataTable d'un DataSet comme datasource. ce dataset est rempli de la sorte : 'Debut du code requete = "SELECT ln_nomencdtl_articleentree as 'Article', CASE WHEN
0
852
by: Aryan | last post by:
Hi, I have concern regarding two approaches to decide, 1>DataSet.WriteXML 2>SQL Server 2000 to return XML from stored procedure. With Second approach, I have to open Cursor's and nested stored proc calls and have to create temporary tables. while in first approach I dont have to create all this.
4
2287
by: jayfeb29 | last post by:
Hi , Any one can guide me in Import the Excel 2002 data into sql server 2000 using ado.net1.1. I have searched all the forums most of the solutions end with a line of suggestion not with any code . here is my code . by using OLEDB JET I got the data from Excel to data set , the data is in the data table OleDbConnection strConn = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;data source= c:\\1005.xls;Extended...
6
2676
by: jsacrey | last post by:
Hello everybody, I've got a bit of a situation that I could use some guidance with if possible. I work for an auditing firm where my users audit electronic shipping data for customers to see if they've been overcharged for shipments by truck and rail carriers. 99.9% of the time, one of our auditors needs to see all data sent by a customer in datasheet view so that they can sort records in many different ways looking for duplication...
3
4295
by: shobhitguptait | last post by:
How to Run C#.NET Windows App on N/W with centralized DB using SQL SERVER 2000 Hello All...i m really grate full to c such a website where developers try to help people like us who face problems in s/w develepment...I am shobhit Gupta...23 years of age prusuing B.Tech in I.T. Engg. Final Year from Kurukshetra University, Kurukshetra. Recently, i have been working on a project which is to be implemented in a hospital for real life use...Its a...
0
8750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9401
Oralloy
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...
0
9256
jinu1996
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...
0
9105
tracyyun
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...
1
6701
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4509
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...
0
4780
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2632
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2154
bsmnconsultancy
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...

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.