event handling blues

[PerplexedB]

I need functionality to retrieve the new pk, like I could in DAO. I would like my "nLog" function (see code below) to return the new pk.

I have found this, and I was able to make it work fine in my environment, so I'm pretty sure the problem I have is caused by my failure to understand event handlers and possibly partial classes and inheritance in VB.Net. And not because of my use of the access database, or because the "select @@identity" does not work. It all works OK, so there is not need to be bothered by that.

In the code below that I write in dsLog.vb (dsLog is a visually designed dataset), the code here is (written by me) and inserted by .net.

My problem is I don't understand how I can get the "RowUpdated" event to fire.

If I understand this correctly, logTableAdapter wraps the actual tableadapter which is why the IDE won't let me code the obvious ...

AddHandler me.RowUpdated, AddressOf HandleRowUpdated

On the other hand, this object has it's CANRAISEEVENTS set to TRUE just before the call to the ADDHANDLER statement.

Please find below

1. the code that I insert in the dataset
2. the code in my "modLog" module

When I execute ? nLog("Testing") from the immediate window, the record is inserted in the table, but the function returns a frustrating 0.

Thanks for any light you could shed on this.

Imports System.Data.OleDbx



Namespace dsLogTableAdapters
    
    Partial Public Class logTableAdapter
        Dim cmdGetPK As OleDbCommand
        Public nPK As Long

        Public Sub InitiateGetPK()
            cmdGetPK = New OleDbCommand()
            cmdGetPK.CommandText = "SELECT @@identity"
            MsgBox(Me.CanRaiseEvents.ToString)
            AddHandler me.Adapter.RowUpdated, AddressOf HandleRowUpdated
            'AddHandler me._Adapter.RowUpdated, AddressOf HandleRowUpdated
        End Sub

        Private Sub HandleRowUpdated(ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)
            If e.Status = UpdateStatus.Continue AndAlso e.StatementType = StatementType.Insert Then
                ' Get the Identity column value
                e.Row("ID") = Int32.Parse(cmdGetPK.ExecuteScalar().ToString())
                Me.nPK = (e.Row("ID"))
                e.Row.AcceptChanges()
            End If
        End Sub
    End Class

End Namespace

Imports System.Data.OleDb

Public Enum LogType
    logNormal = 1
    LogNiceTohave = 2
    LogError = 3
    logTerminalError = 4
End Enum

Public Enum LogAction
    logcreate = 1
    logUpdate = 2
    logDelete = 3
    logNa = 4
End Enum



Module modLog

    Public Function nLog(ByVal cDescription As String, Optional ByVal cCaller As String = "Immediate Window") As Integer
        Dim da As New dsLogTableAdapters.logTableAdapter
        Dim c As String = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString()
        da.InitiateGetpk()
        Dim n As Integer = da.Insert(My.Computer.Name, Environment.GetEnvironmentVariable("username"), LogType.LogNiceTohave, LogAction.logNa, Now, 0, "", cDescription, cCaller)
        Return da.nPK
    End Function
    
End Module

[PerplexedB]

Alright, the solution is not as brilliant as I had hoped, but I think it's much simpler than anything I've seen until now.

All you really need to do is to execute the SELECT @@IDENTITY using the same connection as the INSERT command.

The problem was that I want to use generated code, and that this opens and closes the connection. Well it does, if you did not open the connection in the first place, in fact what the generated code does is restore the state of the connection.

So all it takes really is to open the connection of the underlying dataadapter, set the SELECT @@IDENTITY command connection to this connection, execute the insert statement, execute the SELECT @@IDENTITY statement and then close the connection. Yes.... it's that simple.

The next challenge is to encapsulate this logic ... I guess.

Imports System.Data.OleDb

Public Enum LogType
    logNormal = 1
    LogNiceTohave = 2
    LogError = 3
    logTerminalError = 4
End Enum

Public Enum LogAction
    logcreate = 1
    logUpdate = 2
    logDelete = 3
    logNa = 4
End Enum



Module modLog

    Public Function nLog(ByVal cDescription As String, Optional ByVal cCaller As String = "Immediate Window") As Integer
        Dim da As New dsLogTableAdapters.logTableAdapter
        Dim c As String = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString()
        Dim cmdGetPK = New OleDbCommand()
        cmdGetPK.CommandText = "SELECT @@identity"
        da.Connection.Open()
        Dim n As Integer = da.Insert(My.Computer.Name, Environment.GetEnvironmentVariable("username"), LogType.LogNiceTohave, LogAction.logNa, Now, 0, "", cDescription, cCaller)
        cmdGetPK.Connection = da.Connection
        Dim n2 As Long = cmdGetPK.ExecuteScalar()
        da.Connection.Close()
        nLog = n2
    End Function
    
End Module