Saving data via DataSet - going mad!

[batvink]

Hi all,

before I start, I have read the various threads on this topic, followed the advice (as you will see below) but it still hasn't resolved my problems. I'm also creating a simple database application quickly, so data binding is really my best option if I can get it working.

I'm trying to save data to a database (SQLite), but it's just not happening. Onscreen, all looks well, but when you close the application, the data changes are lost.

I have:
A Data Connection
A Data Set with 3 of my tables defined in it
A DataGridView Control, successfully displaying records from the dataset. It's using a Binding Source, which it automatically created.
A small number of text fields bound to the same BindingSource.
A Data Adapter that was automatically created for me.

So now I can click on a row in the DataGridView, and the text fields update correctly. I can edit the text field, navigate away, come back and my new data is still intact.

I thought that data was automatically updated for me. I have read, however, that this isn't necessarily the case. So in the DataGrid RowValidated Event I followed some advice I found that suggests at this point you should EndEdit on the Binding Source, and Update the Data Adapter. The Data Adapter errors, complaining about concurrency and no rows being available to update. I have ignored this error, but the data still doesn't reach the database.

Any .net data geniuses (genii?) out there that can shed some light on where my issue lies?

[johnpolo]

are u running the .exe file from bin or u doing this experiment right after debugging ?

[batvink]

Thanks for the fast reply!

I'm running in debug, from bin/debug. I did read about the problem where the database gets replaced each time and fools people. Right now, the database is stationary, it isn't being copied into the executable folder each time. So I should be looking at the same updated version of the data each time.

[steve_rm]

Hello,

Does the tables have any relationships between them. If you have set foreign key in some of the tables?

You could try doing this before you update.

myDataSet.EnforceConstraints = False
'Then for updating
myBindingSource.EndEdit()
myTableAdapter.Update(myDateSet.myTable)
myDataSet.EnforceConstraints = True

Hope that helps,

Steve

[batvink]

Thanks for the advice. At one point, I set the constraints to disabled in the visual editor. But I have tried so many things I don't know what the other settings were at the time. I'll try this again.

[John_SC]

The problem is that VB has a setting which specifies if the database is automatically copied into the working directory every time it starts. In effect you have two copies of your database, and possibly three. The first is the database that might reside anywhere on your computer. This is the one that you linked to when you created your connection. When you go through the wizard, it generally indicates that your database is outside your application and asks you if you want to copy it into it. If you say yes, you get your second copy which I will refer to as the master copy in the root of your application (and VB now forgets about the first copy - it is irrelevant). Then when you run the application, it will copy the Master copy of the database into the bin\debug subdirectory (do a search of your project for the database files and you should find two copies). This is the working version of the database. Every time you start, it copies your master copy overtop your working copy. Note: It does pop up a message when you are creating the connection string stating this. This is great when you are debugging, since you are always starting with a standardized database, but obviously not the greatest when you are releasing the application.

What you need to do is change the "Copy to Output Directory" property of the database. I generally click on the database icon (yellow cylinder) in the Solution Explorer. You have three options. The default is to "Copy Always". This is fine for debugging, not for production. The second is "Do Not Copy". This is a good option, but be warned, it deletes the current copy of the working database the first time you set this. You will have to manually go back and copy it into the proper location. The third option is a nice fall back solution, and should always work. It is "Copy if Newer", and will only copy the master DB over the working DB if the Master DB is newer (i.e., if it has been modified outside the application). Obviously if you make a years of changes to your working DB, and then go an modify the structure of your Master, the next time you run the app all your data will be lost. So "Choose wisely!".

I hope this helps.

[batvink]

Thanks again, I will check these things out. I have been looking at all the right things, but quite possibly in the wrong combination. It's been a "try it and see what happens" experience!