none
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.  http://msdn.microsoft.com/en-us/library/5ycd1034.aspx

    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

Answers

All replies

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

     http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/0c77a13e-5b54-43c3-af64-94f0ce557ae1/

    Regards

    Gaspard

    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
                  e.Row.AcceptChanges()
                End If
              End If
            End If
          End If
        End Sub


    Hope This Helps!

    -Ben

    Friday, July 1, 2011 1:43 PM