Announcement

Collapse
No announcement yet.

Get last inserted ID in afterinsert

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Get last inserted ID in afterinsert

    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

    Comment


    • #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...

      Comment


      • #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/show...light=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:

        Code:
        $insert_sql = "UPDATE tblxxxxx SET ID = ". $IDkey ." WHERE...........";
        
        sc_exec_sql($insert_sql);
        c) redir with that key

        Comment


        • #5
          How about in afterInsert:

          Code:
          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.

          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.

          Dave
          Last edited by daveprue; 12-07-2014, 08:57 AM.
          Dave Prue
          Code Whisperer
          Lahar International Corp
          www.lahar.net

          Comment

          Working...
          X