RETRIEVE AN AUTO-INCREMENT FIELD VALUE AFTER AN INSERT

Question

I have just inserted an record to a table (Access mdb).

I am now trying to find the value in the auto-increment field so I can display the new record right after I closed the connection used to insert the record.

Dim CON As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbSource As String
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = D:\Jaws2.mdb"
        CON.ConnectionString = dbProvider & dbSource

        If CON.State = ConnectionState.Closed Then
            CON.Open()
        End If
        Dim IntAuto As Integer '      SELECT Max(TBLJAWDETAILS.JAWDETAILID) AS MaxOfJAWDETAILID FROM TBLJAWDETAILS;
        Dim cMD As New OleDbCommand("SELECT Max(TBLJAWDETAILS.JAWDETAILID) AS MaxOfJAWDETAILID FROM TBLJAWDETAILS;", CON)
        cMD.Parameters.Add(New OleDb.OleDbParameter("JAWDETAILID", IntAuto))
        Dim DbReader As OleDbDataReader = cMD.ExecuteReader(CommandBehavior.SingleRow)
        If DbReader.HasRows Then
            Stop
            DbReader.Read()
            IntAuto = DbReader.Item("JAWDETAILID").ToString
            MsgBox(IntAuto)
        End If

I'm getting an

An unhandled exception of type
'System.IndexOutOfRangeException' occurred in System.Data.dll

Additional information: JAWDETAILID

The exception occurs either on the "IntAuto = DbReader.Item("JAWDETAILID").ToString" Line or the msgbox line

Note sure if the code is wrong or I'm querying the db too early

The record is being written to the database

Answer 1

How about IntAuto-1

Answer 2

You need the @@Identity, be aware however, this one fails if it is about multiuser use

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

It seems you want to use an autoidentifier as a logical identifier, avoid that, as soon as you need an database update you can sent then all your customers an update about the new assigned ID's.

 

---

Success
Cor

Answer 3

Hello,

Best to get the new key directly after adding a new record as shown below in a simple demo. We do the insert, check the return value to make sure it's 1, else we do not run the next SQL which 'gets' the newly added records primary key value into the variable Identifier. If you attempt to get the key after closing the connection as Cor indicated there is no guarantee when multiple users may be adding data. 

Dim Affected As Integer
Dim Identfier As Integer = -1
Using cn As New OleDb.OleDbConnection With {.ConnectionString = "Your connection string"}
    Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
        cmd.Parameters.AddWithValue("@CompanyName", "ABC")
        cmd.Parameters.AddWithValue("@ContactName", "Mary")
        cn.Open()
        Affected = cmd.ExecuteNonQuery()
        If Affected = 1 Then
            cmd.CommandText = "Select @@Identity"
            Identfier = CInt(cmd.ExecuteScalar)
        End If
    End Using
End Using

This example is not for multi-user environment as per Cor but if there will never be more than one person adding records this is fine. We are asking specifically the last identifier by sorting descending on that key then return one value.

Public Sub GetLastKey()
    Using cn As New OleDbConnection("Your connection string")
        Using cmd As New OleDbCommand With
            {
                .CommandText = "SELECT Identifier FROM People ORDER BY Identifier DESC",
                .Connection = cn
            }
            cn.Open()
            Dim Identifier As Integer = CInt(cmd.ExecuteScalar)
            Console.WriteLine(Identifier)
        End Using
    End Using
End Sub

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

Answer 4

I see what you mean about multi user.
I ended up doing it similar to Kevin's answer but I think the best thing is just to do a where query
on the fields updated.
Chances of two identical records existing are slim and even if this is the case it simply returns two records at least they are both valid

Thanks

Andy