Classic ASP Discussion - How to retreive the autonumber

Print Page | Close Window

How to retreive the autonumber

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=573
Printed Date: 29 March 2026 at 12:51am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: How to retreive the autonumber

Posted By: maniva
Subject: How to retreive the autonumber
Date Posted: 27 February 2003 at 12:22pm

Dear friends,
I've an asp page where I create a record in a Access table.
In that table I've a Key field that is autonumber.

How can I know which is the number just insered ?

Here the code that i've

If (strMode = "edit" OR strMode = "new") Then

  If strMode = "new" Then rsOpere.AddNew
   rsOpere.Fields("Author_id") = AutorId

   rsOpere.Update

End If

The field name in the table is ID

Thank you in advance

-------------
Ivano Mangiarotti

Replies:

Posted By: MorningZ
Date Posted: 27 February 2003 at 12:35pm

after your code:

if strMode = "new" then
Set rstIdent = connectionObject.Execute("SELECT @@IDENTITY")
ThisNewID = rstIdent(0)
Set rstIdent = Nothing
end if

-------------
Contribute to the working anarchy we fondly call the Internet

Posted By: maniva
Date Posted: 27 February 2003 at 12:46pm

Thanks Morninz,
but what is RSTIDENT ??

I've to define it ?

Bye

-------------
Ivano Mangiarotti

Posted By: MorningZ
Date Posted: 27 February 2003 at 12:52pm

its a new recordset object, thats all

-------------
Contribute to the working anarchy we fondly call the Internet

Posted By: maniva
Date Posted: 27 February 2003 at 12:56pm

Sorry Morninz but It doesn't work

Tipo di errore:
Errore di run-time di Microsoft VBScript (0x800A01A8)
Necessario oggetto: ''
/Artist/Opere_upload.asp, line 56

-------------
Ivano Mangiarotti

Posted By: MorningZ
Date Posted: 27 February 2003 at 1:05pm

dunno what to tell you, i pulled that little snip from some working code on my site...

which i originally got from http://www.aspfaqs.com/webtech/100598-1.shtml - this how-to

-------------
Contribute to the working anarchy we fondly call the Internet

Posted By: MadDog
Date Posted: 27 February 2003 at 1:52pm

keep it easy =P

If (strMode = "edit" OR strMode = "new") Then

  If strMode = "new" Then rsOpere.AddNew
   rsOpere.Fields("Author_id") = AutorId

   rsOpere.Update
    rsOpere.MoveLast

AutorID = rsOpere("Author_id")
End If

-------------
http://www.iportalx.net" rel="nofollow">

Posted By: Bunce
Date Posted: 27 February 2003 at 6:52pm

Mornigz method is preffered, however you need to be using Jet 4.0 (Access 2000+)

Cheers,
Andrew

-------------
There have been many, many posts made throughout the world...
This was one of them.

Posted By: maniva
Date Posted: 28 February 2003 at 2:25am

Hi to all,
just to clarify for MADDOG i Know the value of Author_ID what I don't know is the value of ID ( my Key that is an Autonumber field)

And for my friend MorninZ I tryed in different way but it is not retreiving the number. Sorry.
I'm using Access2000 DB.

I followed a suggestion of boRg that suggest me to define an SQL by Author_id and sorted by ID descending and then after insserting the new record and fiels requery the table.

Bye

-------------
Ivano Mangiarotti

Posted By: Bunce
Date Posted: 01 March 2003 at 4:45am

If your'e using Access2K, then use the Jet database driver and execute a "SELECT @@Identity" after you create the records and it will return the autonumber:
http://www.adopenstatic.com/Experiments/FastestAutonumber.asp - http://www.adopenstatic.com/Experiments/FastestAutonumber.asp

Cheers,
Andrew

-------------
There have been many, many posts made throughout the world...
This was one of them.

Posted By: maniva
Date Posted: 02 March 2003 at 3:59pm

Hi Bunce,

Thank you wery much your example and documantation are exellent.

-------------
Ivano Mangiarotti

Posted By: Stinger
Date Posted: 03 March 2003 at 11:30am

I had the same problem and made this script. I don't have to create a diff recordset this way. It works for me (basically I don't use a SQL statement to create the rs, but I user the AddNew method. After the Update the recordset is still pointing the added row, so u can retrieve the autonumber). Just change connection db name, table name and fields:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("dbpath.mdb") & ";")

Set oRS=CreateObject("ADODB.Recordset")

oRS.ActiveConnection = oConn
oRS.CursorLocation = adUseClient
oRS.CursorType = adOpenKeyset
oRS.LockType = adLockOptimistic
oRS.Source = "tablename"
oRS.Open

oRS.AddNew
oRS("Field1") = "Value1"
oRS("Field2") = "Value2"
oRS.Update

intAutoID = oRS("ID")

From your code, if you use just the last line after the Update, you should be able to retrieve the ID.

-------------
Stinger
A hero's job is to save everyone but himself.

Posted By: Bunce
Date Posted: 03 March 2003 at 5:03pm

You *are* opening a recordset. As soon as you do oRS.Open.

Unless you wish to work with the recordset, such as displaying or manipulating the output some way, then why bother with the overhead of creating a recordset?

Set based processing (ie. INSERT's, UPDATES etc) is generally the preferred method..

Cheers,
andrew

-------------
There have been many, many posts made throughout the world...
This was one of them.