Retrieving Primary Key

[smcgarry]

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

[Carnifex]

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.

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.

[smcgarry]

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.

[Carnifex]

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.