sql server compact sdf file within c# project - problems with SQLException

Question

This question is related to this previous SO question

I have fixed the string used in the program so it now runs that line of code.

Why does the following fail on the line myAdapt.Fill(mySet, "AvailableValues"); with a error SQLException was Unhandled; 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

The database is part of the project so why should there be difficulties connecting?

I've tried running the sql string in a sql file and it runs ok.

I've tried deleting all columns from the DGV.

void PopulateGridView()
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseDGVexperiments.Properties.Settings.DatabaseDGVexperimentsConnStg"].ConnectionString);
    SqlDataAdapter myAdapt = new SqlDataAdapter("SELECT refText FROM helloworld", conn);
    DataSet mySet = new DataSet();
    myAdapt.Fill(mySet, "AvailableValues");
    DataTable myTable = mySet.Tables["AvailableValues"];
    this.uxExperimentDGV.DataSource = myTable;
}

score 2 · Accepted Answer · answered Jun 09 '12 at 14:07

2

Are you trying to open a SqlCE file (SDF) with SqlConnection?
I think you should a use SqlCeConnection and the ADO.NET classes specific for Sql Compact

using System.Data.SqlServerCe;
.....

void PopulateGridView() 
{ 
    SqlCeConnection conn = new SqlCeConnection(ConfigurationManager.ConnectionStrings["DatabaseDGVexperiments.Properties.Settings.DatabaseDGVexperimentsConnStg"].ConnectionString); 
    SqlCeDataAdapter myAdapt = new SqlCeDataAdapter("SELECT refText FROM helloworld", conn); 
    DataSet mySet = new DataSet(); 
    myAdapt.Fill(mySet, "AvailableValues"); 
    DataTable myTable = mySet.Tables["AvailableValues"]; 
    this.uxExperimentDGV.DataSource = myTable; 
}

answered Jun 09 '12 at 14:07

Steve

213,761
22
232
286

does this mean that effectively everything else will be the same as if using full sql server just the adapter and connection objects that are slightly different? – whytheq Jun 09 '12 at 17:17
From the specs for ADO.NET every class as SqlCeConnection, SqlCeCommand, SqlCeDataAdapter, etc... implements a common Interface thus these classes are required to implement the same methods. The functionality could be different, but in my experience there are only minor variations to leverage specific functionality of a particular database. A notable difference is the support of Stored Procedures which SqlCe lacks (so no `SqlCeCommand.CommandType = CommandType.StoredProcedure`) [See here for refs](http://en.wikipedia.org/wiki/SQL_Server_Compact) – Steve Jun 09 '12 at 17:20
(MS up to their usual tricks) that's a massive difference! ...what workarounds are commonly used for the lack of the sproc command? – whytheq Jun 09 '12 at 17:32
Apart from switching to another database the only option is to write your query in strings inside your code. Perhaps, if you don't need the sharing of the database you can opt for [LocalDB](http://stackoverflow.com/questions/9655362/localdb-deployment-on-client-pc), it is like SqlServer Express but easy to install. – Steve Jun 09 '12 at 17:39

sql server compact sdf file within c# project - problems with SQLException

1 Answers1

Linked