Retrieving @@Identity from Access database table

Retrieving @@Identity from Access database table

• Alex Farber

  using the code in the post I referenced in my initial post this is the code I am trying to put in the partial class:
  
  Partial Class RosDBDataSet
  
  Partial Public Class LibraryNotesTableAdapter
  Private IdentityQuery As New System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", Nothing)
  
  Private Sub m_adapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
  If e.StatementType = StatementType.Insert AndAlso _
  e.Status = UpdateStatus.Continue Then
  IdentityQuery.Connection = e.Command.Connection
  e.Row("NoteID") = IdentityQuery.ExecuteScalar
  End If
  End Sub
  End Class
  
  End Class
  
  
  The folowing is the code generated by VB in the designer.vb file that defines the tableadapter:
  Partial Public Class LibraryNotesTableAdapter
  Inherits System.ComponentModel.Component
  
  Private WithEvents _adapter As System.Data.OleDb.OleDbDataAdapter
  
  Private _connection As System.Data.OleDb.OleDbConnection
  
  Private _commandCollection() As System.Data.OleDb.OleDbCommand
  
  Private _clearBeforeFill As Boolean
  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
  Public Sub New()
  MyBase.New
  Me.ClearBeforeFill = true
  End Sub ..............
  
  
  When I put the code directly into the designer.vb file it works fine like this:
  Partial Public Class LibraryNotesTableAdapter
  Inherits System.ComponentModel.Component
  
  Private WithEvents _adapter As System.Data.OleDb.OleDbDataAdapter
  
  Private _connection As System.Data.OleDb.OleDbConnection
  
  Private _commandCollection() As System.Data.OleDb.OleDbCommand
  
  Private _clearBeforeFill As Boolean
  Private IdentityQuery As New System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", Nothing)
  
  Private Sub m_adapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles _adapter.RowUpdated
  If e.StatementType = StatementType.Insert AndAlso _
  e.Status = UpdateStatus.Continue Then
  IdentityQuery.Connection = e.Command.Connection
  e.Row("NoteID") = IdentityQuery.ExecuteScalar
  End If
  End Sub
  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
  Public Sub New()
  MyBase.New
  Me.ClearBeforeFill = true
  End Sub ...........
  
  The obvious probelm is that any changes made to the dataset using the designer will overwrite code I added.
  
  

• frieste

  I haven't looked at the generated code for a typed dataset, but the way you've declared the partial class looks a bit strange. Does the generated code actually declare the TableAdapter as an internal class of the typed dataset class The code you've described shows the generated TableAdapter partial class declared by itself, but your own code wraps it inside of a typed dataset partial class.
  
  
  

• Andrei Faber

  So I would just put this in after the call to the update method of the tableadapter
  

• Ronnie Smith

  ok, thanks Dman for confirming.
  
  

• bbossi

  actually you are best to use Scope_Identity() and not @@Identity. I'm not sure if Access supports it but if it does, use it :-) My apologies if it does not
  
  

• sanwanas

  Access does not support Scope_Identity
  
  

• Chimme

  I just copied the relevant part parts of the dataset declaration and within that the tableadapter class declaration that are genarated by the designer to show where I can succesfully put the code (but have the problem of it being overwritten when the dataset is changed and saved in the designer) I also included the partial class whose "shell" is created by VS and the code I copied into it from the post I referenced at the begining of this thread.
  
  

• joynerCN

  What partial class are you extending with your code Have you checked the generated code to make sure that you attaching the correct field name _adapter should be a private field, not an event. Finally, what provider are you wrapping DbDataAdapter itself doesn't implement the RowUpdated event, so each provider has to implement it their adapter.
  
  
  

• Ana Maria Bisbe

  To retrive the @@IDENTITY:

  Private Function GET_Identity()

  Try

  ' Include a variable and a command to retrieve the identity value from the Access database

  Dim newID As Integer = 0

  Dim idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY From myTable", myConnection)

  ' Retrieve the identity value

  newID = CInt(idCMD.ExecuteScalar())

  Return newID

  Catch myException As Exception

  MessageBox.Show(myException.Message, _

  "GET_Identity error...", _

  MessageBoxButtons.OK, MessageBoxIcon.Error)

  End Try

  End Function

  Steve

  
  
• Retrieving @@Identity from Access database table