Question Deploying a application vb express 2010 with a sql server 2008 r2 database

[Dancer123]

Hi,
I am new to this forum and a novice at programming.
I have recently developed a back end database in SQL server 2008 R2 and attached it to a application in VB express 2010. Everything works fine and when i publish the application, it works fine on my machine, but once i try to setup up the application on another machine i get the error message: A network related or instance specific error occurred while establishing a connection to SQL Server. Server was not found or not accessible please verify the instance name is correct or allow remote connections.
I have read numerous forums on this saying about changing the connection string and instance name.. but with being novice i dont understand what needs to be changed etc.
The sql server allows remote connections, but i am unsure what else needs changing to allow the application to work with the database on another machine.
Thanks

 

[jmcilhinney]

The connection string contains the name of the instance and the name of the database. I would assume that at least the instance name will change when you deploy. Maybe if you were to show us what you already have and tell us what you want to do.

 

[Dancer123]

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\DanceDatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

That is my connection string. I want it so that my application can be used on another computer with it being able to connect to the database. I have set perequisites to SQL server express and ms .net framework 4 but it is a project for university so it will be used on a tutors laptop but does it matter what sql server they have in order for my database to work?

Also i am unsure as to do i need to place the database in a different location for the other computer to acces it :s or will they be able to access it from my machine
Thanks for all your help

 

[jmcilhinney]

That's a bit of a dodgy connection string. There are two ways to work with SQL Server databases: create them in Management Studio so they are permanently attached or create them in Visual Studio so they are part of your project and only attached on demand. In the first case the data file should be stored in the SQL Server data folder and you use the Initial Catalog attribute to specify the database name. In the second case the data file should be stored in your project folder and you use the AttachDbFileName attribute to specify the file path. You are attaching on demand via AttachDbFileName but your data file is stored with the permanent databases. While what you're doing is not invalid, it doesn't really make sense. You should choose which of the standard ways you want to go and make the appropriate changes. If you go the first way then you will need to change the instance name and the database name if required, although both may remain the same. If you choose the second option then you should use |DataDirectory| in your connection string so you will almost certainly not need to change anything when you deploy. You can find examples of both at:

ConnectionStrings.com - Forgot that connection string? Get it here!

 

[Dancer123]

I have developed the database in management studio and then used the data source wizard in vb to attach it.. so should my connection string look something like this instead..
Data Source=PC-Name\SQLEXPRESS;InitialCatalog;DanceDatabase;Integrated Security=True;Connect Timeout=30;User Instance=True
The link you provided doesnt work i am just guessing this from what youve said and what ive seen on the internet or would you say the second option is the best way to go? Also do they need a copy of my database or do they access the one on my machine?
Sorry, just its really stressing me out :/
Thanks

 

[jmcilhinney]

If you created the database in Management Studio then you don't use the Data Source wizard to attach the file. When specifying connection details you choose the server/instance first and then you choose either a database or a data file. You chose the data file, which is wrong. You should have chosen the database. Then the wizard would create the correct connection string for you. If you do actually want a data file that you can deploy with your app then you shouldn't have used Management Studio. You should have added a database directly to your project just as you add other items. Again, the wizard will then build the correct connection string.