Return AutoNumber

  • Thread starter Thread starter Kevin Fu
  • Start date Start date
K

Kevin Fu

Hi,

I am a student learning to build a website with ASP based on MS Access
database. I want to return the autonumber (@@identity) right after a new
record is inserted into the table.

I am trying to use ado to do that but so far still cannot make it. Most
reference says that only stored procedure can return the @@identity.
However, I cannot create any stored procedure, like I did in SQL server.

If anyone can tell me how to do it, that will be great.

Thank you very much all in advance.!

Kevin
 
@@identity is a SQL Server command. For Jet, you could try the LastModified
property of the recordset (this is DAO, not ADO) to move to the last
modified or added record then retrieve the value from the desired field. I
tried looking for a similar ADO property and didn't see one, but may have
missed it. Another option would be to place a date/time stamp field in the
recordset and move to the record with the latest date/time. You may also
want to check with the ASP newsgroups to see if ASP has a command that will
help.
 
Try:

Dim rst As New ADODB.Recordset
Dim strSql As String

strSql = "insert into contacts (FirstName,LastName)
values('test1','test2')"

CurrentProject.Connection.Execute strSql

rst.Open ("select @@identity from contacts"), CurrentProject.Connection


Debug.Print rst(0)
 
Back
Top