Trouble synching with DataSet/Database.

[qwert231]

I have some code that creates a new recordset in a table with an autonumber field. It then updates the database with the changes from the dataset. I then try to retrieve that new autonumbered field from the dataset, but I get a 0.

How can I get the autogenerated number?

Here is how I am trying now. refreshDb uses a dataAdapter to update the datasource with the Dataset's changes.

Me.DsGlobal1.Tables("SubjectData").Rows.Add(newRow)
Me.refreshDb()
idxNum = Me.DsGlobal1.Tables("SubjectData").Rows.Count - 1
newRec = Me.DsGlobal1.Tables("SubjectData").Rows(idxNum).Item("recNum")

 

[icca]

first you have to reload your dataset. if you have a counter as PK you pick the row with largest value.

/icca

 

[qwert231]

That would work also, thanks. However, I eventually worked it out by doing this:

Private Sub tryCheckINSERT(ByVal sender As Object, ByVal args As OleDb.OleDbRowUpdatedEventArgs) Handles daSubjects.RowUpdated
  Dim newID As Integer = 0
  Dim idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", subjCon)
  If args.StatementType = StatementType.Insert Then
   ' Retrieve the identity value and store it in the CategoryID column.
   newID = CInt(idCMD.ExecuteScalar())
   args.Row("recNum") = newID
  End If
 End Sub

This works while the connection that the DataAdapter uses is still open to pull the current record's primary key. I don't know how this would play with a table that has multiple primary keys, but I tend to stick to one anyways. The command simply fires imediatly after your Insert command, and only on INSERT statements.