sqlTools : retrieve auto number

[Shawn Anderson]

If I insert a record that has an autonumber field, how can I retrieve the autonumber after the insert?

I tried this:

          sqlStatement="SELECT @@IDENTITY AS NewID"
        
        ' ...invoke sql statement
          SQL_stmt %SQL_STMT_IMMEDIATE, sqlStatement
        ' ...check for erros
          errmsg = SQL_ErrorQuickAll
          IF errmsg <> "" THEN    
              errMsg=sqlStatement+errMsg  
              GOTO errOut
          END IF     
          
          SQL_Fetch %NEXT_ROW
          IF NOT SQL_EOD THEN    
            newID=TRIM$(STR$(SQL_ResColSint(1)))
          END IF

But I get nothing.
I'm using SqlTools Pro with an Access database.

[Bud Durland]

If you are the only user updating the table, you could use

Select max(Row_id) from MyTable

On the databases we use at work, that's rarely an option. Usually what I do is have a column in the table who's default value is the user_id that inserted the record (we almost always such a column for auditing purposes, anyway). Then I can do this:

SELECT max(row_id) from MyTable where Creator = 'MyName'

Hope this helps.

[Shawn Anderson]

Thanks Bud,
those are good ideas. For some reason my code started working. I'm doing a CGI app and I think that I had updated my pbcc code but not my javascript or something. I saved and refreshed everything and it's worked ever since.

Using IDENTITY is supposed to return the autonumber within the scope of the last insert for that user so I'm hoping it works as expected.

Thanks!

[Knuth Konrad]

This SQL statement works for me:

sSQL = "SELECT @@IDENTITY AS ID FROM <table name>;"