none
Cannot open SqlConnection: "A network-related or instance-specific error occurred while establishing a connection to SQL Server"

    Question

  • I'm new to C#, and have been having problems trying to connect to a local database using an SqlConnection in a simple app I'm trying to create. I started in Visual C# 2010 Express by selecting Data > Add new Data Source, and followed the steps in the wizard, selecting a database with a dataset type model. I then added the tables and columns I wanted to add in the empty database in the database explorer.

    I have created an SQLConnection in code, passing the connection string to the constructor. However, when I try to open this connection, it throws an exception with the message:

    "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

    The connection string I am using is: "Data Source=C:\\Users\\... \\Visual Studio 2010\\Projects\\MusicDatabase\\MusicDatabase\\MusicDB.sdf;Persist Security Info=True". The path for the connection string is the same as the connection string in the database properties tab.

    I checked the connection properties in database explorer and made sure that there was a saved password which matched the one in the database, and made sure that the Test Connection button succeeded without error. I tried adding "Password=xxx" (where xxx is the password of the database) to the start of the connection string to make sure that this wasn't, but this did not fix the error.

    I have since removed the password from the database altogether in the database properties, and this doesn't fix the problem either, so I don't think it is anything to do with the password.

    I don't have any experience with creating database connections, so I'm not sure what I've done wrong, or if there are any steps I've missed, or if there's any additional information that might be helpful. Any help appreciated.

    Friday, June 25, 2010 10:42 AM

Answers

  • Hi,

    Thank you for the mail, I found the solution.

    For SQL Server Compact Edition we should use SqlCeConnection instead of SqlConnection. we should also add a reference to the 'System.Data.SqlServerCe'. then I tried following code and works fine:

    using System;
    using System.Data.SqlServerCe;
    
    namespace ConsoleApplication1
    {
      class Program
      {
        static void Main(string[] args)
        {
          using (SqlCeConnection dataConnection = new SqlCeConnection("Data Source=C:\\Downloads\\MusicDB.sdf;Persist Security Info=True"))
          {
            try
            {
              dataConnection.Open();
              Console.WriteLine("Success.");
            }
            catch (Exception e)
            {
              string errorString = e.Message;
              Console.WriteLine(errorString);
            }
            finally
            {
              dataConnection.Close();
            }
          }
          Console.ReadKey();
        }
      }
    }
    /*OUTPUT
    Success.
    */

    Best Wishes,

    Yasser


    LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
    Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data templating
    • Marked as answer by m_userName Monday, June 28, 2010 5:59 PM
    Monday, June 28, 2010 4:58 PM

All replies

  • Welcome to the MSDN Forums.

    According to 'MusicDB.sdf', it seems that you use Microsoft SQL Server Compact Edition.

    Is this service installed? is it running?

    Please see if you can connect to this database using Microsoft SQL Server Management Studio. then we can continue with C# code if you could connect.

    Regards,

    Yasser


    LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
    Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data templating
    Friday, June 25, 2010 1:37 PM
  • Thanks for getting back to me.

    I have Microsoft SQL Server Compact Edition 3.5 installed (I presume this was installed when I installed Visual C#/Visual Web Developer Express edition).

    I'm not sure what you mean about whether the service is running, or how to check whether it is. I'm also not sure what how to connect to the database using Microsoft SQL Server Management Studio.

    I've never done anything with SQL Server, so I'm not sure how to use it to check whether I can connect to the database. I have SQL Server 2008 R2 installed. I've taken a look at the tutorial: http://msdn.microsoft.com/en-us/library/ms166996.aspx, but I don't have the SQL Server Management Studio option when I point at SQL Server 2008 R2 from the start menu (I followed the link to install Management Studio Express, and apparently it is already installed)

    If it helps, I am able to connect to the database by double clicking the .sdf file in the database explorer in Visual C# Express.

    Sunday, June 27, 2010 2:01 PM
  • Hi,

    Just open the SQL Server Management Studio then in the File Menu > Connect Object Explorer dialog change server Type dropdown to SQL Server Compact Edition and then in Database File dropdown choose and you're ready to go.

    Hope this helps.

    Regards,

    Yasser


    LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
    Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data templating
    Monday, June 28, 2010 6:42 AM
  • Hi,

    The problem for me is actually opening SQL Management Studio, in that I've never used it before and I can't find it, so I'm not even sure if I have everything installed correctly.

    On the msdn page: http://msdn.microsoft.com/en-us/library/ms166996.aspx, the note at the top explains that SQL Management Studio is not available with SQL Server Express (which is what I'm assuming I've got -in the Start menu > Programs section, it just says "SQL Server 2008 R2", and the only option this has is Configuration Tools > Installation Centre).

    I couldn't find SQL Server Management Studio Express on the Start menu > Programs or in Program Files, so I assumed I didn't have it. However, if I try to install it using the msdn website: http://www.microsoft.com/downloads/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en#filelist, when I try to install the various components, it tells me I already have them...

    So trying to troubleshoot, where should SQL Server Management Studio/SQL Server Management Studio Express be kept? I want to check what I have installed, and whether I have the correct things installed, then I can check whether I can connect to the database.

    Many thanks

    Monday, June 28, 2010 8:04 AM
  • OK, don't worry, it's available at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=56ad557c-03e6-4369-9c1d-e81b33d8026b (Microsoft SQL Server 2008 R2 RTM - Management Studio Express).

    Anyway, if you are not going to download, please mail me the sdf file to yasser.zamani#live.com.NO_SPAM (replace # with @ and remove .NO_SPAM please). I'll try to connect it via C# and will post the code here.

    Best Wishes,

    Yasser


    LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
    Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data templating
    Monday, June 28, 2010 8:20 AM
  • I tried to download Management Studio Express using that link (although I installed PowerShell 1.0 for Windows Vista -the link you posted defaults to XP). However, I try to run the setup.exe, it eventually sends up an error message saying that a network error has occured and aborts the setup.

    I am going to create a separate thread about that in the SQL Server forum. In the mean time, I have sent you the sdf file (the attachment is in the second email).

    Thanks for your time and help :)

    Monday, June 28, 2010 1:07 PM
  • Hi,

    Thank you for the mail, I found the solution.

    For SQL Server Compact Edition we should use SqlCeConnection instead of SqlConnection. we should also add a reference to the 'System.Data.SqlServerCe'. then I tried following code and works fine:

    using System;
    using System.Data.SqlServerCe;
    
    namespace ConsoleApplication1
    {
      class Program
      {
        static void Main(string[] args)
        {
          using (SqlCeConnection dataConnection = new SqlCeConnection("Data Source=C:\\Downloads\\MusicDB.sdf;Persist Security Info=True"))
          {
            try
            {
              dataConnection.Open();
              Console.WriteLine("Success.");
            }
            catch (Exception e)
            {
              string errorString = e.Message;
              Console.WriteLine(errorString);
            }
            finally
            {
              dataConnection.Close();
            }
          }
          Console.ReadKey();
        }
      }
    }
    /*OUTPUT
    Success.
    */

    Best Wishes,

    Yasser


    LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
    Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data templating
    • Marked as answer by m_userName Monday, June 28, 2010 5:59 PM
    Monday, June 28, 2010 4:58 PM
  • That fixes it, thanks :)
    Monday, June 28, 2010 6:00 PM
  • had this problem for a long while. This solution worked for me.
    Thursday, December 09, 2010 1:30 PM