Edwin
The main problem with Identities is geting back the value, There
is the @@IDENTITY variable (up to 7) but the main problem
with this is if you have any triggers that subsequently insert into
Identitiy based tables then @@IDENTITY will return the last
inserted identity, It looks like they've addressed this ni 2000 with
SELECT IDENT_CURRENT('GroupTable')
My prefered solution is to use a High/Low Key system or
a stored proc (as in a Generator in IB speak)
A high/low system is where you get the server to generate
a high part of the PK and the client adds the low part of the
key, It has performance advanges over a total server side key
generation and the client has the key before insertion, Ie if
your low key is a byte you only have to generate a high
key from the server every 256 insertions
HTH
Neven
----- Original Message -----
From: "Edwin Das" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Tuesday, July 01, 2003 6:57 PM
Subject: [DUG]: ClientDataset inserts with MSSQL Identity column problem
hi...
I have a client dataset / MSSQL Server problem with Identities..
For Example:
I have the following table definition
CREATE TABLE GROUPTABLE (
SEQNO INTEGER IDENTITY(1,1) PRIMARY KEY,
GROUPNAME VARCHAR(30) NULL)
Note : This table has a trigger which inserts into another table
which also has an IDENTITY column.
Then Lets say i have a TCLientDataset on my form with a commandtext property
value of
"SELECT * FROM GROUPTABLE WHERE SEQNO=-2'
And in code i do the following.
ClientDataset.Insert.
ClientDataset.FieldbyName ('GROUPNAME').asString := 'TEST';
ClientDataset.Post;
ClientDataset.Applyupdates;
My question is how do i return / view the just inserted identity value for
the SEQNO Field in the GROUPTABLE without re-opening the clientdataset.
I cannot seem to rely on the "SELECT @@IDENTIFY" statement as this will
return the value of the identity value inserted by the trigger and not by
the inserted row of the GROUPTABLE.
Is there a way of returning an just inserted identity value from client
dataset with some sort of SQL Statement or hooking into a certain Provider
event or something.
Surely inserting into MSSQL tables with IDENTITY Columns using
TClientDatasets and returning a the IDENTITY Column value
back to the code or clientdataset to be used in a master/detail relationship
has been done before.
Any help or info would be appreciated..
Thanks heaps.
Edwin Das
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
