Hi.

I'm using Access/SQL/ASP to make a web-app. I have a table which I use to store completed transactions, and I'm using the "AutoNumber" variable type to give each transaction a unique number.

The trouble is I need to relate information in other tables to the transaction which I just posted to the storage database. Basically I need a way to get at the AutoNumber which was automatically generated when I inserted the new recordrow.

I could probably get at it by SELECTing the AutoNumber WHERE all the data matches the data I just posted, but that seems like an inefficient and inelegant way to do something pretty simple.

Any ideas?

--Robert (rames@utdallas.edu)

-- Anonymous, July 07, 1999

Answers

Robert,

If you can guarantee that no other process is adding data to completed transactions table (for instance, by using a lock) you can select the maximum autonumber instead of specifying all the data.

With SQL Server you could also use a trigger which references the inserted table.

Hope this helps,

Eric

-- Anonymous, July 13, 1999

The SQL statement to retrieve the last autonumber inserted is: "SELECT autonumber_column_name FROM table_name WHERE IdentityCol = @@Identity" You would need to ensure that there are no intervening inserts, as the last number used is returned to you. Maureen.

-- Anonymous, July 26, 1999