New Record Return for update

[kgjeremytw]

Hello,

I've set up a form for users to add records to a DB. Adding new records is a multiple step process where I have users insert general information first, then the users would add more information (to a different table) in another form. How do I have the DB return the record for the general information that was just entered. Since it would be a new number that is generated from the primary key?

All of the examples I have seen for people to update, lists every record available in the DB. That can get cumbersome for the user to have to search for a record they have just entered. Any sample code or web tutorials would be greatly appreciated. Thanks, Jeremy

[pankaj]

If the primary key is an IDENTITY COLUMN then the new number generated can be retrieved by using

select IDENT_CURRENT(tablename) //sql server

[kgjeremytw]

Will that work for Access / ASP/ Win Adv. Server?

[Mhtml]

Not sure if there is a better way to do it but I do it like this..

xDateTime = now()
sqlInsert = "INSERT INTO Table (Field1, Field2, DateTime) VALUES ('"&request.form("f1")&"','"&request.form("f2")&"',#"&xDateTime&:#)"
rs.Open sqlInsert, conn
sqlFind = "SELECT Id FROM Table WHERE DateTime = #"&xDateTime&"# AND Field1 = '"&request.form("f1")&'"
rs.Open sqlFind, conn
entryId = rs("Id")
rs.close


That leaves you with the post id, and by including the first field in the search, it lessens the probability (as little as it is) of finding duplicate records.

[raf]

I use that method frequently when working with access.

Lott of other db's have better ways for retreiving that ID. (like @@IDENTITY for SQLServer)

this thread deals with that
http://www.codingforums.com/showthread.php?s=&threadid=12613&highlight=identity

(have been quite some threads about it ...)

So the best way to do this depends on the db-format you're using.

[whammy]

For SQL Server I either use @@IDENTITY or return a variable containing the last recordset's primary key from a trigger (a little trick a colleague showed me when @@IDENTITY didn't work with a particular table)... don't have the code handy, but lemme know if you need it, it wasn't too difficult.