Update to access doesnt seem to do anything (moved)

[tonyladuk]

Hello Smart people!

I've done my research and self taught myself kinda how to use a database with VS2005 in VB. I've looked through the forums for an answer but cant find anything similar so sorry if this has been answered before. I'm using access (for ease of learning).

Here's my code to write back to my database:

    Public Sub SetAccountsToDataBase()
            Dim dsDataSet As New FPSdataDataSet.accountsDataTable
            Dim dbaAdapter As New FPSdataDataSetTableAdapters.accountsTableAdapter

            dsDataSet.Clear()

            For Each AccountRow As AccountTypeStructure In colAccounts
                Dim newAccountsRow As FPSdataDataSet.accountsRow = dsDataSet.NewaccountsRow
                newAccountsRow.ID = AccountRow.intID
                newAccountsRow.username = AccountRow.strUserName
                newAccountsRow.password = AccountRow.strPassword
                newAccountsRow.acctype = AccountRow.strType
                dsDataSet.Rows.Add(newAccountsRow)
                newAccountsRow = Nothing
            Next

            dbaAdapter.Update(dsDataSet)
            dsDataSet.AcceptChanges()

            dsDataSet = Nothing
            dbaAdapter = Nothing
        End Sub

I have some code to get from the database and save into an arraylist, the rest of the program is spent playing with the data and this section runs through the arraylist to save back to the database. The only thing is, it doesnt seem to do anything. The database doesnt seem to change atall after this procedure. I have no errors, and i'm sure its something simple that Ive just not got to full grips with yet.

Would really appreciate any help.

Thanks in advance.

 

[chrissie1]

Did you debug and is there something in the dsDataSet?

Christiaan Baes
Belgium

"My new site" - Me

 

[tonyladuk]

By the time it gets to the update line, the row count in dsDataSet is 6. It seems as though it populates ok.

Any specific properties you want to know when it hits the update line?

 

[chrissie1]

I don't play much with wizard created typed datasets. but I think the acceptchanges must be before the update statment.

Christiaan Baes
Belgium

"My new site" - Me

 

[tonyladuk]

Just on a side note, whats the easiest way of deleting all rows from the database to start with?

I'm thinking that dsDataSet.Clear() is redundant because you're declaring it as new to start off with. Should (when it works) the update amend the new data to the end of the database or overwrite the data already there with the new information?

 

[tonyladuk]

I just thought you might want to see the Get from the database too. It just doesnt seem to update even if i call the get (for testing) right after the Set.

It doesnt seem to do anything! I just dont understand!

    Public Sub SetAccountsToDataBase()
        Dim dsDataSet As New FPSdataDataSet.accountsDataTable
        Dim dbaAdapter As New FPSdataDataSetTableAdapters.accountsTableAdapter

        For Each AccountRow As AccountTypeStructure In colAccounts
            Dim newAccountsRow As FPSdataDataSet.accountsRow = dsDataSet.NewaccountsRow
            newAccountsRow.ID = AccountRow.intID
            newAccountsRow.username = AccountRow.strUserName
            newAccountsRow.password = AccountRow.strPassword
            newAccountsRow.acctype = AccountRow.strType
            dsDataSet.Rows.Add(newAccountsRow)
            newAccountsRow = Nothing
        Next

        dsDataSet.AcceptChanges()
        dbaAdapter.Update(dsDataSet)
        dsDataSet.AcceptChanges()

        dsDataSet = Nothing
        dbaAdapter = Nothing

        colAccounts.Clear()
        GetAccountsFromDatabase()
    End Sub


    'saves the account information from the database into colAccounts
    Private Sub GetAccountsFromDatabase()
        Dim dsDataSet As New FPSdataDataSet.accountsDataTable
        Dim dbaTableAdapter As New FPSdataDataSetTableAdapters.accountsTableAdapter
        dbaTableAdapter.Fill(dsDataSet)
        Dim intRowCounter As Integer = 0
        For Each drThisRow As FPSdataDataSet.accountsRow In dsDataSet.Rows
            Dim AccountRow As New AccountTypeStructure
            If drThisRow("ID") IsNot DBNull.Value Then AccountRow.intID = drThisRow("ID")
            If drThisRow("username") IsNot DBNull.Value Then AccountRow.strUserName = drThisRow("username")
            If drThisRow("password") IsNot DBNull.Value Then AccountRow.strPassword = drThisRow("password")
            If drThisRow("acctype") IsNot DBNull.Value Then AccountRow.strType = drThisRow("acctype")
            AccountRow.intRowID = intRowCounter
            intRowCounter += 1
            colAccounts.Add(AccountRow)
            AccountRow = Nothing
        Next
        dsDataSet = Nothing
        dbaTableAdapter = Nothing
    End Sub

 

[tonyladuk]

OK, I've been playing with this all day today and yesterday. There's something i'm missing. I've just downloaded and produced the microsoft example called NorthWind.

For those of you that dont know, its a simple customer table and using the wizard you produce a form that runs through the database and alows you to save changes in the data. I've finished it and it seems to work fine, the database during run time updates great.

Here's the issue: When you close the application and open the access database to see the changes, or when you run it again. No changes have been made. This has got to be something crucial i'm missing. Please someone heed my plea!