0

I am attempting to write a function which deletes the contents of a MS Access table and replaces it with that of a datatable that contains the same columns.

So far I have the following, however upon running the function I receive a result of 0, indicating no updated rows.

Public Function UpdateMyTableContents(ByVal tblNew As DataTable) As Integer
        Dim oleConn As OleDb.OleDbConnection                        '| Create & Configure
        oleConn = New OleDb.OleDbConnection(strConnection)          '| connection object

        Dim oleCmd As New OleDb.OleDbCommand()                      '| Create & Configure
        oleCmd.Connection = oleConn                                 '| command object
        oleCmd.CommandText = "Delete * from tbl_MyTable"

        oleConn.Open()                                              '| Delete the old
        Dim intDeletedRows As Integer = oleCmd.ExecuteNonQuery()    '| table's contents.
        oleConn.Close()                                             '|

        'Setup Data Adapter to write new contents
        Dim oleDA As New OleDb.OleDbDataAdapter()
        oleDA.InsertCommand = New OleDb.OleDbCommand("INSERT INTO tbl_MyTable ([Company], [Brand], [Description]) VALUES (@Company, @Brand, @Desc)", oleConn)
        oleDA.InsertCommand.Parameters.Add("Company", OleDb.OleDbType.VarChar, 20, "Company")
        oleDA.InsertCommand.Parameters.Add("Brand", OleDb.OleDbType.VarChar, 100, "Brand")
        oleDA.InsertCommand.Parameters.Add("Desc", OleDb.OleDbType.LongVarChar, 1000, "Product Description")
        
        'Let's do it
        oleConn.Open()
        dim result as integer = oleDA.Update(tblNew)
        oleConn.Close()

        return result
    End Function

As well as not being entirely sure of what the integer in the .InsertCommand.Parameters.Add command is supposed to represent (I assumed a maximum field length?), I don't understand why the table does not populate with the new data.

Does anyone have any idea where I've gone wrong?

Doofitator
  • 60
  • 1
  • 6
  • Does this answer your question? [VB.Net - Inserting data to Access Database using OleDb](https://stackoverflow.com/questions/35759150/vb-net-inserting-data-to-access-database-using-oledb) – June7 Jun 27 '20 at 08:42
  • Thanks for the link, but no. That only populates one row of the table based on textbox input, not from a Datatable. Additionally, it throws a 'Type name is invalid.' error :( – Doofitator Jun 27 '20 at 09:32
  • After solving the 'Type name is invalid' error, I can confirm that it works but is incredibly slow. Taking over 12 minutes to copy the data required, as opposed to the near instant copy performed between two .accdb files manually. – Doofitator Jun 27 '20 at 10:06
  • It sounds like the specific problem you asked about in this question is resolved, so you should post a solution and accept it. If you now have a different problem, you should post a new question and provide all and only the information relevant to that. – jmcilhinney Jun 27 '20 at 11:57
  • And give the users of this site another excuse to downvote me and make it impossible for me to post for another two years? Not likely – Doofitator Jun 27 '20 at 12:14
  • @Doofitator, while I feel your pain on the down votes and understand some are pretty quick to hit the down vote button. Perhaps the reason for down votes is in the way you've asked questions? – Hursey Jun 27 '20 at 22:24
  • See if this approach is any faster. Check the response to clear up some errors. https://stackoverflow.com/questions/27907627/insert-a-data-table-to-access-database-using-c-sharp – wazz Aug 20 '20 at 08:53

0 Answers0