Member Avatar for Ap0ca1ypse

Hello,

I can pull 3 tables data into my program and read from all of them, i can update all 3 datasets but when it comes to updating the actual database i can only send the updates from the last dataset.

Imports System.Data
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Public Sub OpenAllTables()
            'CONNECTIONSTRING - THIS IS WHERE THE CONNECTION TAKES PLACE
            con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\Danny\Desktop\Financial Data\FBS.mdb"
            con.Open()                               

            sql = "SELECT * FROM tblIncome"              
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "Income")
            sql = "SELECT * FROM tblSavings"             
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "Savings")
            sql = "SELECT * FROM tblExpenditure"      
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "Expenditure")

            con.Close()                               
        End Sub

The above code is how i call the three tbls, the wierd thing is that if i switch the order (savings with expenditure) then savings will be allowed to get updated and not expenditure.

Any Ideas??

  • 1 Contributor
  • 1 Reply
  • 123 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by Ap0ca1ypse

Dani AI

Generated

The behavior you described is exactly the symptom of reusing the same adapter variable for multiple tables: each New OleDbDataAdapter(...) overwrote the previous adapter object, so only the last adapter remained available when you called Update. Swapping the SELECT order only changed which adapter survived — that explains why only the last table could be pushed to the database. found a practical fix by keeping separate adapters; that is the correct approach.

A compact, reusable pattern is to keep one DataSet with multiple DataTables but keep a separate OleDbDataAdapter per table (store them in a collection so you can Update each table later). Example pattern in VB.NET:

Dim con As New OleDb.OleDbConnection(connString)
Dim ds As New DataSet()
Dim adapters As New Dictionary(Of String, OleDb.OleDbDataAdapter)()

For Each tbl In New String() {"tblIncome","tblSavings","tblExpenditure"}
    Dim sel As String = "SELECT * FROM " & tbl
    Dim ad As New OleDb.OleDbDataAdapter(sel, con)
    adapters.Add(tbl, ad)
    ad.Fill(ds, tbl)
Next

' When saving changes:
For Each kvp In adapters
    Dim cb As New OleDb.OleDbCommandBuilder(kvp.Value)
    kvp.Value.Update(ds, kvp.Key)
Next

Important notes and troubleshooting tips:

  • CommandBuilder only generates Insert/Update/Delete commands if the SELECT returns a primary-key column (or if you set DataTable.PrimaryKey after fill). If no key exists, either add a PK in the DB or supply parameterized commands manually.
  • Keep each adapter (or its CommandBuilder) available at least until after Update is called, or explicitly assign InsertCommand/UpdateCommand/DeleteCommand.
  • For production, prefer explicit parameterized commands rather than relying on CommandBuilder for correctness and performance.
  • If using Jet (Microsoft.Jet.OLEDB.4.0), remember 64-bit compatibility issues — consider ACE (Microsoft.ACE.OLEDB.12.0) or compiling for x86.

See the docs on the CommandBuilder and DataAdapter.Update for details: OleDbCommandBuilder and DataAdapter.Update.

Member Avatar for Ap0ca1ypse

After i left it for a bit i cam up with an idea,

I setup 3 new dataset commands and separate update commands and it worked perfectly.

Imports System.Data
    Class AllDBUses
        Dim con As New OleDb.OleDbConnection
        Dim dsExp As New DataSet
        Dim dsSav As New DataSet
        Dim dsInc As New DataSet
        Dim daExp As OleDb.OleDbDataAdapter
        Dim daSav As OleDb.OleDbDataAdapter
        Dim daInc As OleDb.OleDbDataAdapter
        Dim sql As String
        Public Sub OpenAllTables()

            con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Users\Danny\Desktop\Financial Data\FBS.mdb"
            con.Open()                           

            sql = "SELECT * FROM tblIncome"              
            daInc = New OleDb.OleDbDataAdapter(sql, con)
            daInc.Fill(dsInc, "Income")
            sql = "SELECT * FROM tblSavings"              
            daSav = New OleDb.OleDbDataAdapter(sql, con)
            daSav.Fill(dsSav, "Savings")
            sql = "SELECT * FROM tblExpenditure"          
            daExp = New OleDb.OleDbDataAdapter(sql, con)
            daExp.Fill(dsExp, "Expenditure")

            con.Close()                                
        End Sub
End Class

Now i can update any table as long as a specify the command to look at that table

Dim cbExp As New OleDb.OleDbCommandBuilder(daExp)
'<Do some update here>'
daExp.Update(dsExp, "Expenditure")

Hope this helps anybody else.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.