Retrieving @@Identity from Access database table

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

StephenMcGuire at 2007-8-31 >

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.

Simmy7 at 2007-8-31 >

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.

alazela at 2007-8-31 >