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
- the code that I insert in the dataset
- 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