Partager via

Why am I losing data when debugging SQL Server Express?

  • Article

This question comes up frequently enough (exhibit A), that I thought I should blog about it to help raise the Google-juice and add some self-help to confused customers.

Answer:
When you add a local SQL Server Express database to a Visual Studio Express project, VS will by default set the "Copy to Output Directory" property for your SQL Server Express database equal to "Always".

Scenario that appears broken to users

  • Run app
  • Make changes to data
  • Save Data changes
  • Close app
  • Run app again
  • All data changes are lost

How to fix it
Luckily, this is a pretty easy fix, simply open your favorite copy of Visual Studio Express, in the solution explorer, select your database, and change the "Copy to Output Directory" to something like "Copy if newer". Screenshot below 

We're considering changing the default for this in the future, what do you all think? Will changing the default confuse things more? Should this be a (wizard) option when creating the database?

Comments

  • Anonymous
    May 27, 2006
    Comment accidentally deleted, copy/pasted below:

    This works fine. But if I have done any modification in the structure of the database file, what will happen?  
    The VS will overwrite the last copy in the bin directory i.e. loosing the change of the data made by the program.  


  • Anonymous
    May 27, 2006
    The comment has been removed

  • Anonymous
    June 21, 2006
    I don't understand why the data access was set up with the "Copy to ouput" scenario.  I understand how it works but for me it is cumbersome.  For example, I create an SQL database.  That database is copied to the output directory.  I start debugging the application and add data to the database.  Say I encounter a problem and I need to see the data in the database to figure it out.  I can't use the data designer preview data tool because it looks at the original database which is different from the one that the app was working on.  Wouldn't it be better if I could see that current data so I could debug my application?

  • Anonymous
    August 13, 2006
    The comment has been removed

  • Anonymous
    March 05, 2007
    I have tried this... but it still doesn't work for me, please help!!!

  • Anonymous
    March 21, 2007
    I spent all day on this. Google is no longer the search engine it once was. But finally an answer. Thanks so much.

  • Anonymous
    June 22, 2007
    Hi! I'm not quite sure if that's ripe for decision, but I read that's possible to temporary or constantly save SQL databases to an externl provider. It might be a good idea to check this site from time to time, they always offer the newest information and developement in  this category: http://www.onlinebackupguide.com

  • Anonymous
    July 27, 2007
    The major problem with the settings is that it is not clear what is happening. The worst part is for the enthusiastic beginners who go through the "Abolute Beginner's Series VB lesson 9" on databinding.  The video shows that the data is actually being saved and the presenter calls up the data to show that it was saved and can be retrieved.  Of course, that will never happen to the person going through the series.  There is a good chance that the person will just give up.  Obviously, that person is not going to be a fan of Microsoft development environments.

  • Anonymous
    October 30, 2008
    I've also tried this to no avail.  I even have an  "AcceptChanges" for the dataset I am using, but it still does not save.  I'm even checking the various databases in the debug and app folders, but I cannot insert.  Here's the code: Dim newInventoryRow As DataSet.InvRow newInventoryRow = DataSet1.Inv.NewInvRow newInventoryRow.ProductID = ProductIDVal newInventoryRow.ProductName = splitout(1) DataSet1.Inv.Rows.Add(newInventoryRow) DataSet1.AcceptChanges() This is right from the MSDN tutorial for inserting data using datasets.  Does anyonoe think it is permissions?  I read somewhere that the aspnet user should have access to the database.  Should this be set up in IIS?  Could it be the SQLAgentCommandExec user?  Do you have to be a user of the group(s):  SQLServer2005MSSQLServerADHelperUser$PCNAME, SQLServer2005MSSQLUser$PCNAME$SQLEXPRESS, and SQLServer2005SQLBrowserUser$PCNAME  ????