cjard
Well-known member
- Joined
- Apr 25, 2006
- Messages
- 7,081
- Programming Experience
- 10+
keywords:
Access SQL Server mdb mdf TableAdapter update changes lost not saved commit committed
I recently had a friend ask me about a problem that was bugging him for a long time.
In MS Access or SQL Server, he connected his tables, downloaded some data into a data table, edited it, called Update on the table adapter and was told that the rows had been updated.
What he couldnt understand, was if he looked in the database, or restarted the app, the changes hadnt been saved. It was exactly as if a transaction was operating and had been rolled back, but his entire app was transactionless.
I experienced the same issue with MS Access and SQLS but not with Oracle. After some extensive investiation I found the root cause of the problem, and oh how I laughed..
When first adding an MDB or MDF (sqlserver) file as a data source, the IDE asks if you want to copy the file into the project directory, and notes that it will be copied along with the exe whenever a project is built. I would guess most people would say YES to this question without considering the implications of the footnote.
What you can guess, but i didnt immediately click with this, is that when you debug the app, it is rebuilt and the database file is copied out of the project directory, into the bin/Debug directory. If your code saves changes to the database, and then you reload your app, the original database is copied over the changed one.
This had us going for a while, and the resolution is simple:
EIther say NO to the first question OR
Say YES, but then click on the MDB file in the solution explorer and choose "Copy if Newer" instead of "Copy Always"
This way the database will be copied in the following situations:
It doesnt exist, but needs to (first debug, or first build)
You updated its structure or default data
It wont be copied in the following situations:
Your code updated the debug version of the db
-
I cant believe Copy if Newer isnt the default option - it would stop most users falling into this gotcha, and i cant think of a situation where Copy Always is useful, even if you make a habit of editing structure your debug build database
Access SQL Server mdb mdf TableAdapter update changes lost not saved commit committed
I recently had a friend ask me about a problem that was bugging him for a long time.
In MS Access or SQL Server, he connected his tables, downloaded some data into a data table, edited it, called Update on the table adapter and was told that the rows had been updated.
What he couldnt understand, was if he looked in the database, or restarted the app, the changes hadnt been saved. It was exactly as if a transaction was operating and had been rolled back, but his entire app was transactionless.
I experienced the same issue with MS Access and SQLS but not with Oracle. After some extensive investiation I found the root cause of the problem, and oh how I laughed..
When first adding an MDB or MDF (sqlserver) file as a data source, the IDE asks if you want to copy the file into the project directory, and notes that it will be copied along with the exe whenever a project is built. I would guess most people would say YES to this question without considering the implications of the footnote.
What you can guess, but i didnt immediately click with this, is that when you debug the app, it is rebuilt and the database file is copied out of the project directory, into the bin/Debug directory. If your code saves changes to the database, and then you reload your app, the original database is copied over the changed one.
This had us going for a while, and the resolution is simple:
EIther say NO to the first question OR
Say YES, but then click on the MDB file in the solution explorer and choose "Copy if Newer" instead of "Copy Always"
This way the database will be copied in the following situations:
It doesnt exist, but needs to (first debug, or first build)
You updated its structure or default data
It wont be copied in the following situations:
Your code updated the debug version of the db
-
I cant believe Copy if Newer isnt the default option - it would stop most users falling into this gotcha, and i cant think of a situation where Copy Always is useful, even if you make a habit of editing structure your debug build database
Last edited:
