locked
RETRIEVE AN AUTO-INCREMENT FIELD VALUE AFTER AN INSERT RRS feed

  • 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


    Monday, March 10, 2014 6:57 PM

Answers

  • 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

    • Marked as answer by AndyNakamura Tuesday, March 11, 2014 2:16 PM
    Tuesday, March 11, 2014 9:55 AM
  • 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.


    Tuesday, March 11, 2014 11:11 AM

All replies

  • How about IntAuto-1
    Tuesday, March 11, 2014 9:24 AM
  • 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

    • Marked as answer by AndyNakamura Tuesday, March 11, 2014 2:16 PM
    Tuesday, March 11, 2014 9:55 AM
  • 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.


    Tuesday, March 11, 2014 11:11 AM
  • 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

    Tuesday, March 11, 2014 2:20 PM