inserting a new record and getting its id

[lordhuh]

i have my primary key a autoincriment integer field called ID.
in another table i have them under a field to basically link a user with up to 2 fields. my problem is when i add a new record to table2 how would i get the ID value that it just got so i can stick it in a field in table 1. Karl Pietri

http://www.crantorland.com

 

[haslo]

As the new ids are normally created incrementally (you can select this in the field properties in your DBMS normally) the newest value is always the highest. Just select the first record in a query that returns just the ids from your table.

In SQL this would look like that:

select id from table2 order by id desc

If you are a little advanced you can of course also create some intermediate code that just right after the first insert took place (you do find the place in UD's code, I had this problem recently, too) you can make the query and insert the second record as well.

Otherwise you'll just have to insert another page that asks the user to press the button ("otherwise your changes will be lost" and stuff, the problem is just to say the right things so the client doesn't notice it's really a workaround - you can also let the user add additional information on the second page, so the first page is the "creation part" and the second page is the "definition part" where you already have the ID)

Hope this helps

allow thyself to be the spark that lights the fire
haslo@haslo.ch - www.haslo.ch​

 

[jonscott8]

It would help if you identify what database you are using.

SQL Server and Access2000 both support the @@Identity global variable:

oConn.Open "MyDSN"
sSQL="INSERT INTO MyTable(MyField) VALUES('MyValue')"
oConn.Execute sSQL,nAffected
IF nAffected>0 THEN
SET oRS = oConn.Execute "SELECT @@IDENTITY"
ELSE
'Insert didnt work
END IF

Search the MSDN for a more detailed example. Jon Hawkins