Problems updating a database using vb.net, oledbdataadapter

Question

After going over multiple questions/answers on Stackoverflow and other boards I'm still lost on why I can't update an Access database from a datatable. I'm trying to take data from a datatable and insert that data into an Access table if it is blank, and replace the table if it already has data. I can successfully replace the table, but the data from the datatable does not get added.

However, the method which I'm using does not appear to work. My datatable comes from a bound datagridsource and the Access layer is called like this:

ConnectedDB.UpdateTable(DBTable, bsDataSource.DataSource)

Where ConnectedDB is the Access Layer class, DBTable is the string containing the Access table name, and bsDataSource is the bound data. As you can see, I passed the .Datasource to turn it into a datatable.

Here is the original (pre-Jan 29th) section of my work to add the datatable back into the Access table:

 Public Function UpdateTable(strTable As String, dgDataTable As DataTable) As Boolean
        Dim DS As New DataSet
        dgDataTable.TableName = strTable
        DS.Tables.Add(dgDataTable)
        Using OpenCon = New OleDb.OleDbConnection(strConnectionString)
            Using DataAdapter As New OleDbDataAdapter("SELECT * FROM " & strTable, OpenCon)
                Dim DBcmd As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter)
                DBcmd.QuotePrefix = "["
                DBcmd.QuoteSuffix = "]"
                DataAdapter.UpdateCommand = DBcmd.GetUpdateCommand()
                Try
                    OpenCon.Open()
                    DataAdapter.Fill(DS.Tables(strTable))
                    If DataAdapter.Update(DS.Tables(strTable)) > 0 Then
                        Return True
                    Else Return False
                    End If
                Catch exo As Exception
                    MessageBox.Show(exo.Message)
                    Return False
                End Try
            End Using
        End Using
    End Function

My function tries to update an existing Access table with the name represented as strTable with the information in the datatable, dgDataTable from a datagridview. Each run hits the update check > 0 and returns a false which means syntax wise it should be working (i.e. no error messages). I have traced the table and it has all the data it should have (so the information is getting passed correctly from the grid through the update commands). I was playing with applying it in a dataset but I'm not sure I really need that.

I was tracing the variables through the update method and I think I found out why it won't update but I'm not sure what to do about it. The query it comes up with is like this:

UPDATE [RtoC] SET [R] = ?, [C] = ?, [Type] = ?, [Unknown] = ? WHERE (([R] = ?) AND ([C] = ?) AND ([Type] = ?) AND ((? = 1 AND [Unknown] IS NULL) OR ([Unknown] = ?)))

The Access table name is RtoC with fields R, C, Type, and unknown. I'm thinking the "?" are not getting filled in causing the query to just not apply data back to Access. I'm not sure though how to set those items.

EDIT 1/29/20: I used the code changes I and jmcihinney document below and it does insert the lines into the Access table. This edit alters the question to be more specific about what I'm am trying to do, and how the datatable is created. Hopefully this clears up some wording on my part and provides some basis for the alteration of the row state.

Answer 1

I took another tack at manipulating the database and in looking that up, I found the answer provided by jmcilhinney back in 2014! [Bulk Insert From DataTable to Access Database

In a for each loop across the rows of my datatable I set this: row.SetAdded()

If I was filling I would have done something like: DataAdapter.AcceptChangesDuringFill = True Before the Fill command.

Unless this method has changed or there is a better way, I'll mark the link as the answer.

Thanks jmcilhinney....twice!

Answer 2

The issue is that the Fill method of that data adapter calls AcceptChanges on the DataTable after populating it, thus there are no changes to save when you call Update.

That call to Fill shouldn't be there anyway though, because you don't want to retrieve any data, just save changes. You've got a whole lot of pointless code there. It should look more like this:

Public Function UpdateTable(strTable As String, dgDataTable As DataTable) As Boolean
    Using DataAdapter As New OleDbDataAdapter("SELECT * FROM " & strTable, strConnectionString)
        Dim DBcmd As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter)

        DBcmd.QuotePrefix = "["
        DBcmd.QuoteSuffix = "]"

        Try
            Return DataAdapter.Update(dgDataTable) > 0
        Catch exo As Exception
            MessageBox.Show(exo.Message)
            Return False
        End Try
    End Using
End Function