Retrieving Primary Key

I have a form that is managing and updating data in the dataset. When I write the data back to my table using the update method, I need to refresh the dataset so that I can get the identity value that was inserted into the table (if it was a new record). I tried using the AcceptChanges method but that didn't seem to work. Any other ideas?

Thanks

Shane,

Add a handler for the data adapters row updated event. Point it too a sub that uses SQL and Accesses @@Select statement. (Select statement gets the ID of the last record to be inserted or modified)

ie.

VB Code:

AddHandler myAdapter.RowUpdated, AddressOf mySub
 
....
 
Private Sub mySub(ByVal sender As Object, ByVal e As SqlClient.SqlRowUpdatedEventArgs)
 
Dim cmdIdentity As New SqlClient.SqlCommand("Select @@Identity", sqlConnection)
            Dim Row As DataRow
            Dim RowID As Integer
 
            'Get the row that is being updated in the DataSet
            Row = e.Row
 
            Try
                'Get the updated row ID from the database
                RowID = cmdIdentity.ExecuteScalar
 
                'Update the datatable row
                Row.Item("myColumn") = RowID
            Catch ex As Exception
                Throw New Exception("Unable to retrieve ID")
            End Try
 
End Sub

This code will fire whenever a row is being updated by the data adapter, and change the ID of the row right after the update in the dataset.

That didn't work. It throw an exception stating the it could not convert type DBNull to Long which means the "Select @@Identity" is returning null. Why doesn't the acceptchanges work? I thought the dataset would refresh itself from the dataprovider when you called the acceptchanges method.

Accept changes won't work because a datatable is not connected to the database in any form. Hence the connectionless nature of Datasets/Tables. The only way the datatable knows it's being updated is thru the dataadapter. (ie when you use the data adapters update command via the data table)

@@Identity has always worked fine for me in Access and SQL. I suggest you preform an update or insert on the database, and then if using SQL use Query Analyzer and the Identity command in there.