VWD Express 2008, SQL Server Express 2005.
I have been using SQL Server Express with ASP and VB for some years - I just create a DB in Management Studio Express, have SQL Server set to 'SQL Server and Windows authentication', and use a connection string with 'sa' and a password (I never have managed to get it working with 'integrated security', but that is probably irrelevant to this post). So I have a list of DB's attached to SQL Server that I can see, attach, detach etc in MSE.
I am getting started with .NET. Working through a VWD Express book tutorial involves adding login/registration etc using the wizard(s). This works fine, the wizard creates an ASPNETDB.mdf database, this shows in Data Explorer, and I can add users etc which create entries in that DB. If I right click that DB, the menu shows a 'detach' option, which implies it is 'attached', though DB does not show in MSE. But it works, so presumably that is OK.
The next stage in the tutorial involves creating a simple DB and using a gridview. I right-click the website in Solution Explorer and use 'add item' to add a SQL DB, accepting the message box that suggests I put it in the App_Data folder. This then shows in Solution Explorer, and I can add tables and data etc. I then add a gridview to the page and try to configure a datasource. I choose 'new connection'. On the next dialog 'Add Connection', if I leave the Data Source as the default 'Microsoft SQL Server (SqlClient)', then I get stuck, if I change it to 'Microsoft SQL Server Database File (SqlClient)' then I can carry on. But the books all say that I should be using the first default option. Specifically the symptoms are:
1. With 'Microsoft SQL Server (SqlClient)' it makes no difference whether I have the server name as 'MACHINENAME', 'MACHINENAME\SQLEXPRESS' (which is what my server is called) or '(local)' (which is what the book tells me to use), or what authentication I use (I leave it on Windows authentication, as that is what the book says):
1a. If lower down the dialog I leave the default 'Select or enter a database name', the only DBs that the dialog recognises are the ones that show in MSE i.e. all my old DB's, or at least, the ones I currently have attached. Typing in the new DB name just results in an error message saying it cannot find it
1b. If instead I select 'Attach a database file' and browse to the .mdf file, then it accepts it, but 'Test Connection' gives a long error message, the first part of which is "Unable to open the physical file .C:\....\testDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process)".
2. If in the top Datasource field I choose 'Microsoft SQL Server Database File (SqlClient)', then the dialog changes to a much smaller one, I can browse to the .mdf file, and Test Connection works, and so does the gridview.
So although I have found a way to get things working, it is not what the tutorials tell me should be happening. And I am thoroughly confused. What is the difference between 'Microsoft SQL Server (SqlClient)' and 'Microsoft SQL Server Database File (SqlClient)'? Am I doing something wrong?
When we create a new database in the folder “App_Data” with IDE (Visual Studio or VWD), and access it in Server Explorer, it’s attached with user-instance.
A SQL Server user instance is similar to a normal SQL Server instance but it is created on demand while normal instances are created during setup. The service account for a user instance is the Windows user who opened the SQL Client connection to the database. When a connection with this connection string opens successfully, the user application is connected to a user instance of SQL Server Express running as the user who opened the connection.
That’s why you cannot see the newly created database in SQL Server Management Studio Express, because the database is not attached in it.
SQL Server manages the locking of the database data in its memory. Thus, if more than one SQL Server instance has the same file open, there is the potential for data corruption. If two different user instances use the same database file, one instance must close the file before the other instance can open it.
If the database is using by IDE, you cannot attached it in SQL Server Management Studio Express.
If you want to connect the database with Microsoft SQL Server (SqlClient), you need detach it in IDE and attach it in SQL Server Management Studio Express.
Otherwise, you have to use Microsoft SQL Server Database File (SqlClient).
Please refer to the document below, I think it’s quite helpful.
SQL Server 2005 Express Edition User Instances
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Thank you very much. Very helpful. That article is also very good. I understand now.
It seems my book tutorial is 'wrong' - in that it shows the dialogs for creating a connection to a DB that is attached to the parent instance (using Microsoft SQL Server (SqlClient), but the steps it leads you through only create DB files via the IDE.