INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Manipulation

SELECT @@IDENTITY with MS-Access by foxbox
Posted: 30 Mar 10

The Jet 4.0 provider supports @@Identity:
http://support.microsoft.com/kb/232144/nl

The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately.  



So with that new information, here is the technique for obtaining this value using Access:

CODE

<%
    fakeValue = 5
    set conn = CreateObject("ADODB.Connection")
    conn.open "<conn string>"
    sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _
        VBCrLf & " SELECT @@IDENTITY"
    set rs = conn.execute(sql)
    response.write "New ID was " & rs(0)
    rs.close
    set rs = nothing
    conn.close
    set conn = nothing
%>

If you are unable to use JET 4.0, you can do a more risky hack like this:

CODE

<%
    fakeValue = 5
    set conn = CreateObject("ADODB.Connection")
    conn.open "<conn string>"
    conn.execute "INSERT someTable(IntColumn) values(" & fakeValue & ")"
    set rs = conn.execute("select MAX(ID) from someTable")
    response.write "New ID was " & rs(0)
    rs.close
    set rs = nothing
    conn.close
    set conn = nothing
%>

This is more risky because it is remotely possible for two people to "cross" inserts, and receive the wrong autonumber value back. To be frank, if there is a possibility of two or more people simultaneously adding records, you should already be considering a "real" DBMS. However, if you're stuck with Access and need more security that this won't happen, you can use a Recordset object with an adOpenKeyset cursor (this is one of those rare scenarios where a Recordset object actually makes more sense than a direct T-SQL statement):

CODE

<%
    fakeValue = 5
    set conn = CreateObject("ADODB.Connection")
    conn.open "<conn string>"
    set rs = CreateObject("ADODB.Recordset")
    rs.open "SELECT [intColumn] from someTable where 1=0", conn, 1, 3
    rs.AddNew
    rs("intColumn") = fakeValue
    rs.update
    response.write "New ID was " & rs("id")
    rs.close
    set rs = nothing
    conn.close
    set conn = nothing
%>
 

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close