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 º¿º 9 1704
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*************@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 º¿º
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****************@TK2MSFTNGP14.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*************@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 º¿º
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**************@TK2MSFTNGP14.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****************@TK2MSFTNGP14.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*************@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 º¿º
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**************@TK2MSFTNGP14.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****************@TK2MSFTNGP14.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*************@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 º¿º
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 º¿º
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
"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 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****************@TK2MSFTNGP09.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**************@TK2MSFTNGP14.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****************@TK2MSFTNGP14.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*************@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 º¿º
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |