Problems connecting to .sdf database through SqlConnection/SqlCeConnection

Question

I'm having immense trouble getting a connection to a .sdf (sql compact edition) database. I can connect initially to extract rows in order to verify a username/password, but when I try to add items to the database, either by a SqlCeConnection/SqlCeCommand command or by trying to add items SqlClient/SqlCommand connection, I have no luck. I get:

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)

when using:

private void button1_Click_1(object sender, EventArgs e)
{
    System.Data.SqlClient.SqlConnection sqlConnection1 =
    new System.Data.SqlClient.SqlConnection("Data Source=C:\\Users\\Tim\\Documents\\Visual Studio 2010\\Projects\\Dispatch POS\\Dispatch POS\\GhsDB.sdf");

    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = "INSERT [Employee Table] (SSN, FirstName) VALUES ('555-23-4322', 'Tim')";
    cmd.Connection = sqlConnection1;

    sqlConnection1.Open();
    cmd.ExecuteNonQuery();
    sqlConnection1.Close();
}

or when I try:

System.Data.SqlClient.SqlConnection sqlConnection1 =
            new System.Data.SqlClient.SqlConnection("Data Source=C:\\Users\\Tim\\Documents\\Visual Studio 2010\\Projects\\Dispatch POS\\Dispatch POS\\GhsDB.sdf");

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT [Employee Table] (SSN, FirstName) VALUES ('555-23-4322', 'Tim')";
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();
            cmd.ExecuteNonQuery();
            sqlConnection1.Close();

Nothing happens, it seems to work until I check the .sdf file and see nothing has been added. I do not have SQL Server CE installed, though I do have 2008 R2 installed (I'm working on someone else's project).

Right now I'm not worried about the security holes, I'm just trying to successfully add a record. Any help would be much appreciated, as I've spent about three or four hours working on something I figure would take about 20 minutes.

Answer 1

Probably you have found the solution by now, but if you use a sdf file, the assembly you should add a reference to System.Data.SqlServerCe and then something like:

SqlCeConnection sqlConnection1= new SqlCeConnection();
sqlConnection1.ConnectionString = "Data Source = C:\\Users\\Tim\\Documents\\Visual Studio 2010\\Projects\\Dispatch POS\\Dispatch POS\\GhsDB.sdf";

System.Data.SqlServerCe.SqlCeCommand cmd = System.Data.SqlServerCe.SqlCeCommand;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "INSERT [Employee Table] (SSN, FirstName) VALUES ('555-23-4322', 'Tim')";
cmd.Connection = sqlConnection1;

sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();