0

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
2
Views
7 Years
Discussion Span
Last Post by Ap0ca1ypse
0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.