get auto_increment field after addnew ?

[Inhumanoid]

If I add a record to a table (with addnew or whatever) and this table has an auto_incrementing field (as a unique identifier) how do I retrieve this identifier (without having to execute a new query) ?

[JoshT]

If the db cursor is on the new record when you call the .Update function (I would think it would be, just look at .Fields("AutoID") or whatever you called it, right after the Update.

[Inhumanoid]

Nope this does not work. If I do this f.i.:

objRS.Open "table",objConn,0,3,2
objRS.AddNew
objRS("Name") = strName
objRS("Pass") = strPass
Response.Write "ID = " & objRS("ID")
objRS.Update
Response.Write "ID = " & objRS("ID")

I get:

ID = ID =

[omarswan]

Here is an example of something that I recently used.

                               'Save the data
                 Set tmpRS   = Server.CreateObject("ADODB.Recordset")
                 Set tmpConn = Server.CreateObject("ADODB.Connection")
                 tmpSQL      = "SELECT * FROM realestate_properties"
                 
                 tmpConn.ConnectionTimeout = DATABASE_CONNECTION_TIMEOUT
                 tmpConn.Open DATABASE_CONNECTION_STRING
             
                 tmpRS.Open tmpSQL, tmpConn , adOpenKeyset, adLockOptimistic
                 
                 tmpRS.AddNew                 
                 tmpRS.Fields("realestate_company_id").Value               = gCompanyId
                 tmpRS.Fields("realestate_owner_firstname").Value          = PadSQL(get_form("ownerfirstname"))
                 tmpRS.Fields("realestate_owner_lastname").Value           = PadSQL(get_form("ownerlastname"))
                 tmpRS.Fields("realestate_owner_home_phone").Value         = PadSQL(get_form("ownerhomephone"))
                 tmpRS.Fields("realestate_owner_work_phone").Value         = PadSQL(get_form("ownerworkphone"))                 
                 tmpRS.Fields("realestate_owner_address").Value            = PadSQL(get_form("owneraddress"))
                 tmpRS.Fields("realestate_owner_city").Value               = PadSQL(get_form("ownercity"))
                 tmpRS.Fields("realestate_owner_state").Value              = PadSQL(get_form("ownerstate"))
                 tmpRS.Fields("realestate_owner_zipcode").Value            = PadSQL(get_form("ownerzipcode"))                 
                 tmpRS.Fields("realestate_property_address").Value         = PadSQL(get_form("propertyaddress"))
                 tmpRS.Fields("realestate_property_city").Value            = PadSQL(get_form("propertycity"))    
                 tmpRS.Fields("realestate_property_state").Value           = PadSQL(get_form("propertystate"))
                 tmpRS.Fields("realestate_property_zipcode").Value         = PadSQL(get_form("propertyzipcode"))                 
                
 'Update
 tmpRS.Update
                 
'get the new property id (auto-incremented field)
get_property_id = tmpRS.Fields("realestate_id")
                                                      
Set tmpRS   = Nothing
Set tmpConn = Nothing