Handling autonumber/key fields in a dataset

Problem: If you want to use a dataset and dataadapter exclusively for managing the updates to a table, and the table was designed with an autonumber field as as the primary key, you have to fill in the primary key after the record is inserted.

Solution: The insert statement of the data adapter can't contain the primary key column. You let the underlying data provider fill in the key value. I have taken two solution routes, but they do the same thing. After inserting the row, I updated the row in the mainline code with the new key value in the datagridview and the datatable. My second, more elegant solution, was to attach a row updated event handler to the data adapter. I then fill in the primary key value in the data row. The datagridview picked up the update automatically.


 Private Sub Add_Autonumber(s As Object, e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles TViewAdapter.RowUpdated
        If e.StatementType = StatementType.Insert Then
            Dim NewID As Integer
            Dim idCMD As New OleDbCommand("SELECT @@IDENTITY", e.Command.Connection)
            NewID = idCMD.ExecuteScalar
            e.Row("ID") = NewID
        End If
    End Sub

SELECT @@IDENTITY is specific to the oledbconnection for Access databases. SQL has a similar statement. You must do this on the connection that generated the autonumber, hence the command references the rowupdated argument's connection that was passed in