Why Saving Changes to a Database Fails

Why saving changes to a database fails?

It is a quite common problem. You use the |DataDirectory| substitution string. This means that, while debugging your app in the Visual Studio environment, the database used by your application is located in the subfolder BIN\DEBUG folder (or x86 variant) of your project. And this works well as you don't have any kind of error connecting to the database and making update operations.

But then, you exit the debug session and you look at your database through the Visual Studio Server Explorer (or any other suitable tool). This window has a different connection string (probably pointing to the copy of your database in the project folder). You search your tables and you don't see the changes.

Then the problem get worse. You restart VS to go hunting for the bug in your app, but you have your database file listed between your project files and the property Copy to Output directory is set to Copy Always. At this point Visual Studio obliges and copies the original database file from the project folder to the output folder (BIN\DEBUG) and thus your previous changes are lost.

Now, your application inserts/updates again the target table, you again can't find any error in your code and restart the loop again until you decide to post or search on StackOverflow.

You could stop this problem by clicking on the database file listed in your Solution Explorer and changing the property Copy To Output Directory to Copy If Newer or Never Copy. Also you could update your connectionstring in the Server Explorer to look at the working copy of your database or create a second connection. The first one still points to the database in the project folder while the second one points to the database in the BIN\DEBUG folder. In this way you could keep the original database ready for deployment purposes and schema changes, while, with the second connection you could look at the effective results of your coding efforts.

EDIT Special warning for MS-Access database users. The simple act of looking at your table changes the modified date of your database ALSO if you don't write or change anything. So the flag Copy if Newer kicks in and the database file is copied to the output directory. With Access better use Copy Never.

Sql Server 'Saving changes is not permitted' error ► Prevent saving changes that require table re-creation

From Save (Not Permitted) Dialog Box on MSDN :

The Save (Not Permitted) dialog box warns you that saving changes is
not permitted because the changes you have made require the listed
tables to be dropped and re-created.
The following actions might require a table to be re-created:
Adding a new column to the middle of the table
Dropping a column
Changing column nullability
Changing the order of the columns
Changing the data type of a column <<<<
To change this option, on the Tools menu, click Options, expand
Designers, and then click Table and Database Designers.
Select or clear the Prevent saving changes that require the table to be
re-created check box.

See Also
Colt Kwong Blog Entry:

Saving changes is not permitted in SQL 2008 Management Studio

SQL server: can't save/change table design

The following actions might require a table to be re-created:

Adding a new column to the middle of the table
Dropping a column
Changing column nullability
Changing the order of the columns
Changing the data type of a column

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

refer

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created

I would strongly suggest that you use T-SQL to make changes, or at the very least, preview the scripts that the Designers generate before committing them. However, if you want to do this in the designer, you can turn off that lock by going to Tools...Options...Designers..Table and Database Designers.. and unclick the "prevent saving changes that require table re-creation".

That lock is on by default for a reason; it keeps you from committing some change that is obfuscated by the designer.

EDIT: As noted in the comment below, you can't preview the changes unless you disable the lock. My point is that if you want to use the table-designer to work on a table with this feature disabled, you should be sure to always preview the changes before committing them. In short, options are:

BEST PROCESS: Use T-SQL
NOT GREAT: Disable the lock, use Table
Designer, and ALWAYS preview changes
CRAZY TALK: Click some buttons.

Unable to save changes to database via Entity Framework 6, have tried a few different totorials but none work

The solution just came to me, I realized on my view there was no input holding the ID of the section being edited so when it tries to check the DB it has no data to compare to the primary key on. I simply added: @Html.HiddenFor(m => m.Section.ID) and now it all works using:

using (db)
{
    db.Entry(section).State = EntityState.Modified;
    db.SaveChanges();
}

Why Saving Changes to a Database Fails