Connecting MS SQL database to C# application

I am new to c# and MS SQL databases.

I have created a database called Rulesdb2 and have attached it to my c# application. I had previously attached it as a local database but have changed this to a direct connection by specifying NO to "Copy the file to your project" prompt

Below is my connection string:

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

However when I run the application I get an error indicating that the application is looking for the database in debug\bin directory as shown below.

"An attempt to attach an auto-named database for file C:\Users\User\documents\visual studio 2010\Projects\RBS07\RBS07\bin\Debug\Rulesdb2.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

Can someone explain where I am going wrong ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hello Pat,

it seems that you have an instance of SQL Server installed,
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Rulesdb2.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True
the below connection string will allow you to connect via the instance.

 Data Source=(local);Initial Catalog=Rulesdb2;User ID=username;Password=password" providerName="System.Data.SqlClient

Open in new window

Cheers Trentos
pclarke7Author Commented:
Hi Trentos,
sorry that doesn't work and I don't understand why the original connection string does not work. I am totally confused about whether it should be a local database connection or not ?

What I want is a database that I can read from and update/Write to from my application. At some point in the future I will need to locate this database on a remote server. Can you tell me the benefit of a local connection V a direct connection to my database. Is there any documentation available on this that will guide me through this ?

Try this
Data Source=(local)\SQLEXPRESS;Initial Catalog=Rulesdb2;Integrated Security=False;User ID=username;Password=Password

If this does not work then make sure you are not using Windows Auth mode in which case you would set Integrated Security to True and leave out your user name and password.

Also, if you did not stick with the default name for your sql server, it might not be SQLEXPRESS.  a good way to find that if this doesn't work is to open SQL Server Management Studio and click the combobox to show servers.  You will use (local)\InstanceName

Plus: take out any reference to the database file.  That file should not be part of your project.  Unlike Access which uses an engine to help you process a file, MS SQL is a server.  It uses the file internally and that file should for the most part never be messed with.  The server sends the data directly to your program.  If you have the file in your project, it needs to come out.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Hello pclarke7, attached is a sample .net 4.5 c# website.
It has a sql express mdf database in it and a connection string and a grid to display the data

the sample is based on all default microsoft and sql install locations.
when it is opened with visual studio it should run and work

i used vs2012..see how you go.

It is important to not that there are about 8 files with a txt extension that needs to be removed
this was done so this 1920's website could upload the zipfile.

files for rename
to Rulesdb2.csprojResolveAssemblyReference.cache

the same applies for all the ones with .txt extensions, just remove the .txt BEFORE you extract the ZIP file.

cheers Trentos2
The site is actually .net 3.5

also if you created you database in VS 2 things to remember;

start VS by using "Run as Administrator"
the same goes for SQL Server Mgmt studio if you are using that.
it helps to overcome issues sometimes

also though not related to your immediate ? if you are creating the MDF in VS then consider the tips in this link
Fix Database creation issue

the shortened version
use SSMS to Stop all database instances
past this in your start | run box
%LOCALAPPDATA%\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Open in new window

select the folder,
delete everything in it
and go back to VS and create your databases as normal

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pclarke7Author Commented:
Thanks  XGIS and VBJim,
finally got it sorted.

 I think the problem was due to the fact that I had a number of versions of MS SQL Server installed on my PC and had at various time alternated between a local copy of the database and a direct link to the database. I backed up the database and then uninstalled all versions of MS SQL SERVER deleted all MS SQL SERVER services and removed all folders linked to MS SQL SERVER. I then installed MS SQL Server 2012 (32 bit) , restored the database and created a direct (non local) connection. Now working like a dream, and I can eventually able to move on.

Thanks for your help on this
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.