SQLInsert

rept · Post by **rept** » Thu 21 Oct 2010 23:20

I have the following stored procedure:

BEGIN
insert into Timesheet (TS_ORID, TS_Begin, TS_End, TS_Hourrate, TS_PRID, TS_Desc, TS_OldID, TS_USID)
values (ORID, BeginT, EndT, Hourrate, PRID, Description, OldID, USID);
set TS_ID = LAST_INSERT_ID();
END

TS_ID is a Output parameter.

I call this procedure in a SQLInsert part and that works. However it will not put the TS_ID into the identity field of the component, how can I do this?

AndreyZ · Post by **AndreyZ** » Fri 22 Oct 2010 09:48

Hello,

Please take a look at this topic: http://www.devart.com/forums/viewtopic.php?t=12095

rept · Post by **rept** » Fri 22 Oct 2010 10:35

I read that post before, with a couple of others I found this doesn't really answer the question.

How can I put the newly created ID in the identity column by only using SQLInsert? Or do I need to run a second query that gets the identity and then sets it in the identity field?

AndreyZ · Post by **AndreyZ** » Fri 22 Oct 2010 13:13

If you use the INSERT statement in SQLInsert the identity column will be automatically filled. For that you have to put the following SQL code to SQLInsert:

Code: Select all

  insert into Timesheet (TS_ORID, TS_Begin, TS_End, TS_Hourrate, TS_PRID, TS_Desc, TS_OldID, TS_USID) 
  values (ORID, BeginT, EndT, Hourrate, PRID, Description, OldID, USID);

And if you want to use stored procedure in SQLInsert, then you will have to set the identity column manually. You can get the LAST_INSERT_ID value using the following code:

Code: Select all

  MyQuery.SQL.Text := 'SELECT LAST_INSERT_ID() as ID;'; 
  MyQuery.Open; 
  ID := MyQuery.FieldByName('ID').AsInteger;

rept · Post by **rept** » Fri 22 Oct 2010 15:33

Thanks,

Since I'm using a stored procedure to do the insert I will need to use a query to get the ID value.

I got the value, but in which event do I put it in the identity of the table?

AfterPost?

Will I need to put the dataset in edit mode again to fill in the identity?

rept · Post by **rept** » Fri 22 Oct 2010 15:35

Never mind, found it!!!

I put it in AfterUpdateExecute

AndreyZ · Post by **AndreyZ** » Mon 25 Oct 2010 06:54

It is good to see that this problem has been solved. If any other questions come up, please contact me.