Best method to grab an autonumber from a recently added row in a datatable? RRS feed

  • Question

  • I am using this guide to add rows to a datatable.

    And I was hoping there was an accepted method to find the index or an autonumber field from the recently added row. I considered simply just looking for the last row, but that doesn't seem entirely safe. I'm sure there has to be some sort of index returned from the insertion, correct?

    Any suggestions appreciated.

    Thursday, June 30, 2011 5:04 PM


All replies

  • In such case I use Stored Procedure and here there is a FAQ about it



    Thursday, June 30, 2011 8:30 PM
  • See the below link:

    Retrieving Identity or Autonumber Values (ADO.NET)


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, July 1, 2011 12:17 PM
  • If you are using a DataAdapter, then you can do what I do, which is...

    After creating your DataAdapter, add a Handler to process the RowUpdated() method:

    ' Check If Table Has an ID Column
    If Me.Tables(p_strTableName).Columns.Contains("ID") Then
       ' Create RowUpdate Event Handler For Identity Field Retrieval
       AddHandler objAdapter.RowUpdated, New SqlClient.SqlRowUpdatedEventHandler(AddressOf Me.DataAdapter_OnRowUpdate_GetIdentity)
    End If

    Then create the method that processes the RowUpdated() event:

        Private Sub DataAdapter_OnRowUpdate_GetIdentity(ByVal Sender As Object, ByVal e As SqlClient.SqlRowUpdatedEventArgs)
          ' Procedure/Function: DataAdapter_OnRowUpdate_GetIdentity()
          ' Author: Ben Santiago
          ' Created On: 08/04/2006
          ' Description:
          '    On RowUpdate() this method will retrieve the ID (Identity) field
          '    value created by SQL Server and store it into the local DataSet/Table.
          If e.StatementType = StatementType.Insert Then
            ' Initialize Variables
            Dim objSQLCommand As New SqlClient.SqlCommand("SELECT @@IDENTITY", e.Command.Connection)
            ' Retrieve Identity Value For Current 
            ' Row & Store In DataSet/Table
            If e.Row.RowState <> DataRowState.Deleted Then
              If IsDBNull(e.Row("ID")) Then
                e.Row("ID") = objSQLCommand.ExecuteScalar()
                If Not IsDBNull(e.Row("ID")) Then
                End If
              End If
            End If
          End If
        End Sub

    Hope This Helps!


    Friday, July 1, 2011 1:43 PM