Resolved Datagridview not saving underlying source

[LabProARW]

I have FrmTempRequired and on its Load open a connection to my Access backend using OleDbConnection. The dataAdapter fills the dataGridView1 with the dataSet ds. So far so good. The form opens and the datagridview displays several records. I can edit a value in one of those records and move to another record. The btnCloseTempRequired is where I expect to put the code to update the underlying source of dataGridView1 to reflect the record change. All attempts to save to the underlying data source table fails. Is this the wrong place to update the underlying data source? Is there a good link to examples of doing this using OleDb? All of the many examples I have looked at and tried variations of all have some different elements which are throwing me off.

Thanks.

 

[Skydiver]

Since you mentioned you are using OLEDB, that would hint that you are using an Access database (or hopefully not Excel) as your backing database. Are you sure that as part of your build process you are not dropping in the original blank template database on top of the database that you had previously written to the last time you ran your code?

A good test would be to run your program, make the data changes, save, and then exit your program. Then go to the directory that has your database. Is the file stamp of the database relatively recent? Open it directly using Access (or whatever database you are using) and inspect the data. If the data is there, that means it's likely your build process that is overwriting the Access database file.

If you are sure that you are not overwriting your file, show us the contents of your close button event handler. Perhaps you started a transaction and forgot to commit it?

Also another thing to check is to make sure that your startup code isn't blowing away the old database contents. I once wasted half an afternoon trying to figure out why my data was getting lost, and eventually figured out that on my program startup I had code that was dropping all tables and recreating them from scratch.

 

[Skydiver]

Also worth reading:

Updating Data Sources with DataAdapters - ADO.NET

Learn how the Update method of DataAdapter resolves changes from a DataSet back to the data source in ADO.NET applications.

learn.microsoft.com

 

[jmcilhinney]

All attempts to save to the underlying data source table fails.

Do they really? You haven't actually showed us any of these attempts or told us what actually happens so we have no idea whether you're accurately reflecting what's actually happening. Here's what should be happening:

1. Call Fill on a data adapter to populate a DataTable.
2. Bind that DataTable to a BindingSource and that to the grid.
3. Edit the data via the UI as required.
4. Call Validate on the form and EndEdit on the BindingSource to ensure all changes have been committed to the DataTable.
5. Call Update on the data adapter to save the changes from the DataTable to the database.

That's it, that's all. On step 5, there are three possible outcomes:

1. The call succeeds and returns zero. This means that there were no changes to save.
2. The call succeeds and returns a non-zero value. This means that there were changes to save and they were saved.
3. The call fails and throws an exception. This means that something went wrong and the exception will tell you what it was.

That is the first thing to check. If there's no exception and the return value is not zero, everything is working exactly as it should be and you are trying to solve the wrong problem.

As suggested above, a very common problem with beginners using Access databases or any other file-based database is that, by default, the working database gets overwritten every time they make a code change and run the project again. If you save some data changes, exit the app and then run the project again immediately, are those changes still there? The project is not built again if there are no code changes so the working database is not overwritten. If you make a code change - even just add a space and then delete it - and run the project, are the changes gone? In that case, it is definitely the case that your working database is being overwritten.

Here's how it works. When you add a data file to your project, it becomes a source file, like your code files. That data file should be kept clean, so it is ready to be deployed with the final app. It should contain the schema and any default data but you should not be polluting it with test data. For that reason, when you build, VS copies that data file into the program folder along with your EXE and it is that copy that you access at run time. When you create a Release build to deploy, instead of a Debug build to test, you will get a nice clean copy of the database to deploy.

The problem is that, by default, the source data file is copied over the working data file every time you build your project. This is not what most people want. Select the data file in the Solution Explorer and open the Properties window. The value of the Copy to Output Directory property will be Copy Always by default. You should change that to Copy if Newer, which means that the working database will only be overwritten if the source file is newer than the working file. That will generally only happen when you make changes to the source file - when you change the schema or the default data - in which case you would not want to keep working with the old copy anyway. Now, you can test as much as you want and your changes will be kept between runs. Just be aware that, if you open the source file outside of VS, this might update its Last Modified Date and trigger a copy on the next build, even if you don't change the contents of the file. If you ever want to refresh your working database without making a code change, simply delete the working file from the bin folder and force a rebuild of the project.

 

[jmcilhinney]

Here is an article from Microsoft that talks about the handling of local data files. You should ignore their warning about using Copy if Newer but do be ware of the implications.

 

[cjard]

Is there a good link to examples of doing this using OleDb?

By the way, we've moved away from it, and what you're doing with it, pretty much completely these days. If there is some external reason why you have to use an Access DB then that's how your hands are tied, but if you're doing this out of choice and could pick any database system in the world, Access DB via ole and a DataAdapter a would be the last thing I'd do for learning or a new development

 

[LabProARW]

By the way, we've moved away from it, and what you're doing with it, pretty much completely these days. If there is some external reason why you have to use an Access DB then that's how your hands are tied, but if you're doing this out of choice and could pick any database system in the world, Access DB via ole and a DataAdapter a would be the last thing I'd do for learning or a new development

Thank you cjard for your comment. My comment has a legacy database which is an Access DB on their server. This current project is a redesign of the existing Access front end. My hands are tied.

 

[LabProARW]

Do they really? You haven't actually showed us any of these attempts or told us what actually happens so we have no idea whether you're accurately reflecting what's actually happening. Here's what should be happening:

1. Call Fill on a data adapter to populate a DataTable.
2. Bind that DataTable to a BindingSource and that to the grid.
3. Edit the data via the UI as required.
4. Call Validate on the form and EndEdit on the BindingSource to ensure all changes have been committed to the DataTable.
5. Call Update on the data adapter to save the changes from the DataTable to the database.

That's it, that's all. On step 5, there are three possible outcomes:

1. The call succeeds and returns zero. This means that there were no changes to save.
2. The call succeeds and returns a non-zero value. This means that there were changes to save and they were saved.
3. The call fails and throws an exception. This means that something went wrong and the exception will tell you what it was.

That is the first thing to check. If there's no exception and the return value is not zero, everything is working exactly as it should be and you are trying to solve the wrong problem.

As suggested above, a very common problem with beginners using Access databases or any other file-based database is that, by default, the working database gets overwritten every time they make a code change and run the project again. If you save some data changes, exit the app and then run the project again immediately, are those changes still there? The project is not built again if there are no code changes so the working database is not overwritten. If you make a code change - even just add a space and then delete it - and run the project, are the changes gone? In that case, it is definitely the case that your working database is being overwritten.

Here's how it works. When you add a data file to your project, it becomes a source file, like your code files. That data file should be kept clean, so it is ready to be deployed with the final app. It should contain the schema and any default data but you should not be polluting it with test data. For that reason, when you build, VS copies that data file into the program folder along with your EXE and it is that copy that you access at run time. When you create a Release build to deploy, instead of a Debug build to test, you will get a nice clean copy of the database to deploy.

The problem is that, by default, the source data file is copied over the working data file every time you build your project. This is not what most people want. Select the data file in the Solution Explorer and open the Properties window. The value of the Copy to Output Directory property will be Copy Always by default. You should change that to Copy if Newer, which means that the working database will only be overwritten if the source file is newer than the working file. That will generally only happen when you make changes to the source file - when you change the schema or the default data - in which case you would not want to keep working with the old copy anyway. Now, you can test as much as you want and your changes will be kept between runs. Just be aware that, if you open the source file outside of VS, this might update its Last Modified Date and trigger a copy on the next build, even if you don't change the contents of the file. If you ever want to refresh your working database without making a code change, simply delete the working file from the bin folder and force a rebuild of the project.

Thank you for your useful information! I will mark this solved.

 

[Skydiver]

My comment has a legacy database which is an Access DB on their server

I hope to God that they have not put the Access database on their server and just shared it out via file share. Although Microsoft claims that they have finally fixed the Access database file corruption issues when the database is on a network file share, I am still very leery.

 

[cjard]

Access DB on their server

Erk!

 

[Skydiver]

Seems like @cjard has also been burned by an Access database on a file share.