Link to home
Start Free TrialLog in
Avatar of codefinger
codefingerFlag for United States of America

asked on

SQL Server CE connection error message

In app.config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="WindowsFormsLearning_C.Properties.Settings.NorthwindConnectionString"
            connectionString="Data Source=E:\Documents and Settings\SampleDatabase\Northwind.sdf"
            providerName="Microsoft.SqlServerCe.Client.3.5" />
    </connectionStrings>
</configuration>

Open in new window


In button code:

// connect to a database and retrieve data:
            try
            {
                SqlConnection sqlc = new SqlConnection();
                sqlc.ConnectionString = Properties.Settings.Default.NorthwindConnectionString;
                

                sqlc.Open();   <<------Fails

                DataSet xds;
                SqlCommand xsqlcmd = new SqlCommand();
                StringBuilder sbsql = new StringBuilder();

                sbsql.Append("Select distinct ");
                sbsql.Append(" 'Company Name' ");
                sbsql.Append(" from Customers ");

                xds = new DataSet();

                SqlDataAdapter sqdap = new SqlDataAdapter(sbsql.ToString(),sqlc);

                sqdap.Fill(xds, "Customers");

                if (xds == null) { 
                
                    //do something
                }
               else {

                   foreach (DataRow xdr in xds.Tables[0].Rows)
                   {

                       //ds.Tables[0].Rows[0]["ColumnName"].ToString() 
                       Console.WriteLine(xdr["Company Name"]);
                   }

                    // do something else
                    
                    }
                sqlc.Close();
            }
            catch(Exception exc){

                MessageBox.Show(exc.Message);
                
              }
            }

Open in new window



Exception message (some of it, anyway):

System.Data.SqlClient.SqlException was caught
  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)
  Source=.Net SqlClient Data Provider
  ErrorCode=-2146232060
  Class=20
  LineNumber=0
  Number=-1
  Server=""
  State=0

I know the path to the database file is correct, because I can successfully connect to it and test the connection from within the IDE connection dialog.  I copied and pasted that connection string into the config file.

So what am I doing wrong?

Thanks in advance!
Avatar of Norman Maina
Norman Maina
Flag of Kenya image

The path of the database is the problem
 i.e "Data Source=E:\Documents and Settings\SampleDatabase\Northwind.sdf"

Is the error when you try to connect to the DB froma PDA?
If yes, you need to make the db cacheable locally in the PDA...cuz its trying to locate it in your file system in the computer.
Avatar of Alfredo Luis Torres Serrano
Are you importing System.Data.SqlServerCe??
Avatar of codefinger

ASKER

The database file and the code is on my PC.  (Windows XP with latest SP)  

I am using Visual C# 2010 Express Edition.

I am using System.Data.SqlServerCe, and I have a reference to it in my project.
Please try changing
 in the button code

sqlc.ConnectionString = Properties.Settings.Default.NorthwindConnectionString;

For

sqlc.. connectionString=""Data Source=E:\Documents and Settings\SampleDatabase\Northwind.sdf;providerName="Microsoft.SqlServerCe.Client.3.5"";
Data Source=E:\Documents and Settings\SampleDatabase\Northwind.sdf;providerName= "Microsoft.SqlServerCe.Client.3.5"

Now I get:

Keyword not supported: 'providername'.

I get this whether the providerName is surrounded in single quotes or double quotes.  If I do not supply a providerName,  I get the same error I started with.
your problem is the path containing spaces between Documents and Settings.
Ok, trying new path:
Connection string is now:
Data Source=E:\temp\SampleDatabase\Northwind.sdf

and I have moved the database file to this location, as well as removed it and re-added it to my project.

Error is:
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)


Please advise.  Thanks!
ASKER CERTIFIED SOLUTION
Avatar of codefinger
codefinger
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
See my last comment.