Retrieving autonumber value after adding record

[emozley]

What is the best way of getting the value of an autonumber field just after you add the record?

Would you have to select all and order by UniqueID desc and then take the value for the first row in the recordset or is there a less clumsy way?

cheers

 

[emozley]

I have tried the following but with no luck:

rsCreate.Open strSQL, adoCon

rsCreate.AddNew

'Add a new record to the recordset
rsCreate.Fields("FirstName") = Request.Form("FirstName")
rsCreate.Fields("Surname") = Request.Form("Surname")
rsCreate.Fields("Company") = Request.Form("Company")
rsCreate.Fields("Contact") = Request.Form("Contact")

'Write the updated recordset to the database
rsCreate.Update

VisitorID=rsCreate("VisitorID")

'Reset server objects
rsCreate.Close

 

[Slippenos]

It looks like it should work ...
Are you generating errors?

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[guitardave78]

try

rsCreate.Update
rsCreate.MoveLast
VisitorID=rsCreate("VisitorID")
[code]

}...the bane of my life!
[URL unfurl="true"]http://www.fuzzyd.co.uk[/URL]

 

[guitardave78]

Or after the update try

strSQL = "SELECT MAX(VisitorID) as ID FROM tbl_users"
set rs = conn.execute(strSQL)
VisitorID=rs("ID")
set rs=nothing

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[DotNetGnat]

code looks good to me...what is the error you are getting..

-DNG

 

[emozley]

Cracked it! Wasn't getting an error as such but if I put in:

VisitorID=rs("VisitorID")
Response.Write(VisitorID)

it would be blank - anyway it turned out that I was using the wrong cursor type. I had it set to 2 then I changed it to 1. Apologies I should have posted the full code.

Thanks very much though.

 

[Bullschmidt]

Yes I'd suggest using adOpenKeyset cursor type (and probably adLockOptimistic lock type) if adding a new record and will be wanting to get the autonumber.

Best regards,
-Paul

http://www.Bullschmidt.com

- Freelance Web and Database Developer

http://www.Bullschmidt.com/DevTip.asp

- Classic ASP Design Tips