Issue updating one mdb table out of a selection

[Ap0ca1ypse]

Hello all,

I am creating a project for myself to practice Database connectivity, updating, deleting and adding items.

I am having an issue updating one of the tables i have added, within a table there is a column called Today's Date which will contain the date the last time the program ran, when i run the program i want it to update that column with the new date.

Imports FinancialBalanceSheet.AllAccessDBConnections    

'Form 1 Data
Public Class StarterPage
    Private Sub StarterPage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim objDBItems As AllDBUses   
        objDBItems = New AllDBUses      
        objDBItems.OpenAllTables()   
        objDBItems.UpdateTodaysDateOnTables()  
    End Sub
End Class

Imports System.Data
Namespace AllAccessDBConnections
    Class AllDBUses
        Private con As New OleDb.OleDbConnection
        Private ds As New DataSet
        Private da As OleDb.OleDbDataAdapter
        Private 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\FinancialBalanceSheetDB.mdb"
            con.Open()                                  'OPEN CONNECTION

            sql = "SELECT * FROM Income"                'OPEN ALL DATA IN INCOME TABLE
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "Income")
            Sql = "SELECT * FROM Expenditure"           'OPEN ALL DATA IN EXPENDITURE TABLE
            da = New OleDb.OleDbDataAdapter(Sql, con)
            da.Fill(ds, "Expenditure")
            sql = "SELECT * FROM Savings"               'OPEN ALL DATA IN SAVINGS TABLE
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "Savings")

            con.Close()                                 'CLOSE CONNECTION
        End Sub
        Public Sub UpdateTodaysDateOnTables()
            Dim rowNumber As Integer = 0
            Dim maxRowsExpenditure As Integer
            Dim maxRowsSavings As Integer
            Dim currentDate As String
            Dim todaysDate As Date = DateTime.Now
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            Dim updateExpenditureDate As Boolean = False

            todaysDate = todaysDate.ToString("dd/MM/yyyy")

            maxRowsExpenditure = ds.Tables("Expenditure").Rows.Count
            maxRowsSavings = ds.Tables("Savings").Rows.Count

            Do Until rowNumber = maxRowsExpenditure
                currentDate = ds.Tables("Expenditure").Rows(rowNumber).Item("Todays Date")
                If currentDate <> todaysDate Then
                    currentDate = todaysDate
                 ds.Tables("Expenditure").Rows(rowNumber).Item("Todays Date") = currentDate
                    updateExpenditureDate = True
                End If
                rowNumber = rowNumber + 1
            Loop
            If updateExpenditureDate = True Then
                da.Update(ds, "Expenditure")
            End If
            rowNumber = 0
        End Sub
    End Class
End Namespace

When the code runs to 'da.Udate(ds, "Expenditure") I get the following error:

Missing the DataColumn 'Asset Name' in the DataTable 'Expenditure' for the SourceColumn 'Asset Name'.

The 'Asset Name' mentioned in the error is a column name but it is in my two other tables, why would it be reading them when I specify the table.

Any help will be much appreciated.

Thanks

[kvprajapati]

>When the code runs to 'da.Udate(ds, "Expenditure")....

When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed.

Take a look at this page - http://msdn.microsoft.com/en-us/library/33y2221y.aspx

[Ap0ca1ypse]

Hello adatapost,

Thanks for the information but that link you provided contains the code for sql connections. I understand i have to use an update command which i do state in my code.

I have written this code with only one table in a mdb and it works flawlessly in another project, the only difference being is i did not create a central class, i just wrote the code on the form class, could that have anything to do with it?

I commented out the two other tables so the 'Expenditure' table is only being used, now i am getting an error 'Syntax error in UPDATE statement'.

I don't understand what its problem is, its a straight copy over from my previous project but cleaned up into a central class.

Any ideas on this new error?

Thanks in advance

[Ap0ca1ypse]

Hello,

I have managed to code it to work with a table, I can have only one table in my access db and it has to be version 2000.

I wish to create 3 tables within the database but if you look to my original post I get the same error, when updating, even though I state which dataset to use it try's looking at all of them and gets confused, has anybody attempted to have more than one table?

My program can read all the databases fine and read the data into labels/textboxes from any table it just cannot update correctly. Am I needing to specify the update further??

[Ap0ca1ypse]

I managed to get all three tables working perfectly, i did not realise that the other tables did not have a primary key set on them.

A couple of notes for people having similar issues

• Access DB = Version 2000
• All tabled in the DB need a Primary Key
• The Primary Key can be anything but i used it as a row identifier set as AutoNumber

Thanks to all that were helping me with my query