locked
Retrieving @@Identity value RRS feed

  • Question

  • Hi

    I am using below code to insert a record into an access table and then retrieve the auto number id using @@Identity;

            If LocalConn.State = ConnectionState.Closed Then
    
                LocalConn.Open()
    
            End If
    
            Dim DBCommand As System.Data.OleDb.OleDbCommand
    
            Dim I As Integer
    
            St = "INSERT INTO tblClients ( MyFld ) SELECT 123 "
    
            DBCommand = New System.Data.OleDb.OleDbCommand(St, LocalConn)
    
            I = DBCommand.ExecuteNonQuery()
    
            Dim Cmd As OleDb.OleDbCommand
    
            Dim Reader As OleDb.OleDbDataReader
    
            Dim ID As Int32
    
            St = "SELECT @@Identity as ID"
    
            Cmd = New OleDb.OleDbCommand(St, LocalConn)
    
            Reader = Cmd.ExecuteReader()
    
            If (Reader.Read()) Then
    
                ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
    
            End If
    
    

    The problem is that ID returns a 0 (zero) value instead of the actual id value. What am I doing wrong?

    Many Thanks

    Regards

    Monday, March 15, 2010 10:18 PM

Answers

  • Database is Access 97.


    I know that the Jet OLEDB Provider version 4.0 is required, but I think Access 97 is a version 3.5 database. While you can use the 4.0 provider with older database versions, I don't think they support @@IDENTITY.

    You could try SELECT MAX on the AutoNumber field but in a multi-user environment this would probably not be reliable.

    Unfortunately, I'm not aware of any other method you can use with ADO.NET that will retrieve an Access AutoNumber value after an INSERT when working with a pre-Access 2000 database. AFAIK, only Classic ADO and DAO support this feature.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, March 18, 2010 1:29 PM

All replies

  • This code worked when I tried it.  I assume you are using SQL Server, as that is the database that uses @@IDENTITY.

    The #1 thing that could go wrong is if tblClients does not have a column set up as the IDENTITY.

    Use Management Studio to drill down to the column and then double-click it.  The Identity property should say True.

    Table DDL that I used:

    CREATE TABLE tblClients 
    (
    	ID INT IDENTITY,
    	MyFld INT
    )
    Note that triggers can interfere with @@IDENTITY. If you have triggers on this table, this may be your problem.  Substitute SCOPE_IDENTITY() for @@IDENTITY, as this is a newer function that avoids the trigger issue.
    Monday, March 15, 2010 10:45 PM
  • Hi

    Could it be that db is Access 97? I am reasonably sure I tried something similar with Access 2000 db in the past and it worked.

    Table has an Autonumber ID as below;

    Name	Type	Size
    	ID	Number (Long)	4
    	AllowZeroLength: 	False
    	Attributes: 	Fixed Size, Auto-Increment
    	Collating Order: 	General
    	ColumnHidden: 	False
    	ColumnOrder: 	1
    	ColumnWidth: 	Default
    	Ordinal Position: 	1
    	Required: 	False
    	Source Field: 	ID
    	Source Table: 	tblClients
    


    Thanks

    Regards
    Tuesday, March 16, 2010 3:15 AM
  • This may help, there is a section on getting identity/autonumber at the end.

    "Retrieving Identity or Autonumber Values (ADO.NET)"
    http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx

    Also, you need to get the identity from the same connection that does the insert.

    "Getting AutoNumber from Access via "SELECT @@IDENTITY" needs to be done in same connection as the INSERT."
    http://blogs.msdn.com/spike/archive/2009/12/02/getting-autonumber-from-access-via-select-identity-needs-to-be-done-in-same-connection-as-the-insert.aspx

    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.
    Tuesday, March 16, 2010 8:53 AM
  • Try this kind of code

            If LocalConn.State = ConnectionState.Closed Then

                LocalConn.Open()

            End If

            Dim DBCommand As System.Data.OleDb.OleDbCommand

            Dim I As Integer

            St = "INSERT INTO tblClients ( MyFld ) SELECT 123 "

            DBCommand = New System.Data.OleDb.OleDbCommand(St, LocalConn)

            I = DBCommand.ExecuteNonQuery()

            DBCommand.CommandText = "SELECT @@Identity"

            Dim ID As Object

            ID = DBCommand.ExecuteScalar()


    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, March 17, 2010 10:13 AM
  • VMazur, the original post code did seem to work on my machine.  Assuming the database was SQL Server (is there some other db that has @@IDENTITY??).  At least for SQL Server, my understanding is that the re-use of the OleDbCommand object will not matter because the @@IDENTITY is connection-scoped and the user has kept the LocalConn open and used it for both commands.  Personally, I usually use SCOPE_IDENTITY() and combine both the INSERT statement and the SELECT SCOPE_IDENTITY() statement into the same command batch.  Optimizes the number of database trips and avoids any confusion as to whether the identity value is still valid...
    Wednesday, March 17, 2010 11:23 PM
  • Database is Access 97.

    Thursday, March 18, 2010 1:08 AM
  • > Database is Access 97.

    Ah... the classic :)

    Have you tried VMazur's code, which reuses the DBCommand object to run the SELECT @@IDENTITY and uses ExecuteScalar instead of ExecuteReader?

    Have you verified by looking in the Access database directly that autonumber values are actually being assigned correctly?

    Thursday, March 18, 2010 1:25 AM
  • Database is Access 97.


    I know that the Jet OLEDB Provider version 4.0 is required, but I think Access 97 is a version 3.5 database. While you can use the 4.0 provider with older database versions, I don't think they support @@IDENTITY.

    You could try SELECT MAX on the AutoNumber field but in a multi-user environment this would probably not be reliable.

    Unfortunately, I'm not aware of any other method you can use with ADO.NET that will retrieve an Access AutoNumber value after an INSERT when working with a pre-Access 2000 database. AFAIK, only Classic ADO and DAO support this feature.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, March 18, 2010 1:29 PM
  • Database is Access 97.


    I know that the Jet OLEDB Provider version 4.0 is required, but I think Access 97 is a version 3.5 database. While you can use the 4.0 provider with older database versions, I don't think they support @@IDENTITY.

    You could try SELECT MAX on the AutoNumber field but in a multi-user environment this would probably not be reliable.

    Unfortunately, I'm not aware of any other method you can use with ADO.NET that will retrieve an Access AutoNumber value after an INSERT when working with a pre-Access 2000 database. AFAIK, only Classic ADO and DAO support this feature.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Safely, Access 97 does not support "@@ Identity." You get the last record that you Insert and get the ID. I ran into the same problem...
    Monday, September 19, 2011 11:46 AM