SQL 2005 Express connection string problem in Visual Basic. Invalid value for key 'user instance'.

I am having difficulty with SQL Server Express when using Visual Basic 2008.

I can connect to and use SQL 2005 Express via SQL Server Management Studio without any problems. I can use databases from SQL 2005 Express in Visual Basic if I use the Visual Basic wizards. What I cannot do attach to a database using a connection string in code. I keep getting an error message saying "Invalid value for key 'user instance'. My connection string looks like this:

Dim connStr As String = ("Server=(Local);AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventureworks_Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True)")

I have tried numerous alternatives on the connection string and all give the same error message. I am logged itnto the computer as administrator and have tried with and without passwords for loging in at boot up.

This problem exists on 3 computers. Two running Windows XP Professional and the other running Windows 7 Ultimate.

What am I doing wrong and how do I correct it?

EDIT

If I remove the "user instance=true", I get an error message "Invalid value for key 'connect timeout'. If I remove the "connect timeout=30" then I get another error suggesting that the server is not configured for remote connections.

The server is SQL 2005 Express and on the same computer's "C" drive so should not require remote connections.

I have already tried using "sp_configure 'user instances enabled','1' " on the database after connecting through SQL Server Management Studio and, although the command is executed successfully, I still cannot use the connection string.

May I add that this problem does not exist on only one computer. I have tried using connection strings on 4 computers with different operating systems and all of them fail so could it be something in the setup of SQL Server 2005 that I am doing wrong.

I have searched the web for nearly 7 years in an attempt to resolve this problem and I have still not succeeded

6 answers

Probably the "User Instance" feature is not enabled on the SQL Server (local) instance that you try to connect to. And also, probaby the "User instance" feature is not what you want as far as I can read your question.

Try remove the "User Instance=True" part from your connection string.

You can read more about the user instance feature here,
http://www.connectionstrings.com/sqlconnection/using-an-user-instance-on-a-local-sql-server-express-instance/ to understand its purpose.

Hope this helps!

In the post you provided the connection string like this,

Dim connStr As String = ("Server=(Local);AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventureworks_Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True)")

Is this how it looks in your code? Cause that looks not correct. There should be a semi colon after the last value and the ')' should not be there. Also enclosing the path to the file might be a good bet. Something like this.

Dim connStr As String = "Server=(Local);AttachDbFilename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventureworks_Data.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True;"

Or better up, do not enable the User Instance feature (I am assuming you do not want this feature)

Dim connStr As String = "Server=(Local);AttachDbFilename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventureworks_Data.mdf';Integrated Security=True;Connect Timeout=30;"

Does this work?

I have used another web browser and that seems OK.

Your last suggestion appears to have syntax problems. For example, you have an apostrophe just before "C:" and another after "\Adventureworks_Data.mdf. The second one just comments out the rest of the line of code. I have modified it to:

Dim connStr As String = "Server=(Local);AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventureworks_Data.mdf;Integrated Security=True;Connect Timeout=30;"

but that produces an error saying this at the end "(provider: Named Pipes Provider, error 40 - Could not open a connection to SQL server.

The strange thing is that I can connect to the same database using the VB wizards without any problem.

This is not unique to this computer. It is happening on 2 other machines as well. I am beginning to think that it may be something common in the way the SQL Server 2005 Express has been set up although I have just accepted the defaults on installation.

Ah, I forgot you were using VB.Net.

Anyway you're getting closer now.

The error is probably caused by the value for Server.

You need to find the instance name of the SQL Server installation if you don't know it.

It might be "localhost\SQLEXPRESS" for you SQL Server instance (you are connecting to an SQL Server instance on the local machine right?). Then it would be:

Dim connStr As String = "Server=localhost\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adventureworks_Data.mdf;Integrated Security=True;Connect Timeout=30;"

If this is not correct you can find the instance name by going to Control Panel -> Administrative Tools -> Services. Scroll down to SQL Server and the name in the parenthesis is the instance name. For example "SQL Server (SQLEXPRESS)". Use the name inside the parenthesis after the backslash for the Server value in the connection string.

Thank you SO MUCH for your time and patience. Your last suggestion worked after checking the correct name for the server