Connection to SQL Server 2005 database from VB 2005 Express problem, please help

Dear All,

I am beginner. I wanted to create a connection from VB 2005 Express to a database created with SQL Server 2005.

From the project I did this:

Add new data source, -> database -> New Connection -> Browse got the .mdf file, then click on OK, after waiting the following error occurs:

"An error has occured while establishing a connection with the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces,error: 26 - Error Locating Server/Instance Specified)

I can't find how to correct this, please help! Note that I use SQL Server 2005
(not express).

Thank you in advance,

Nikosgeo

Where is the .mdf file located?

I don't have VB Express installed, but I suspect that it's trying to use SQL Server Express by default. In the Add Connection dialog (the one where you browse to the MDF file), click the Advanced... button in the lower right. Check the Data Source property; if it says, ".\SQLEXPRESS", that's the problem. Change it to "." or "(local)" (without the quotes) and click OK. That should allow you to connect.

Thank you for your answers.

Unfortunately, the Data Source has .\MSSQLSERVER or .\SQLEXPRESS to choose from and .\MSSQLSERVER does not solve the problem.

The .mdf file is in the following path (local hard disk):

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\

It may be something basic, since I am beginner.

This time the error message was different:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces,error: 25 - Connection string is not valid)

Hello,

Ensure that you have configured the SQL Server 2005 Listeners. By default SQL Server 2005 is locked down and needs TCP/IP enabled. You may need choose Shared Memory or Named Pipes to connect to SQL in VS2005

Make sure that SQL Server 2005 services are running too.

All these options and services can be found in the "SQL Server Configuration Manager"

- SQL Svr 2005 Network Configuration/Protocols for MSSQLSERVER
- Services (Stop and Start Services)

Well, for all the other newbies (I am a newbie to MS SQL stuff, but not to SQL, I am a PHP/MySQL programmer) here is how I was able to connect to this #$@!$!!:mad: MS SQL Server 2005 Express.

Installing the SQL server (get the MS SQL Server with Advanced Services and choose to install Server Management Studio Express along with the SQL server):

instance name: leave as default named instance SQLExpress -this is VERY IMPORTANT later...

service account: choose built in system, local acct. Also request SQL server and SQL browser be started when the system starts.

auth mode: choose Windows auth mode

When finished installing, check that SQL server is running. Right click on My Computer, choose manage, and under services find the SQL services config manager, choose sql 2005 services, and check that your SQL server instance is running.

Download and install SQL Server Management Studio Express if you did not already install it as part of the SQL express install.

Run SQL Server Management Studio Express.
VERY VERY important: server connection string is Computer Name\sqlexpress

Pray a small prayer.

Connect to database.

When you try to connect from within VB Express, you have to specify your server as Computer Name\sqlexpress.

So if your computer is named Frank, you would specify the server as Frank\sqlexpress.

Very important.

Viv

where do i specify the computer name\sqlexpress? the advance properties windoe won't allow me to change the value of the data source.

I've had a likewise problem, but I use regular VS 2005. Try copying your database to your project folder (where you see the maps "bin", "My Project" and "obj") and again in the map bin\Debug. Then reestablish your connecion.

i tried visual basic to open my database file on the sql server folder but i get the error that "may be caused by the fact that under the default settings SQL Server does not allow remote connections."

so, i copied the database files (mdf and ldf) on to the folder, C:\Documents and Settings\windowsuser\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS, where i think visual basic looks for the database files. i've tested it but it didn't work. same error.

visual basic has detected that i have sql express and sql server installed but it won't let me change the value to workstation\sqlexpress on the advance window.

First try making sure that the service is running.

On Win XP, go to the My Computer icon, right click, choose manage,
and in the services and applications folder, click the services entry,
and look for SQLEXPRESS.

Right click it and choose the start option.

Then you can try to connect to the SQLEXPRESS db using the SQL Server Management Studio Express Edition free program from Microsoft. This is where you would type in the computer_name\sqlexpress for the db connection.

In the install, the default name should already by sqlexpress if you are installing sql server 2005 express...

I was having the same problem with nikosgeo and have tried all the help methods above.

After all these methods are done, can i conclude here that microsoft Visual Basic 2005 Express Edition is not compatible ms sql server 2005?

The best thing im going to do now is install the express edition of sql from microsoft and hope to god this will work.

Try this Guys.......
The problem occurs when trying to connect using server explorer....

Delete the connection in Server Explorer, then did Add Connection and, in the Choose Data Source dialog, instead of selecting SQL Server Database File (which would sound right) select <other> and then, in the Data provider field, selected .NET Framework Data Provider for SQL Server. Pressing Enter resulted in a different dialog box, than had select SQL Server Database File. After this it’s all downhill. Then select the server, choose Windows Authentication and click on Test Connection - It will connect now!!!!

Remember this solution may or may not work for you. I should also mention that the database appears in the Data Connections list with a .dbo at the end, which was not the case before.

One other difference is that the connection string now contains
Data Source=<myservername> instead of server=(local) or server=.\MSSQLSERVER, which was there before.


Hope it will work for you........

"Delete the connection in Server Explorer, then did Add Connection and, in the Choose Data Source dialog, instead of selecting SQL Server Database File (which would sound right) select <other>"

Dear asubash, there's no <other> option in my data source dialog..i wonder why. It has only Ms Access Database File & Ms SQL Server Database File. There's also no content at all in the drop down menu of Data Provider.

my pc installed ms visual studio by default which is bundled wt sql server 2005 standard edition. and connection sql server in that vstudio seem to be successful. again i wonder why....