SQLException Chose a different DB, Cannot attach file?

[satory]

This may or may not need to be moved to the deployment folder...


Okay I have a vb.Net application that connections to a sql server 2005 database "TacticalDB" while debugging the application I was using the connection string,

Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=" + Application.StartupPath + "\Data\TacticalDB.mdf; Database=TacticalDB; Trusted_Connection=True; User Instance=true")

Which works fine, but I have moved into getting the application ready for deployment and so I created a setup application in the same solution as the application. Now I have added the database file with the setup but when my application gets to the con.Open() line it catches an exception,

System.Data.SqlClient.SqlException: Database 'C:\Users\John\Documents\Visual Studio 2008\Projects\Blackout_Scout\Blackout_Scout\bin\x86\Debug\Data\TacticalDB.mdf' already exists. Choose a different database name.
Cannot attach the file 'C:\Program Files (x86)\Microsoft\Setup1\Data\TacticalDB.mdf' as database 'TacticalDB'.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Blackout_Scout.Module1.ScoutTeam(String MatchText, ArrayList tNames, ArrayList Players, ArrayList OppTeam)

Now I only have the one place where the application should look for the Database and thats Application.startupPath/Data/TacticalDB.mdf and if im running the newly installed exe it shouldn't look back in the myprojects folder but it is.

Can anyone provide insight as to what I'm doing wrong? is my connection string wrong in someway? Because I've been trying to sort this for a while now and its not working

[jmcilhinney]

Are you trying to deploy on your own machine? It looks like you're trying to attach the release DB while you've still got the debug DB attached and you can't attach two DBs with the same name.

Also, don't use this:

AttachDbFilename=" + Application.StartupPath + "\Data\TacticalDB.mdf;

but rather this:

AttachDbFilename=|DataDirectory|\Data\TacticalDB.mdf;

[satory]

Yeah I'm trying to deploy on my own machine, so that could be it I'll be able to try it out on one of my virtual machines later later on today so I'll give the setup a shot then.


Just a quick question also, if I'm using sql server 2005 express edition, will the users of my app, need to download that as well or will the .Net framework 3.5 install handle that? nearing in mind Im only running select statements against the DB nothing more at all?


btw, thanks for the code suggestion

[jmcilhinney]

If you want to use a SQL Server Express database then you need SQL Server Express installed. SQL Server Express is not part of the .NET Framework. If you check out the Prerequisites for your Setup project you'll see that you can include SQL Server Express and have it downloaded and installed automatically, just like the Framework.

If you don't want to have to install the database the you might consider SQL Server CE, which doesn't require installation and can be used by simply deploying a few extra DLLs with your app.

[satory]

If you don't want to have to install the database the you might consider SQL Server CE, which doesn't require installation and can be used by simply deploying a few extra DLLs with your app.

Okay thanks for the information, I went with SQL Server CE for the database and managed to convert it over okay. I redid the setup project and visual studio added System.Data.SQLServerCE.dll as a detected dependency, but after giving an early build out to other to have a look at they ran into problems, although they haven't been specific about what problems they got, although I managed to get one of them to say there was a error with a dll...


Do you have a link as to what dlls need to go with the project in a sql server ce database, or should I just make the SQL Server Compact 3.5 as a prerequisite for the installation? (still waiting to here if they get the same errors from this)

[jmcilhinney]

I've never used SQL Server CE myself but, if I wanted to know the answer to that question, I do a web search. I'm sure at least one person has posted that information before and I would guess more.