Data will not update, delete, or insert with attached mdf

[RustyAfro]

Hello all,

When accessing records through a basic datagridview that has a bindingnavigator in VS 2005, changes made do not save to the database even though I click the save button and no errors occur.

The database is attached from the application folder and uses the below connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\OMS.mdf;Integrated Security=True;User Instance=FALSE

But if I physically attach the MDF to the database through SQL Server Management Studio and then connect to it in the application, the databases saves and reflects any Updates, Deletes, and Inserts that were saved. I do delete the database before trying the attach method again to avoid the database name already existing on the server. Here is the connection string:

Data Source=.\SQLEXPRESS;Initial Catalog=OMS;Integrated Security=True

I'm trying to figure out how to make it work correctly when it is attached at runtime since I need the database distributed with the app.

One clue which may not be relevant: When I click on the database file connection in Server Explorer, the "Disconnected" X turns into a "plug" connection. But right when I click "Start Debugging", I see it turn back into an X.

Again, the app runs fine, no errors that I can see. Just changes are not saving with the attach method in the connection string.

 

[RustyAfro]

Nevermind, apparently this is a common issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=427451&SiteID=1

"
Sara Parra

Problem:

You have an ADO.NET application that updates, inserts, or deletes data from your database. No errors occur and the data changes are visible in the application, but do not persist between application runs, or if you view the database file outside of the application.

Solution:

There are two common reasons why this happens:

(1) You are calling AcceptChanges on your DataRow/DataTable/DataSet before you call Update on your DataAdapter or TableAdapter. AcceptChanges will commit all changes made since the data was loaded or since AcceptChanges was last called. This means that if you had a row that was marked as Modified, it will now be Unchanged. When you call Update, it will not recognize that the row has changes that need to be submitted to the database. The same applies for inserted and deleted rows as well. For more information, see the AcceptChanges documentation:

http://msdn2.microsoft.com/en-us/library/system.data.dataset.acceptchanges.aspx.

This applies to all database backends and application types (ASP.NET, Windows Forms, etc).

(2) The other common problem is that in Visual Studio, there is an option to copy your database file to the output folder of your project. Usually this is done with an Access MDB file, or a SQL Server MDF file, when you add the database file to your project.

This copy options applies each time you run the application. Therefore, although the database may in fact be updated, the updates are going to the copy instead of the original file that you expect. The key to this is the “Copy to Output Directory” property on the database file in your project. This is typically set to “Copy always” by default. What this means is that every time you build, run, or debug your application, Visual Studio copies the project file over to the output directory, and then that’s what the app connects to.

In these cases, you will probably also be using a connection string that includes the "|DataDirectory|" option. For non-Web apps, this is the project output folder where your executable is created (usually bin\debug). In order to ensure that this connection string will work, Visual Studio chooses the "Copy Always" setting by default. You can keep the connection string as-is, but change the copy option to "Do not copy", and in that case, you should see your changes persist between application runs, or if you check the database outside of the app. Make sure to check the copy in the output folder. Alternatively, you can change the connection string *and* the copy property, and point to the file you actually want to connect to.

This applies to Access MDB files, as well as SQL Server MDF files, as long as you are connecting to the MDFs file at runtime by specifying the location in the connection string with the AttachDBFilename keyword. Access databases are always file-based, so there is no need for a special keyword.

There is a utility you can use to see which database file is actually being used by the application: FileMon by

http://www.sysinternals.com.

Start tracing before you run your application, then watch to see which file is actually accessed. Keep in mind that the trace may also include any file copies, so make sure you search for all occurrences of your filename, to be sure that all of them are using the file you expect.