2

newbie here.

I have a local db in my program. Whilst I was developing the program I used the SQL

Connection string :

SqlConnection sconn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\leemk_000\Documents\......Integrated Security=True;User Instance=True;");

Now If I want to load this program onto a different computer I am sure that this connection will no longer work simply because it will still be looking for Users\Lee_000\

I have tried to remove Lee_000 but I get this following error:

An attempt to attach an auto-named database for file C:\Users\Documents..... failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

What can I do to get a connection string to work on different computers.

With many thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2239593
  • 23
  • 1
  • 3
  • Do you intend to share that database with multiple users on the lan or it is just a single user application? – Steve Apr 03 '13 at 09:13
  • Single user application, with a local db within the program – user2239593 Apr 03 '13 at 09:27
  • I suggest you to investigate the [LocalDB install option](http://stackoverflow.com/questions/9655362/localdb-deployment-on-client-pc) for Sql Server Express. Looks like what your are searching for. Also looks at these specific [connection strings](http://www.connectionstrings.com/sql-server-2012) for LocalDB – Steve Apr 03 '13 at 09:40

3 Answers3

5

The whole User Instance and AttachDbFileName= approach is flawed - at best - especially when you want to share your database amongst multiple clients!

When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

The real solution in my opinion would be to

  1. install SQL Server Express (and you've already done that anyway)

  2. install SQL Server Management Studio Express

  3. create your database in SSMS Express, give it a logical name (e.g. YourDatabase)

  4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

    Data Source=.\\SQLEXPRESS;Database=YourDatabase;Integrated Security=True

    and everything else is exactly the same as before...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
3

If it's a local db you should be placing it within the app folder and carry it with the app right? Put the database in the App_data folder of your app and use that in your connection string

   <add name="YourConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\yourfile.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
Giorgio Minardi
  • 2,765
  • 1
  • 15
  • 11
1

You need to use a database server and let your users use it via your connection string like this;

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

"myServerAddress" should be the ip adress of your server machine.

Deniz Traka
  • 84
  • 3