Saving back to an Access database

[Pianoman]

This is still driving me mad:
I've written a Windows form application in Visual Studio 2017. It contains several forms displaying Dataviewgrids. These get their information from a Microsoft Access database using a DBControl class as follows:

mports System.Data.OleDb
Imports System.IO
Public Class DBControl
    'CREATE YOUR DB CONNECTION
    Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
                                         "Data Source=Phase10.accdb;")
    'PREPARE DB COMMAND
    Private DBCmd As OleDbCommand
    'DB DATA
    Public DBDA As OleDbDataAdapter
    Public DBDT As DataTable
    Public DBDS As New DataSet
    'QUERY PARAMETERS
    Public Params As New List(Of OleDbParameter)
    'QUERY STATISTICS
    Public RecordCount As Integer
    Public Exception As String
    Public Sub ExecQuery(Query As String)
        'RESET QUERY STATS
        RecordCount = 0
        Exception = ""
        Try
            'OPEN A CONNECTION
            DBCon.Open()
            'CREATE DB COMMAND
            DBCmd = New OleDbCommand(Query, DBCon)
            'LOAD PARAMS INTO DB COMMAND
            Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
            'CLEAR PARAMS LIST
            Params.Clear()
            'EXECUTE COMMAND & FILL OUR DATATABLE
            DBDS = New DataSet
            DBDT = New DataTable
            DBDA = New OleDbDataAdapter(DBCmd)
            RecordCount = DBDA.Fill(DBDT)
        Catch ex As Exception
            Exception = ex.Message
        End Try
        'CLOSE YOUR CONNECTION
        If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Sub
    'INCLUDE QUERY & COMMAND PARAMETERS
    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New OleDbParameter(Name, Value)
        Params.Add(NewParam)
    End Sub
End Class

The forms get their information from specific tables in the database through:

Public Sub RefreshGrid()
        'RUN QUERY
        Access.ExecQuery("SELECT * FROM FullDB")
        If NotEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub
        'FILL DATAGRID 
               dgvData.DataSource = Access.DBDT
               TbTotal.Text = dgvData.Rows.Count
    End Sub

Everything works exactly as it should, so I'm a happy bunny; BUT, as the application does many calculations which result in data changes/additions I need to be able to save the new/amended data back to the original Access database.
I've been on numerous forums and had numerous pieces of information!!!! The problem is that I'm a beginner to programming despite being in my 70s, so explanations need to be simple and fully explained.

I've been told I need to have a "Binding source", but I've no idea what it does nor where it fits into the process.

One contributor today told me I need to revamp my DBControl as follows:

Imports System.Data.OleDb
Public Class DBControl
    Private m_Connection As OleDbConnection
    Private m_Command As OleDbCommand
    Private m_Parameters As New List(Of OleDbParameter)
    Private m_RecordCount As Integer
    Private m_ExceptionMessage As String

    Public Property ExceptionMessage() As String
        Get
            Return m_ExceptionMessage
        End Get
        Private Set(ByVal Value As String)
            m_ExceptionMessage = Value
        End Set
    End Property
    Public Property RecordCount() As Integer
        Get
            Return m_RecordCount
        End Get
        Private Set(ByVal Value As Integer)
            m_RecordCount = Value
        End Set
    End Property
    Public Sub New(ByVal COleDbConnection As OleDbConnection)
        m_Connection = COleDbConnection
    End Sub
    Public Sub AddParameter(Name As String, Value As Object)
        m_Parameters.Add(New OleDbParameter(Name, Value))
    End Sub
    Public Function ExecuteQuery(ByVal CQuery As String, ByRef ODataAdapter As OleDbDataAdapter, ByRef ODataTable As DataTable)
        ExecuteQuery = False
        m_ExceptionMessage = ""
        m_RecordCount = -1
        Try
            m_Connection.Open()
            Using command As New OleDbCommand(CQuery, m_Connection)
                m_Parameters.ForEach(Sub(p) command.Parameters.Add(p))
                m_Parameters.Clear()
                ODataTable = New DataTable
                ODataAdapter = New OleDbDataAdapter(command)
                m_RecordCount = ODataAdapter.Fill(ODataTable)
            End Using
            m_Connection.Close()
            ExecuteQuery = True
        Catch exception As Exception
            m_ExceptionMessage = exception.Message
        End Try
    End Function
End Class

I'm at a total loss as to where to go with this so can anyone help me?

 

[jmcilhinney]

There really is way too much code there. By posting lots of irrelevant code, you actually obscure the issue and make it harder for us to focus on that issue and thus less likely that we'll take the time to help you.

Saving data using ADO.NET is easy. You already have a method that will accept a query in a String and output a data adapter and a DataTable. That's a weird way to do things but so be it. You would then edit the data by making changes to that DataTable, which would presumably happen by it being bound to a DataGridView, preferably via a BindingSource. You would then save those changes by calling Update on the same data adapter and passing the same DataTable as an argument. For that Update call to work, you need to have valid InsertCommand, UpdateCommand and DeleteCommand properties on the data adapter if you have added, modified and deleted rows. Assuming that your query involved a single table and retrieved the primary key, the easiest way to do that is with a command builder, e.g.

Public Function SaveChanges(adapter As OleDbDataAdapter, table As DataTable) As Integer
    Dim builder As New OleDbCommandBuilder(adapter)
    Dim result = adapter.Update(table)

    builder.DataAdapter = Nothing

    Return result
End Function

There are some potential issues there for other than the simplest cases but I'll let you research that.

 

[jmcilhinney]

By the way, you really, really ought to turn Option Strict On in your project and also in the VS options, so it will be On by default for future projects. You have declared a Function there with no return type and that doesn't return anything. That's dodgy. A method that doesn't return anything should be a Sub and you would have been informed of the issue if you had Option Strict On. There are almost certainly other issues with your code that are flying under the radar for the same reason. Having Option Strict On helps you write more robust code and also learn how to use the correct data types more quickly.