SQL Server conection not working, Please help!

Question

hi, I'm not very good at deployment and I'm facing a problem:

First Off, I'm using Vista.

i have created a windows application in vb.net which has a database file named Customerdb.mdf in it.

I have selected the database and in the property, I have selected Embedded Resource option.

I have used the following connection:

Dim constring As String
constring = Application.StartupPath.ToString() + "\Customerdb.mdf"
Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" + constring + ";Integrated Security=True;User Instance=True")

My problem is that I cannot access the database after installing the setup. the following error is thrown each time i try to access the database:

"An attempt to attach an auto-named database for file C:\Program File\App\Customerdb.mdf failed. A database with the same name exits, or specified file cannot be opened, or it is located on UNC share."

It seems like once installed, the application cannot have access to the database.

Could you please tell me what is wrong with the connection?

Any help will be very very appreciated. Thanks in advance

Answer 1

I have selected Embedded Resource option.

That means the database file is embedded inside the created *.exe file as a resource. It's not a separate file on the file system. The file name you're trying to create with this code: Application.StartupPath.ToString() + "\Customerdb.mdf" doesn't exist.

To fix this, you have a few options:

• Change from Embedded Resource to Content and tell it to "Always Copy"
• Check if the file exists and write the byte array out to disk before opening the database if it doesn't
• Change from Embedded Resource to Content and set your deployment project to put it in the Application Data folder.

Of those, you really should to do the last one, and don't forget to deploy sql server express with your app.

Finally, Sql Server Express is a really poor choice for use as a single-user desktop database. You really should go for a desktop (or "in-process") engine like SQL Server Compact Edition, sqlite, or even Access.