FAQ: "My database isnt saving changes" or "Changes are gone when I restart the app"

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,081
Programming Experience
10+
FAQ: "My database isnt saving changes" or "Changes are gone when I restart the app"

A question comes up here very often, so I'm going to write an in-depth explanation of it.

Here's what is happening to you:
1) You are using a file-based database, either Jet MDB (access) or SQL Server MDF file
2) You run your app, add/edit/delete some data, press save and then..
2a) either you go and open the database in Access and nothing has changed
2b) or when you re-run the app, the data is back exactly how it was


Why does this happen? First we will talk about how programs work, etc.

You write some code
You build that code
The compiler makes an exe
The exe is run

If you run in debug mode, extra files are made (PDB) and these help the Visual Studio attach to your running program, like a leech, and show you the code as it is running.
Your app might use some things that dont get embedded in the exe, dlls, pictures, icons, sound files, config files etc. Naturally, for these to work, they must be copied out to the same folder that the EXE goes in, when it is built.

Databases are NO EXCEPTION to this rule. They too have to be copied out.

To see this in action, go to your Solution Explorer and click the Show All Files button. Expand the ghosted "bin" folder, then "Debug" or "Release"
These are actual files, on disk. You can see your EXE and you can see all the ancilliary files that go with it.

See the screenshot below for more info



-

Now, when you added this database, you went to Data Sources, and chose to add a datasource (like most good tutorials tell you). You selected Access or SQL Server File from the list and you picked your file.

An information dialog appeared that, likely (like me) you never read, or if you did, didnt understand (I didnt really appreciate what it was wibbling on about either)

See the screenshot below.

What this dialog was talking about, was that you'd picked a database outside the folder structure of your project, did you want the database copying in. Not knowing what the implications of this were (or because you were told to do so by the tutorial), you probably clicked Yes.

Take a look at the SolutionExplorer screenshot now.
You clicked Yes, which meant the IDE copied your database into the project folder, and it shows up as a nice yellow cylinder. I have written 'Development "Clean" version' next to mine.


You can probably see where this is going.


When you build your project (or press play), just like the info dialog said, and in accordance with the "Copy" attribute in the properties (i've drawn a circle round it), the Clean version of the database is copied to the bin\Debug or bin\Release folder. i.e. a copy of the "Clean" is made and i've called this "Dirty" because it gets dirty while we test our app.

So you load your app, it edits the COPY of the database, and then you close the app.

Now you do one of two things related to 2a or 2b earlier:
1) you either open the "Clean" database in Access and wonder why nothing's changed (of course it hasnt - we didnt edit this copy!)
2) you re-run your app, the old "Dirty" version that we did edit, is deleted and a brand new, shiny "Clean" version is copied in to become "Dirty" - you think your changes have been lost (of course they have - the database you changed was replaced with the clean version!)


If youre still having difficulty grasping what's going on here, I'll present an analogy:

You have a file at work
You copy it onto a USB stick and take it home
You edit it at home
You forget, and leave the USB stick at home
You come in to work, and open the original file
You say "hey; it hasnt changed! I edited it all last night, and my changes havent been saved!"

or

You have a file at work
You copy it onto a USB stick and take it home
You edit it at home
You come in to work, bringing the stick with you
You copy the original from your work computer, over the top of the one you edited
You open up the version on the USB stick and say "hey; it hasnt changed! I edited it all last night, and my changes havent been saved!"


It might sound a silly thing to do, but that is exactly what is going on here - Visual Studio is doing the copying , but it never made it obvious to you. I would blame Microsoft, but we are developers; we're supposed to be a bit more computer savvy than the average users!

-


Now, if you'd pressed F1 "for information about controlling this behaviour" like the dialog said, you'd have probably got some help document telling you what I'm going to say next..


The simplest way to solve this "problem" and get things working like you expect, is to change the Copy Always, to Copy If Newer. (See screenshot)

Let's think about this for a second.
Copy if Newer means that the "Clean" version will only be copied out if it is newer than the "Dirty" version

* Initially, the dirty version doesnt exist, so the clean version must be newer. It gets copied out
* Your app runs and changes the database. The Dirty version is now newer, and will not be replaced next time you start the app
* You edit the structure of the Clean database to add a column. Now the clean version is newer, so it will be copied out again.

This means "Copy If Newer" is a better thing than "Copy Always" (if you want the changes to be retained. If you arent bothered, then leave it on Copy Always)
So, if you take nothing else away from this article, at least set the copy mode to "Copy if Newer". It will "solve" the "problem"..



And a last note, you will surely now realise why you cannot open the Clean version in Access and see the changes the app made to the dirty version. If you want to double check that your app really did change the database, do this:
Open any code file in your app, it doesnt matter.
On the tab at the top of the code window, do a right click (the tab)
Choose "Open Containing Folder" and explorer will appear, showing your file.
Open the BIN folder
Open either DEBUG or RELEASE
Open the database you see THERE, if you want to check something has changed...
 

Attachments

  • Dialog.png
    Dialog.png
    7.2 KB · Views: 173
  • SolEx.png
    SolEx.png
    46.2 KB · Views: 186
Last edited:
Dialog screenshot (modified to make it narrower and taller, same text)
 

Attachments

  • Dialog.png
    Dialog.png
    7 KB · Views: 331
Last edited:
Another question

First thanks for the article it really help me understand quite a few things, now my question is when you edit/add/delete since that "dirty" is the newer it will keep the data, and that's true since i've already tested it, the deal is when i refresh the data base it will happen what you stated that now the database since it was refreshed is newer now and will be copied on top of the dirty.


basically you are saying that we should've click no in the dialog box when we first added the database??

what's going to happen when you publish the app?? or when you want to add more columns after the app is published??

thanks!
 
basically you are saying that we should've click no in the dialog box when we first added the database??

Well, that all depends. If you click NO, then the IDE will leave the database alone, where it is (say it's in C:\temp) and the database will:

NOT appear in the project
NOT be copied out
NOT be included in an installer msi
MUST be used from C:\temp (the connection string is created with an absolute path)

That database might also belong to another app, be in use by that app, and suddenly your app will go making a mess in it. This might not actually be desirable!

Overall, the idea of software is that you have a nice clean version. You build, create a copy of all resources, mess them up etc, but when you come to release a final copy to your customer, you want them to start with a nice clean blank database, right?

So, say yes, set it to Copy if Newer, and everyone is happy! :D

what's going to happen when you publish the app??
In terms of what? Your test data? Well you should always perform a CLEAN ALL which deletes all temp files, old versions etc. However, because you typically dont (or shouldnt) run your published version (publish is different to Debug or Release) the database within it should never accumulate any junk!


or when you want to add more columns after the app is published??
when your app is out in the wild, you either:
Provide a new database with the new schema
Copy their data into it

or

Use DLL commands such as ALTER TABLE t ADD newCol VARCHAR2(100)
Back-fill them with data (maybe)
 
Back
Top