Trouble connecting to SQL Server 2005 Express

[BravesCharm]

I am trying to connect to SQL Server 2005 Express with Visual C# 2005
Express using this code:
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\temp.MDF;User
ID=MyUsername;password=MyPass;DATABASE=Test;Integrated
Security=False;User Instance=True");

conn.Open();

conn.Close();
}

I get an error while calling the method Open Here is the error
"Failed to generate a user instance of SQL Server. Only an integrated
connection can generate a user instance. The connection will be
closed."

so I changed the connection string to

SqlConnection conn = new SqlConnection(@"Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\temp.MDF;User
ID=MyUsername;password=MyPass;DATABASE=Test;Integrated
Security=False;User Instance=False");

and I get error when while calling Open:

"Database 'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\test.mdf' already exists.\r\nCould not
attach file 'C:\\Projects\\test1\\test\\bin\\Debug\\temp.MDF' as
database 'Test'."

The user account MyUsername has is part of Server Role sysadmin.

I can also connect to this database using Microsoft SQL Server
Management Studio Express without a problem with the same user.

So does anyone know what i'm doing wrong?

Thanks,
BravesCharm

 

[Sylvain Lafontaine]

You are attempting to connect to an existing database (Test) and to attach
and to connect to another database (Temp.MDF) at the same time. Chose
between Test or Temp.MDF but not both. More infos at:

http://msdn2.microsoft.com/en-us/library/ms254504

http://www.connectionstrings.com/

 

[BravesCharm]

Sylvain,
Thanks for the help. I read the links and have a better
understanding of User Instances now. Can't use User Instances with SQL
security(only integrated). I reworked my connection string to look
like this:

SqlConnection conn = new SqlConnection(@"Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\test.MDF;User
ID=MyUsername;Password=MyPast;Integrated Security=False;User
Instance=False");
conn.Open();

and it still doesn't work! I get the error:

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

I also tried:
SqlConnection conn = new SqlConnection(@"Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\test.MDF;User
ID=MyUsername;Password=MyPast;Initial Catalog=Test;Integrated
Security=False;User Instance=False");
conn.Open();

and got the same error

 

[BravesCharm]

I was able to connect to the database with

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;User
ID=MyUsername;Password=MyPassword;Initial Catalog=Test;Integrated
Security=False;User Instance=False");
conn.Open();

However, I still can't seem to connect using AttachDbFilename and I
also can't seem to connect using Database Explorer(which I believe uses
AttachDbFilename). The connection strings I've used are:

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;User
ID=MyUsername;Password=MyPassword;AttachDbFilename=|DataDirectory|\test.mdf;DATABASE=Test;Integrated
Security=False;User Instance=False");

and


SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;User
ID=MyUsername;Password=MyPassword;AttachDbFilename=|DataDirectory|\test.mdf;Integrated
Security=False;User Instance=False");

and both times I get the error:
An attempt to attach an auto-named database for file
C:\\Projects\\test1\\test\\bin\\Debug\\test.mdf failed. A database with
the same name exists, or specified file cannot be opened, or it is
located on UNC share."

Anyone know what I'm doing wrong?

 

[BravesCharm]

I found the issue. For some reason I had to put the entire path for
AttachDbFilename and you have to put DATABASE= or Initial Catalog=

So it worked when I did:
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;User
ID=MyUsername;Password=MyPassword;Initial Catalog=Test;Integrated
Security=False;User Instance=False");
conn.Open();

Or

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;User
ID=MyUsername;Password=MyPassword;Database=Test;Integrated
Security=False;User Instance=False");
conn.Open();