[2005] Saving to a database doesnt actually change anything

I'm having problems saving back to an Access database. There's something i'm missing. I've just downloaded and produced the microsoft example called NorthWind in the hope that this would bring to light my problem.

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! :confused: This has got to be something crucial i'm missing. I really need some help here.
Thanks in advance.

Could you post your code that you are using to save to the database?

This is the microsoft example:

VB Code:

Private Sub CustomersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingNavigatorSaveItem.Click
        Try
            Validate()
            CustomersBindingSource.EndEdit()
            CustomersTableAdapter.Update(Me.NwindDataSet.Customers)
            MsgBox("Update successful")
 
        Catch ex As Exception
            MsgBox("Update failed")
        End Try
 
    End Sub
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'NwindDataSet.Customers' table. You can move, or remove it, as needed.
        Me.CustomersTableAdapter.Fill(Me.NwindDataSet.Customers)
 
    End Sub

My code is below:

VB Code:

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
    End Sub

And even though it seems as though the database is being affected by the code during run time, ive opened the access database during the changes and it doesnt complain about it being open and the changes arent made.

Since you're using the wizard to generate your form and the necessary code to interact with the database I'm assuming the dat on the form is not correctly flushed to the Access database. Have you tried adding several rows and then closing your application? If you're using a datagrid to add and display the data you're probably not updating invoking the necessary method(s) in the dataadapter to update the database. Remember ADO.NET uses disconnected datasets and you have to explicitly tell it to persist your data to the database.

VB Code:

dsDataSet.AcceptChanges()
        dbaAdapter.Update(dsDataSet)
        dsDataSet.AcceptChanges()
 
        dsDataSet = Nothing
        dbaAdapter = Nothing

OK you posted the code while I was typing the other post :D

You have to invoke the GetChanges method and then pass on the resulting dataset/datatable to the Update method of dbaAdapter.

Thanks for the help so far Mr No.
Firstly, which AcceptChanges can i get rid of? The one before update or the one after? I'm sure i dont need both of them.

Secondly, does the getchanges come before the update AND before the acceptchanges?

Sorry, i'm new to all this database business :S

Should it be something like this?:

Code:

---


        Dim dsChangedDataset As FPSdataDataSet.accountsDataTable = dsDataSet.GetChanges

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

---

Arrrg, still having problems. Now i'm getting an error:

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147467259
Message="The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

So to combat this, on a side note, how do i clear the current database so that i can populate it anew each time I click the save button? (many user changes will probably be done before the save - thats why im doing it this way)

Anyway, its still not updating the DB, any more tips?

Quote:

---

Secondly, does the getchanges come before the update AND before the acceptchanges?

---

Here's how the sequence of code should look like.

VB Code:

Dim dsChangedDataset As FPSdataDataSet.accountsDataTable = dsDataSet.GetChanges
        dbaAdapter.Update(dsChangedDataset)
        dsDataSet.AcceptChanges()

Here's what the help says on AcceptChanges:

Quote:

---

Commits all the changes made to this DataSet since it was loaded or since the last time AcceptChanges was called.

---

and here's what help says on GetChanges

Quote:

---

Gets a copy of the DataSet containing all changes made to it since it was last loaded, or since AcceptChanges was called.

---

Now your database error is a good sign :cool: , it means you changes are been applied to the database. The error here indicates that you're using the same value for the key column for two different rows. If the customerid is the primary key make sure you use unique values for this column.

Thanks for the help so far but its still not working! :cry:

Surely this should work:

VB Code:

Public Sub SetAccountsToDataBase()
        Dim dbaAdapter As New FPSdataDataSetTableAdapters.accountsTableAdapter
        Dim dsDataSet As New FPSdataDataSet.accountsDataTable
 
        dbaAdapter.Fill(dsDataSet)
 
        Dim newAccountsRow2 As FPSdataDataSet.accountsRow = dsDataSet.NewaccountsRow
        newAccountsRow2.ID = 22
        newAccountsRow2.username = "jonesn"
        newAccountsRow2.password = "password"
        newAccountsRow2.acctype = "Master"
        dsDataSet.Rows.Add(newAccountsRow2)
 
        Dim dsChangedDataset As FPSdataDataSet.accountsDataTable = dsDataSet.GetChanges
        dbaAdapter.Update(dsChangedDataset)
        dsDataSet.AcceptChanges()
 
        dsDataSet = Nothing
        dbaAdapter = Nothing
    End Sub

No errors, just does absolutley nothing :eek2:

Here's the issue: When i run my application with the database open in Access at the same time, it kicks up an error, something like problem generating manifest cause its already used by another application - which makes sense to me. But when I have it open when i'm trying to save to the database, it doesnt complain atall. So where is it making these changes to?

As I always say, Update is a function that returns an Integer that represents the number of records affected. What does Update return in your case? If it's not zero then it's making the changes. How exactly are you determining that no changes are being made? Are you testing the actual MDB file that your app is connection to? Quite possibly not. If you've added an MDB file to your project then it's not that file that gets changed. Each time you run debug your app it is built and a new copy of the MDB file will be created in your output folder, thus overwriting any changes you have previously made. You can change this behaviour in the Properties window.

HORRAY!!!

I knew something dodgy was going on that i wasnt picking up on. Thanks J, you were right, I hunted around the file structure and have found the output version of the database which actually HAS updated. Why have i not heard anything of this before? :eek:

OK this leads on to all sorts of questions. How do i use this database? How can I save it into a user specified area? Is the database I attached used for all my default values and just so i could set up the structure of the database? How do i delete all rows from a database?

Thanks again! Slowly getting there.

And thanks for your help so far Mr No, seems as though your assistance has actaully change the database, just not the one i thought it was.

When you add an MDB file to your project it is a source file, just like all your code files etc. You wouldn't want to make changes to that original while testing because you want to be able to distribute a clean database with your app. You can set the properties of the MDB file such that each time you build your project the file will be copied to the output folder every time, only when the source is newer or never. The default behaviour is to copy the file every time you build the project, which means every time you run it in the debugger also. That means that every time you debug your project you lose any data changes you made last time you ran it. The most sensible setting, and the one Microsoft should have chosen as the default in my opinion, is to copy if the source is newer. That means you will keep all your data changes made while debugging until you make a data or schema change to the source file itself, at which point the old file in the output folder will be overwritten. If ever you specifically want to overwrite the file in the output folder, like when you're ready to distribute, you simply change the properties to copy always to ensure a fresh copy is created and build your project.

Right, thanks for that, this leads to 2 questions:

1) Is it possible to save multiple copies of the database (like saving multiple word docs for example)? My application is used for scenario planning and would be nice if i can have the user make multiple copies of the database. Similarly, they would need to open or load different copies of the database into the application.

2) I still dont know how to clear the database tables. There's a function that can delete a single row but you have to specify elements of the row. All i want to do is wipe the entire table.

Quick question while we're on the topic. Most of the time that I use data from a database, I am displaying it in a DataGridView. Generally, when I am doing this, I am intending the information to be read-only - I don't want the end-user to be able to edit at will.

Other times, when I am actually updating the database, it is generally with information from a form that has been filled out, not when the user is viewing the info in a DGV.

It seems to me that a lot of people are using DataSets to push changes back upstream to the database, but that's not how I do it. Should I? Is that really the best way? Is it only the best way when someone can edit the information, a la DataSheet view? I just want to make sure I'm not making my life more complicated than it already is.

Quote:

---

Originally Posted by tonylad

Right, thanks for that, this leads to 2 questions:

1) Is it possible to save multiple copies of the database (like saving multiple word docs for example)? My application is used for scenario planning and would be nice if i can have the user make multiple copies of the database. Similarly, they would need to open or load different copies of the database into the application.

2) I still dont know how to clear the database tables. There's a function that can delete a single row but you have to specify elements of the row. All i want to do is wipe the entire table.

---

1. You can have as many databases as you want. You could prompt the user when the app starts to select a database and then build its path into a connection string to be used for the rest of the application.

2. If you want to delete every record in a table you simply execute a DELETE statement with no WHERE clause. That means creating an OleDbCommand with the appropriate CommandText and calling its ExecuteNonQuery method. Note that if the table has an autonumber column its seed value will not be reset, so if you delete 100 records the next record will have an ID of 101.

Quote:

---

Originally Posted by scgtman

Quick question while we're on the topic. Most of the time that I use data from a database, I am displaying it in a DataGridView. Generally, when I am doing this, I am intending the information to be read-only - I don't want the end-user to be able to edit at will.

Other times, when I am actually updating the database, it is generally with information from a form that has been filled out, not when the user is viewing the info in a DGV.

It seems to me that a lot of people are using DataSets to push changes back upstream to the database, but that's not how I do it. Should I? Is that really the best way? Is it only the best way when someone can edit the information, a la DataSheet view? I just want to make sure I'm not making my life more complicated than it already is.

---

Use whichever method is appropriate to the situation. Sometimes a grid is the best way to display data, sometimes it's not. With the DataGridView being more flexible than the old DataGrid it is a better option in more situations, but certainly there are plenty of situations where there are better ways to display, edit or gather your data than in a grid control.

Quote:

---

Originally Posted by jmcilhinney

1. You can have as many databases as you want. You could prompt the user when the app starts to select a database and then build its path into a connection string to be used for the rest of the application.

---

Thanks for all the information so far, I've just come back to this after doing some other stuff for the last week.

I just built and packaged my software up to see how it would work on another machine and the output file is saved in some bizarre location. So how do I:

1) Save the file to a user defined area (including allowing multiple databases)?

2) Open the last used file?

3) Build the file to open name into the connection string?

4) Get the application to save into the program files (rather than specific user applications under mydocuments)?

Quote:

---

Originally Posted by tonylad

Thanks for all the information so far, I've just come back to this after doing some other stuff for the last week.

I just built and packaged my software up to see how it would work on another machine and the output file is saved in some bizarre location. So how do I:

1) Save the file to a user defined area (including allowing multiple databases)?

2) Open the last used file?

3) Build the file to open name into the connection string?

4) Get the application to save into the program files (rather than specific user applications under mydocuments)?

---

Are you publishing this project, thus creating a ClickOnce installer, or are you creating a Setup project? If you're using VB Express then only ClickOnce publishing is available in the IDE. ClickOnce has advantages and limitations. You can read about them on MSDN.

One thing that ClickOnce does is not install apps into the Program Files but into the ClickOnce cache instead. This is one of the things that allows ClickOnce to manage these apps. A lot of people jump up and down about this like it's a crime when there's usually no specific reason that their app needs to be installed in Program Files anyway, other than convention. If there really is a genuine need for you to install to Program Files then you cannot use ClickOnce publishing and, if you're using VB Express, you'll have to use a third-party tool to create your installer. If your app requires you to do anything else that ClickOnce can't the same thing applies.

Im using visual studio 2005 professional. When i build it and package it, it gives me a number of options and i chose the setup from CD option. It creates a setup exe that is used to install it.

It doesnt have to install into the program files, its just that at the moment, its not saving so that all users can use the program. Its saving in the installers profile, somewhere under mydocs. There just doesnt seem to be any control over where it saves to and where the output database is saved.

You're using the Publish feature to create a ClickOnce installer by the sounds of it. As I said, you should go to MSDN and read about ClickOnce deployment to see if it suits your needs. If it doesn't then you need to add a Setup project to your solution and create an MSI installer.

I've got a similar problem, but i've got an example project for you guys to debug ;)

Download Here

Just doesnt save changes when the program is re-run, the database is empty.

Add a few items, seems fine, can scroll through all of them, you close the program. Start it up again, and its empty :-(