Announcement

Collapse
No announcement yet.

sqlTools : retrieve auto number

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

  • Knuth Konrad
    replied
    This SQL statement works for me:

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

    Leave a comment:


  • Shawn Anderson
    replied
    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!

    Leave a comment:


  • Bud Durland
    replied
    If you are the only user updating the table, you could use

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

    Code:
    SELECT max(row_id) from MyTable where Creator = 'MyName'
    Hope this helps.

    Leave a comment:


  • Shawn Anderson
    started a topic sqlTools : retrieve auto number

    sqlTools : retrieve auto number

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

    I tried this:
    Code:
              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.
Working...
X