storing autonumber for new record 2

[boroski]

Hi,

I am building a site which runs on an access db - i have a page which creates a new record and adds into db. This all works ok. One of the columns in the db is the tables primary key (an autonumber).

When i run the insert, is there an easy way to retrieve this number, so i can redirect the user to another page which displays the new record (selected from querystring)
OR
would i have to run through all existing records, find the last one, then add 1

Many thanks
boro

 

[damber]

You can do it in at least 3 ways:

1. Use the AddNew method of the recordset then after update capture the value of the autonumber field before closing the recordset.
2. Use @@IDENTITY ("SELECT @@IDENTITY as LastID") to retrieve the last created AutoNumber value. This is done after the insert statement is executed and before you close the recordset. This can have some issues in Access though (Works better in SQLServer) and requires Access2K or greater (I think) to work.
3. Use "SELECT MAX(YourIDField) as LastID FROM Wherever" to get the last ID inserted (do this on the calling page, not the target page to help prevent picking up a subsequently added record.

The choice, as they say, is yours...

A smile is worth a thousand kind words. So smile, it's easy!

 

[boroski]

Thanks for that - have quick q,

if make a conn to db ok

Set rsCustomer = Server.CreateObject("ADODB.Recordset")
rsCustomer.ActiveConnection = MM_connShrink_STRING
rsCustomer.Source = "SELECT (intCustomerID) FROM tblCustomer"
rsCustomer.CursorType = 0
rsCustomer.CursorLocation = 2
rsCustomer.LockType = 1
rsCustomer.Open()

Response.Write(rsCustomer.Fields.Item("intCustomerID").Value)

and can retrieve info ok - if however i change to

Set rsCustomer = Server.CreateObject("ADODB.Recordset")
rsCustomer.ActiveConnection = MM_connShrink_STRING
rsCustomer.Source = "SELECT MAX (intCustomerID) as lastID FROM tblCustomer"
rsCustomer.CursorType = 0
rsCustomer.CursorLocation = 2
rsCustomer.LockType = 1
rsCustomer.Open()

Response.Write(**lastID**)

how can i retrieve the value lastID? (**lastID**) unfort doesnt work

many thanks
boro

 

[DotNetGnat]

try this:

Response.Write rsCustomer("lastID")

-DNG

 

[boroski]

spot on - cheers for that!!!