I have tried to read around this for literally weeks and I have hit a complete block in my progress!
I am using Visual Basic 2010 Express to build an application which should connect to a SQL server, I have followed the connection wizard exactly and I cannot get the file to make a connection to the database.
I am running Microsoft SQL Server Management Studio 2012 and have created a table called TestingDatabase, the .mdf file is located C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA (also with the _Log file).
When I follow the Visual Basic connection wizard to begin with I go through and select My SQL Server Database file (SQL Client) and then point it to my .mdf file, however on first attempt it says that I don't have admin permissions, so by right clicking the file and changing the permissions to allow for my specific user (despite the fact that I'm logged on the Admin user account anyway) I'm now getting a message telling me that the file is already in use. I've tried running it just on it's own and it still won't make a connection.
My Microsoft SQL Server Management Studio is always on and I'm not running any other programs which could be connected to it in the back ground. If I connect excel to the database I have no problems whatsoever with the connection, it just doesn't like to connect through VB.net.
I am at a complete loss and highly frustrated with this problem so I would be eternally grateful if someone could help me sort out this problem.
There are two ways to work with SQL Server databases. Either you create the database in SQL Server and it remains attached permanently and you specify an Initial Catalog in your connection string or you create the database in Visual Studio and the data file is part of your project and attached on demand and you specify an AttachDbFileName in your connection string. You've done it half one way and then half the other.
So, first you need to decide which way you want to go. Will your app be deployed to a multi-user environment where all users connect to a single instance of SQL Server or even a single-user environment where SQL Server is installed on a server? If so then you want to go with the first option. If your app is going to be deployed for single users where each user will have SQL Server Express installed on their own machine then you want to go with the second option. With the first option the user will manage the database through Management Studio while with the second it will be managed purely via your app.
In that case you want to create the database in Management Studio and then connect to the database through the SQL Server instance. You do not make any direct reference to the MDF file.
When you want to create a Data Source in your VB project you select the SQL Server option, not the SQL Server data file option. You then select the instance name and the database name. That's the name of the database as it appears in Management Studio.
If you then look at your connection string you will see that it uses Initial Catalog and not AttachDbFileName.
ah, I think I may have one further problem then! I cannot select just SQL Server on the connection wizard, it is not under "Service" or "Object" but when I click "Database" and "Dataset" then New Connection and try and change the Data Source the only options I have is Microsoft Access Database file, Microsoft SQL Server Compact 3.5 and Microsoft SQL Server Database file.
I've tried googling the problem, but I can't find it through the myriad of other solutions about connecting to the exact database file which you described earlier!
I haven't used VB Express for a long time but I think it might be that those are the only options available. It appears that you have basically two options:
1. Build the database in Management Studio and then detach the database so that the MDF data file is then free to be used as a local data file in your project.
2. Create and build the database in Visual Studio so that it's a local database from the get go.
The issue is how you're going to deploy the database. If you want a permanently attached database then, although you could, you probably won't want to distribute an MDF file. That means attaching the database to your SQL Server instance at some point, in order to either create a backup that can be restored during deployment or to generate scripts that can be executed during deployment.
You will have to edit the connection string in the config file when deploying too. You'd likely have to change the Data Source anyway, but you'll also have to change the AttachDbFileName to Initial Catalog and remove the User Instance.