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, 09:57 AM.
          Dave Prue
          Code Whisperer
          Lahar International Corp
          www.lahar.net

          Comment


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

            Comment


            • #7
              Someone any idea?

              Comment


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




                Attached Files

                Comment


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

                  Comment

                  Working...
                  X