RE: [DUG]: ClientDataset inserts with MSSQL Identity column problem

Tue, 01 Jul 2003 13:22:49 -0700


If using MS SQL Server 2000 then I know you can use the IDENT_CURRENT function, it takes a table name as a parameter - and returns the last Identity for that table

eg:

SELECT IDENT_CURRENT('GroupTable')

Not sure if this helps any, as I havn't worked with client datasets much, but works for me within stored procedures...

Aaron

From: "Jeremy Coulter" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Subject: RE: [DUG]: ClientDataset inserts with MSSQL Identity column problem
Date: Tue, 1 Jul 2003 22:26:56 +1200

Yeah, I would go with this suggestion too.

Jeremy

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Bevan Edwards
Sent: Tuesday, 1 July 2003 21:11
To: Multiple recipients of list delphi
Subject: Re: [DUG]: ClientDataset inserts with MSSQL Identity column
problem


How about using a Stored Procedure instead of a trigger?  I know that
the "limitation"/functionality of the @@IDENTITY returning the last
inserted value can be a problem, but if you can use a SP then you could
insert into the first table, grab the @@IDENTITY, then insert into the
second table, and return the original @@IDENTITY value.

Regards,

Bevan


----- 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/

---------------------------------------------------------------------------
    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/


_________________________________________________________________
Gaming galore at  http://xtramsn.co.nz/gaming !

---------------------------------------------------------------------------
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/

Reply via email to