I saw this question in the microsoft.public.vb.database newsgroup this morning and since I wanted to start a “question of the day” series of blog entries, I thought this was as good a candidate as any.
The question:
I am having some problems with Visual Basic 2005 Express Edition and databases. I have tried three different pieces of code (one I made that executes an INSERT command on a SQL database, another sample project from Microsoft also using SQL, and finally one from VB 2005 EE for Dummies using an Access database) on two different computers but none of the databases saved. I have never worked with databases before, but I followed all the instructions exactly for the samples I used. I could see the information was in the dataset when I ran one of the programs. It had multiple forms that displayed the information in the database in two different ways. When I modified the data in on form and clicked the save button and then went to the other form I could see the updated data. When I closed the forms and started the program all the changes were gone, however in the video that went along with the code I saw the code run just as it should, the data came back when he restarted the program. Am I missing something here? I don't see what I could have possibly done wrong. It does not make sense to me at all. HELP ME!!!,
My response:
Let's walk through the steps used to create a database and get it to keep your data. There are a few issues that pop up when working with VB Express that don't occur with other versions (as they have more options). In VB Express, a common option when working with databases is to add a new database to your project. It sounds like you've done that and added one or more tables to the database. This leverages the instance of SQL Server Express installed with VB Express. Once you create your database it should appear as a Data Connection in the Database Explorer. As you've probably already discovered, this is where you can add and configure your database tables. Once the table is added (again, using the Database Explorer against your Data Connection), return to the Data Sources window and add that table using the Data Source Configuration Wizard (it ran once before when the database was first created) to the existing DataSet (or create a new one). Drag the DataSet from the Data Sources window to a Form [design] surface. This constructs a number of plumbing controls that bind the data to the database table and expose user interface elements like the DataGridView and toolbar to manage it. I expect you've reached this point already, but I wanted to make sure we're on the same page.
Okay, here is where I think your problem lies: by default, when you execute your application (click the "Debug" button), Visual Studio takes the .MDF database file from your project and copies it to the Bin\Debug directory associated with your project. It's this version that you're writing to. When you execute the program the next time, the process is repeated so the version containing your data is overlaid with the original version of the database. Microsoft seems to think this is a "feature"--to start with a virgin database each time. I don't agree, but there are a couple of ways to get around this issue.
In your Solution Explorer, select the .MDF database file and examine the properties. Note that the "Copy to Output" property defaults to "Copy Always". If you change that to "Do Not Copy", Visual Studio won't copy the project database to Bin (again). I suggest that you choose this option once the database is fully configured as changes you make to the project database schema (using the Database Explorer) won't be seen when you test your application. The other option "Copy if newer" tells Visual Studio to copy the database (and clear out the data) if the schema changes. Again, this can delete the data when you click F5 to debug the application.
So, what's the "real" solution? Well, there are really two viable solutions. First, you can create a stand-alone database with the (free) SQL Server Management Studio Express and use this tool to configure it. Here you'll find similar tools and wizard-like dialogs to create a new database and add tables, views and whatever you need. However, you'll have to "detach" the database from the SQL Server Express instance before you'll be able to create a Data Connection in Visual Studio Express--I expect due to the licensing restrictions in VS (or a bug).
Another approach is to use Visual Studio's Database Explorer to create a Data Connection and create the database in the process. This (IMHO) is what folks should do instead of adding a database to their projects. Warning, after you create this standalone database, it's attached to the local instance (SQLEXPRESS) of SQL Server Express--it's not added to your project UNLESS you click "Yes" when asked "Do you want to copy the database to your project?". Unless you want to get in the same trouble as before, click "No".
The downside to this standalone strategy is deployment. In this case you'll have to figure out how to get your standalone database installed on the target systems yourself. One approach might be to add it to the project at the last minute--just before you build your deployment configuration CAB files. But that's fodder for another answer...
In other editions of Visual Studio you can Attach and Detach databases and are afforded far more options when working with SQL Server (and other DBMS engines).
Hth
thanks for the information .It is useful to me.
can u help me, how would i attach my exsisting database files .mdf to the exsisting project.
I want my application's database to be in sqlexpress once the Application installation is over .for that reason i want to attach my database .is it right .if you have some other method please let me as early as possible.it's urgent ....Thanks in advance.