SQL Server 2005 Express Connection Issues 1

[Sech]

Heya all, my efforts to connect to SQL Server 2005 Express from Visual Basic 2005 are still not successful, despite trying it on numerous different systems. Can somebody please explain to me what I'm doing wrong?

1) I install SQL Server 2005 Express and Visual Basic 2005 Express (With .NET framework and SQL native client of course)
2) I open SQL Server using Management Studio and going in with windows authentication. It automatically sets the log on name to computername\SQLEXPRESS.
3) I create a new database called Test and add one table to it
4) I go into Visual Basic 2005 and create a new project.
5) I go to the projects application settings (Properties - Settings).
6) I set up a new connection string, choosing SQL server as the conection type and then on the Connection Properties window it asks me to select the mdf file for the database. I choose the file Test.mdf from the directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
7) I click on Test Connection.

When I do this on two out of the three home PCs I get the error:
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf".Operating system error 32: "32 (error not found)".
An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

The third PC gives me a different error:

Cannot open user default database. Login failed. Login failed for user 'DAVE\David'.

I get the same errors when I try to use a connection string inside the code directly. Does anyone have a clue as to why these errors are occuring? Do I need to set up security on the database first or is there another reason? If anyone can please help me get to the bottom of this or provide a web address where I can get the answer (step by step instructions on how to connect) then I would be most grateful.

 

[bmgmzp]

SQL Server Express does not allow remote connections by default. A google search will easily provide you with the steps to enable remote connections.

Hope this helps. ;-)

 

[Sech]

I'm not trying to connect remotely, I'm trying to connect using the local machine...

 

[jebenson]

Could you show the connection string you used?



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[Sech]

Ok I seem to have worked it out. I did this by moving the database files out of the default SQL Server directory into another directory on my PC - then the connection works fine. Why? Who knows...another great example of Microsoft being daft.

 

[Sech]

Maybe I haven't worked it out. Earlier today the copy that I made to the other directory stopped working. I copied it back there again but now my SQL Server doesn't recognise that it's there at all so I can't add tables or anything to it. Can anyone please explain to me how the hell to connect to SQL Server 2005 Express from inside VB 2005 Express? I've tried doing it on multiple systems and it always brings up an error when creating the database straight from the off inside SQL Server (see previous post).

 

[Keyth]

With the full SQL Server 2005 Workgroup version and the Developer version, you have to connect to the database by attaching it. Is there not a menu for attach to database?

 

[Sech]

You mean attach the database inside SQL Server? Why would I neeed to attach it if it is already in there? I don't understand. If you mean inside VB 2005 there is the option to add a datasource under the Tools menu but this just sets up a short cut to the connection string. I've decided to connect to Access back end instead of SQL Server because at least that seems to connect properly and without issue.

 

[AndyLee100]

I connect to SQL Server Express all the time without issues.

However I dont use any kind of wizard to connect.

It is far better to use a connection string and connect manully through code on application start.

To be honest I probably couldnt connect using a wizard even if I wanted to.