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/
