VB 2019 read/write DataTable directly

Question

My application manages construction projects, which can change many times before product is ordered. My goal is to create an audit trail of changes so that they can be verified or explained to the customer, and so that mistakes and malfiance can be caught and corrected. save a separate copy of the current record in memory. When the record is saved, I want to compare it to the copy in memory for changes. If any, I want to create a record in the same table with the original data. The DateChanged field will get the current date/time (which remains null for the original record). There is also a User_ID field to document who created this record.

My problem is figuring out how to manipulate data that is not on the user's form. I suppose that I can create invisible text controls, but that's rather clunky. I'm sure that there are ways to manipulate the data directly in the DataTable, but I've found no decipherable and workable example so far. What I have found is either for VB6 (which I know but I no longer have the compiler/IDE, it won't run easily on Windows 10 and is unsupported as well) or depends on some parameters outside the VB code they show ... please note that I specified the parameters I used. Idealy I'd directly read from and write to the DataTable without involving the form at all.

The example code below is from a book, which is based on VB 2019, hence the reason I'm not using VB 2022.

Table structure:
Author - Short Text, primary key
Year_Born - Integer
User_ID - Short Text
DateChanged - Date/Time

' Visual Basic 2019. The database was created in Access 2019.
' Target CPU has been set to x64.
' In Manage NuGet I have added system.data.oledb, version 6.00.
' I added the reference for "Microsoft ActiveX Data Objects 6.1 Library"
Imports System.Data
Imports System.Data.OleDb
Public Class frmAuthor
    Dim BooksConnection As OleDbConnection
    Dim AuthorsCommand As OleDbCommand
    Dim AuthorsAdapter As OleDbDataAdapter
    Dim AuthorsTable As DataTable
    Dim AuthorsManager As CurrencyManager ' Currency as in "Keeping current", not money
    Dim MyState As String = "View" ' for noticing state transitions
    Dim MyBookmark As Integer ' for cancelling adds
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BooksConnection = New OleDbConnection ' (ConnectionString)
        BooksConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; " _
            + "Data Source = C:\VBDB\BooksDb.accdb"
        BooksConnection.Open()
        AuthorsCommand = New OleDbCommand("SELECT * from Authors ORDER BY Author", BooksConnection)
        AuthorsAdapter = New OleDbDataAdapter()
        AuthorsAdapter.SelectCommand = AuthorsCommand
        AuthorsTable = New DataTable()
        AuthorsAdapter.Fill(AuthorsTable)
        txtAuthorName.DataBindings.Add("Text", AuthorsTable, "Author")
        txtYearBorn.DataBindings.Add("Text", AuthorsTable, "Year_Born")
        AuthorsManager = DirectCast(Me.BindingContext(AuthorsTable), CurrencyManager)
        Me.Show()
        Call SetState("View")
    End Sub
End Case

Answer 1

That code appears to be rather old, due to the fact that it uses BindingContext. What you should do is add a BindingSource to your form in the designer, then bind your DataTable to the BindingSource and the BindingSource to your controls, e.g.

myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource
myTextBox.DataBindings.Add("Text", myBindingSource, "MyColumn")

If you are using a grid then the user can select a row in it. Otherwise, you can use a BindingNavigator to navigate or add your own navigation Buttons and call MoveNext, MovePrevious, etc, on the BindingSource.

You can get the current row from the Current property of the BindingSource. That property is type Object but the item itself is type DataRowView, so you can cast as that type:

Dim currentRow = DirectCast(myBindingSource.Current, DataRowView)

You can then get and set fields in that row by index or column name, e.g.

Dim number = CInt(currentRow(columnIndex))
Dim text = CStr(currentRow(columnName))

currentRow(columnIndex) = number
currentRow(columnName) = text

A DataRowView can be used pretty much like a DataRow for most purposes but, if you need the corresponding DataRow specifically, you can get it from the Row property:

Dim currentRowView = DirectCast(myBindingSource.Current, DataRowView)
Dim currentRow = currentRowView.Row

Answer 2

The DataTable is an in-memory object and you can compare rows in it without binding...

Module Program
  Const UPDATEDATE As String = "UpdateDate"
  Sub Main(args As String())

    Dim dtb = GetDemoDataTable()
    Dim oldRow = dtb.Rows(0)
    Dim newRow = dtb.Rows(1)
    'compare rows
    Dim sb As New StringBuilder
    For Each col As DataColumn In dtb.Columns
      If col.ColumnName = UPDATEDATE Then Continue For 'skip updatedate column
      If newRow.Item(col) <> oldRow.Item(col) Then
        sb.AppendLine($"{newRow(UPDATEDATE)} - {col.ColumnName} changed from ""{oldRow.Item(col)}"" to ""{newRow.Item(col)}"" ")
      End If
    Next

    Console.Write(sb.ToString)
    Console.WriteLine("FINISHED")
    Console.ReadKey()

  End Sub

  Function GetDemoDataTable() As DataTable
    Dim output As New DataTable
    output.Columns.Add(New DataColumn(UPDATEDATE, GetType(Date)))
    output.Columns.Add(New DataColumn("Scope", GetType(String)))
    output.Columns.Add(New DataColumn("Cost", GetType(Decimal)))
    output.Rows.Add(New Date(2023, 2, 1), "Floor and walls", New Decimal(1234))
    output.Rows.Add(New Date(2023, 2, 2), "Floor, walls, and ceiling", New Decimal(1234))
    Return output
  End Function

End Module

Output of this console app:

2/02/2023 12:00:00 AM - Scope changed from "Floor and walls" to "Floor, walls, and ceiling"
FINISHED

DataRow itself also supports versioning, but I'm not familiar with it: see https://learn.microsoft.com/en-us/dotnet/api/system.data.datarow.rowstate?f1url=%3FappId%3DDev16IDEF1%26l%3DEN-US%26k%3Dk(System.Data.DataRow.RowState)%3Bk(DevLang-VB)%26rd%3Dtrue&view=net-6.0