Get last inserted ID in afterinsert

Code Examples Form
#1

Hi,
I have a master/detail form and I want that after the user insert the master I redirect it to the same form to compile the details.
I have put this code in the afterinsert:

sc_commit_trans();
sc_redir(form_master,id_doc={ID_DOC});

but {ID_DOC} (which is the pk) is null (it is generated from a sequence in oracle db)

what is the right way to archive this result?
Thank You

#2

try to use other events

on_after_insert : echo {ID_DOC};
reurns your ID

I’m working on MySQL and MSSQL-Server

good luck!
Jens

#3

I don’t understand what do You say… I need the ID_DOC value in the afterinsert because the ID is created only before that event… so i need to read the value here…

#4

You mention Oracle sequence - so you could query that sequence table knowing what the ID will then be?

If that makes no sense see this link: http://www.scriptcase.net/forum/showthread.php?7164-Name-sequence&highlight=sequence

Talks about sequence tables. If you don’t (or can’t) do that then another option is to:

  1. Manage the key yourself (not autoincrement) in AfterInsert

a) Read the relevant table and get the latest ID used
b) add 1 to it and then update the table yourself:

$insert_sql = "UPDATE tblxxxxx SET ID = ". $IDkey ." WHERE...........";

sc_exec_sql($insert_sql);

c) redir with that key

#5

How about in afterInsert:

sc_commit_trans();
sc_lookup(sc,"SELECT LAST_INSERT_ID()");
sc_redir(form_master,id_doc=$sc[0][0]);

I do this frequently, it works fine with mysql, don’t know about Oracle though.

[SIZE=2]One warning regarding LAST_INSERT_ID(), you never, ever want to do “SELECT LAST_INSERT_ID() FROM myTable” since that will return the last insert ID once for each record in the table. If you have 10,000 records in the table and your last inserted id is 34, then it will return 10,000 records each showing 34.
[/SIZE]
Dave

2 Likes
#6

If I use this:

sc_commit_trans(); sc_lookup(sc,“SELECT LAST_INSERT_ID()”); sc_redir(form_master,id_doc=$sc[0][0]); I get the last ID of the log table. How do I provide that?

#7

Someone any idea?

#8

Is your form Single-record?

If you simply want to re-open the newly inserted record, you don’t need to do any coding. No need to code the onAfterInsert. Just go to Application -> Navigation and turn on Return After Inserting. It does the job for me, re-opening the newly inserted record. I do not need to worry about the ID generated. (see attached screenshot for location)

This only applies if the page you want to re-open is the same page where you inserted records.

Return After Inserting.jpg

#9

Thank you so much for your answer.

If have to post the last_insert_id to another form and do not stay in the same form… :slight_smile:

#10

I think there is a Bug for ON AFTER INSERT EVENT, When i need get LAST INSERT ID.
I was reading old post, i can see it error persist.

My code in On After Insert for a form Single Record is:

sc_commit_trans();
sc_lookup(sc,SELECT LAST_INSERT_ID());
$LastId_Inserted = {sc[0][0]};

IT RETURN LAST INSERT ID of the LOG TABLE.

How solve it?

#11

Right now i dont use the log option in that form as work around. Maybe it might help to make another database connection only for the log system. If im right the last_insert_id takes the last inserted id of that connection. I didnt test this. But this would be the next thing to try.

#12

I solved asign using field for primery key of my table {id_table}, it took the value correct.
But i think it is a bug to fix for SC team.

The Process must be:
First insert into Log table and then the table of form.

#13

This might be old but it solved my case where I wanted to redirect to a tab application after a form insert.

#14

Provided this works as stated, this is probably the best (and most portable) method. With that said, understanding how to do this within an event is also very helpful.

Thankyou for sharing.