[RESOLVED] [2008] Database data not saved or gets deleted somehow...

[jmh228]

I'm using a local database which has one table. I'm able to populate the table programmatically, look through it, etc. I can exit out of the VB program and when I reload the project and run it, my data is still there. Excellent. If at any point I click on the database in the Database Explorer (so I can check out the table data while the program isn't running), all the data in the table is gone and I have to subsequently regenerate it.

One note: There is a red "x" on the icon next to the database name. Does this mean something? Maybe the data isn't really being saved to the DB?

I'm assumming you'll need more information from me in order to help solve this.
Thanks for you're help.

[techgnome]

There's a link in my sig, titled "I swear I saved my data, where'd it run off to?" ... it's actually a common problem, and it simply a case of needing to understand how the build process works (in short the database your app is dealing with is NOT the one you are viewing though the explorer - it's a copy.)

-tg

[jmcilhinney]

The database you see in the Database Explorer is NOT the database that your application connects to. Consider this. You create an application for which you build a database. You start testing that app, which involves adding, editing and deleting data in the database. You now add more functionality to app and you want to test it again from scratch, with a clean database. You you have to go through your database and delete all your existing data? Even if you did, all your identity (AutoNumber) columns won't reset, so they'll keep producing IDs from where they were instead of at 1. What happens when you're ready to deploy? Do you have to clean out your database again before you release? What if you forgot and you distributed an app with test data in the database?

None of that's going to happen. When you create a database for your project your are creating a source file. You build up the database schema and, maybe, add some default data that all copies are supposed to start with. That's the database you see in the Database Explorer. When you build your project, which happens every time you run it in the debugger, the application is compiled and the output is placed in bin\Debug or bin\Release folder. Included in that output is a copy of your database. It's THAT database that your application connects to at run time.

That allows you to do whatever you want to the database at run time without affecting the original, which is still in pristine condition. Whenever you want to reste the database to its original condition for testing you simply use the properties of the database to have a new copy created in the output folder, overwriting your old test database with a new copy of the original. The same goes for when you deploy. You'll be distributing a copy of the original which will never have seen any of your test data.

Note also that, when you make changes to the schema or the default data in your original database, you need to make sure that a copy is written to the output next time you debug or you won't see those changes. Follow the second link in my signature for more information on this. Basically, you either use Copy Never and then switch to Copy Always and back again each time you want to force a new copy, or else you use Copy If Newer. That documentation says that there can be issues with Copy If Newer but I've never had any, so that's what I recommend.

[jmh228]

Thanks for the info guys. Let me get a handle on the new information and see if I can get it to do what I want (which is to programmatically write to the real database since I've worked out the bugs and the table's data doesn't need to change again). Then I'll change the thread to resolved.

[jmcilhinney]

Thanks for the info guys. Let me get a handle on the new information and see if I can get it to do what I want (which is to programmatically write to the real database since I've worked out the bugs and the table's data doesn't need to change again). Then I'll change the thread to resolved.

No, no, no! I've just finished saying that you specifically do NOT want to write to your original database. It's set up to write to a copy because that's exactly what it SHOULD do. When you deploy your application you want to be able to send out a pristine database with it, not one that's full of test data or has had a bunch of test data deleted.

Basically there are two ways you'll deploy a database with an application. VS makes it very convenient to deploy a data file with your app, be it an MDF for SQL Server Express or an MDB for Access. In those cases you specifically add a database file to your app. It sits in the Solution Explorer with all the other source files. You should absolutely NOT be touching that database while testing. You should always work with a copy, and that's what VS does by default.

The other option is to use a database that is constantly attached to a server. In that case there is no source file added to your project to copy. When testing you create a database on your server yourself and that's the database you access. It doesn't matter that you only have one database full of test data though, because you won't be deploying the actual database. the end-user will use a script to create a new database on their server when they install your app, so a new database is created on the spot.

[jmh228]

When you create a database for your project your are creating a source file. You build up the database schema and, maybe, add some default data that all copies are supposed to start with.

Okay, so the bottom line is I want to write default data to the database. So how do I write into the database from my program as I have almost 4000 rows of data that I want permanently in the database? After that, how do I go back to using a local copy of the database?

[jmcilhinney]

Ah, sorry. That is a bit different then. Where is this data coming from exactly? If it's already sitting in some other source then you probably would want to transfer it programmatically but I'd tend not to use your actual application. The specifics would depend on the situation but usually I'd just whip up a little Console app or whatever and hard-code a connection string into a SqlConnection.

[jmh228]

I used a procedure to generate the table data. The procedure will not be accessible to a user once the app is in its final form.

So I closed VB and copied the debug copy of the .sdf into the main directory. When I loaded VB I now have the data I want in the SQL DB. This is probably not a good practise. I would rather do it the right way as you suggested and use a separate app to write to the database directly. Do you have a good example of that? Would this app be a separate project within the solution or how does that work?

[jmcilhinney]

You have access to the original database via the Database Explorer. If you used SQL code to generate the data then you can do that in VS. Just right-click your database in the Database Explorer and select New Query. It will default to a SELECT statement but you can replace the SQL code with anything you like. Enter your valid SQL code and hit the execute button. You may get an error message if the designer can't graphically represent your code but it will still be executed successfully.

[jmh228]

I'm not familiar with using sql commands. I just used the Data Source Configuration Wizard to create a dataset from my database. Then I used this to add data to my dataset:

Dim newEntry As WarDBDataSet.LossProbsRow
newEntry = WarDBDataSet.LossProbs.NewLossProbsRow
 
newEntry.columnA = colAdata
etc...
WarDBDataSet.LossProbs.Rows.Add(newEntry)
 
Dim rowsAffected As Integer = 0
rowsAffected = LossProbsTableAdapter.Update(newEntry)