Connecting to SQL Server 2005 Express 1

[Sech]

I am currently working on a Visual Basic 2005 Express project, and wish to connect to SQL Server 2005 Express for the data tables. However it is not letting me connect. I do not have a user name or password specifically set up under SQL Server, it is just going in with Windows authentication (DAVE\SQLEXPRESS). I have attempted to set up an application setting in the VB project to get the connection string, I browsed for and found the mdf file. The connection string's value is showing as:

Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Customer MI.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

However when I try to test the connection with SQL Server open, it brings up the error - Cannot open user default database. Login failed. Login failed for user 'DAVE\David'.

Does anyone know why it would be doing this and how to get it to connect properly? I have not activated any fancy settings or anything, I just want to link VB 2005 Express with SQL Server 2005 Express.

 

[Sech]

Oh I don't know if this is related or not but I don't seem to be able to Dim a variable with the type Sqlconnection. I can set a variable to SqlClient.Sqlconnection. I've seen other people's code examples where they seem to be setting variables to Sqlconnection type e.g. Dim sc as new SqlConnection. Is my version of VB 2005 Express incorrect? Do I need to add anything to it to enable this?

 

[jebenson]

As to the 'Dim sc As SqlConnection' issue...

If you want to be able to use the"shorthand" declaration, you need to include this line atthe top of your code:

Imports System.Data.SQLClient


Now, about the connection issue. Here's what ConnectionStrings.com says:

Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.


Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Download the SQL Native Client here. The package contains both the ODBC driver and the OLE DB provider >>




So, try this connection string:

Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Customer MI.mdf";Database=<Your_DB_Name>;Trusted_Connection=Yes;

Also, you may want to download the SQL Native Client and see how that works.



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]

Thanks for replying. I tried that new connection string in code (Adding in extra "s and &s around the directory structure of course) but it still comes up with the same error. I tried to download the SQL Native Client but it says I already have a higher version on my PC. When I create a new database with tables in SQL Server 2005 Express do I need to set up some sort of special log on permissions? The error is going on about accessing the default database which I think is set up as the Master database.

 

[Sech]

Would somebody please be able to try out the following so I can see if it is just my PC mucking up and not an error in the code I'm using?...

(1)Download and install Visual Basic 2005 Express and SQL Server 2005 Express.
(2)Log into SQL Server with name of computer followed by \SQLEXPRESS, go on in with Windows Authentication so you do not have to set User Name and Password
(3)Make a new database inside SQL Server with a table in. Do not set up any security settings.
(4)Go into VB 2005 Express (leaving SQL Server open)
(5)Make a new project.
(6)Go to the projects properties.
(7)Go to Settings tab.
(8)Set up a new application setting - give it whatever name, set Type to (Connection String), Scope should automatically change to Application. For Value click the ... button - Select Data Source window appears. Choose Microsoft SQL Server Database File, with .Net Framework Data Provider. Click Continue. Browse for the mdf file of your project - mine is in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, let me know if yours is different in any way. Select Use Windows Authentication. Click the Test Connection button. I get the error: Cannot open user default database. Login failed. Login failed for user 'DAVE\David'.

I know this is a lot to ask but if someone could please do the above and let me know what happens on their PC I would be most grateful. I have to work out the cause of this error, whether it is just my PC or if I'm doing something else wrong.

 

[abjs59]

I was having the same problem, here's follows qhat worked for me:
right click on your database, click on option, on the Autoclose select True.
I might have to reboot the pc