2 Replies Latest reply on Sep 1, 2011 2:31 AM by kokorito

    a sqlite problem

    Kristin95762 Level 1

      Hi

       

      I'm trying to retrieve the last authorID entered into my table.

      Here's my code:

       

      private function getNewAuthorID(event:SQLEvent):void

      {

           getNewAuthorIDStmt = new SQLStatement();

           getNewAuthorIDStmt.sqlConnection = conn;

       

       

           var sql:String = "SELECT MAX(authorID) FROM authors3";

           getNewAuthorIDStmt.text = sql;

       

       

           getNewAuthorIDStmt.addEventListener(SQLEvent.RESULT, newAuthorIDResult);

       

       

           getNewAuthorIDStmt.execute();

      }

       

       

      private function newAuthorIDResult(event:SQLEvent):void

      {

           var result:SQLResult = getNewAuthorIDStmt.getResult();

           var numResults:int = result.data.length;

           trace("numResults: " + numResults);

       

       

           for (var i:int = 0; i < numResults; i++)

           {

                var row:Object = result.data[i];

                var newAuthorID:int = row.authorID;

                trace(newAuthorID);

           }

      }

       

      I've gotten this code to work before but it was not contained in a class file, just a script block (don't know if that matters). The first trace function returns '1', but the second trace function returns'0'.

       

      I need to get the newly entered authorID so I can enter a book title into another table with the correct authorID.

       

      I don't want to use the lastInsertRowID() because my rowid's and author id's will not be the same.

       

      Thanks

       

      Kristin

        • 1. Re: a sqlite problem
          Kristin95762 Level 1

          Nevermind. I found a different route.

           

           

          private function getNewAuthorID(event:SQLEvent):void

          {

               var result:SQLResult = insertNewAuthorDetailsStmt.getResult();

               var primaryKey:int = result.lastInsertRowID;

               trace(primaryKey);

          }

           

          No, wait, still having a problem.

           

          I seem to be having a problem holding onto the value of the primaryKey. How do I perpetuate that value through the next sqlstatement? The trace function displays the correct ID. However, when I run an INSERT to enter data into the next table it inserts a '0'. I'm trying to enter data from one form into two different tables that have a common ID number.

           

          Thanks

           

          Kristin

          • 2. Re: a sqlite problem
            kokorito Level 4

            you declare primaryKey inside the function, so the scope is local and only exists inside the function.

            Declare the primaryKey as a class variable if you need to use it in other methods or pass it as a parameter