Connection string for SQL Server Compact - windows home server

Question

I'm using SQL Server Compact 4.0 and hosting the site on Windows Home Server 2011, program is written in C# - Visual Web Developer.

I've tried every possible connection string variation that I can think of but I keep getting this error:

Format of the initialization string does not conform to specification starting at index 0.

This is the string I have currently

<add name="Inventory" 
     connectionString="Data Source=C:\Program Files\Windows Server\Bin\WebApps\Site\App_Data\PHSInventory.sdf;"  /  >

I've also tried to point to it with |data directory|. I've run out of blue links on Google to click on.

STACK TRACE

[ArgumentException: Format of the initialization string does not conform to specification starting at index 0.] System.Data.SqlServerCe.ConStringUtil.GetKeyValuePair(Char[] connectionString, Int32 currentPosition, String& key, Char[] valuebuf, Int32& vallength, Boolean& isempty) +1182 System.Data.SqlServerCe.ConStringUtil.ParseStringIntoHashtable(String conString, Hashtable values) +188 System.Data.SqlServerCe.ConStringUtil.ParseConnectionString(String& connectionString) +128 System.Data.SqlServerCe.SqlCeConnection.set_ConnectionString(String value) +679 System.Data.SqlServerCe.SqlCeConnection..ctor(String connectionString) +46 System.Data.SqlServerCe.SqlCeDataAdapter..ctor(String selectCommandText, String selectConnectionString) +73 DataAccess.FillDataTable(String SQL) +149 Default.Page_Load(Object sender, EventArgs e) +378 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +51 System.Web.UI.Control.OnLoad(EventArgs e) +92 System.Web.UI.Control.LoadRecursive() +54 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +772

DATA ACCESS

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web.Configuration;

using System.Data.SqlServerCe;

public class DataAccess
{

    private int count = -1;

    public int Count
    { get { return count; } }

    private string connectionString;
    public string ConnectionName
    {
        set
        {
            if (WebConfigurationManager.ConnectionStrings[value] == null)
            {
                throw new Exception("Cannot find connection string named '" +
                   value + "' in web.config");
            }
            //Retrieve connection string from web.config.
            connectionString =
               WebConfigurationManager.ConnectionStrings[value].ConnectionString;
        }
    }

    public DataAccess(string connectionName)
    {
        ConnectionName = connectionName;
    }

    public DataTable FillDataTable(string SQL)
    {
        // Fill dataTable.
         WriteToTraceLogForDebugging(SQL);
        SqlCeConnection sqlConn = new SqlCeConnection("Data Source=D:\\InventoryData\\PHSInventory.sdf;");
        SqlCeDataAdapter objAdapter = new SqlCeDataAdapter(SQL, sqlConn);
        DataTable dt = new DataTable();
        objAdapter.Fill(dt);
        count = dt.Rows.Count;  
        return dt;
    }
    public int ExecuteNonQuery(string SQL)
    {
        WriteToTraceLogForDebugging(SQL);
        SqlCeConnection sqlConn = new SqlCeConnection(connectionString);
        sqlConn.Open();
        SqlCeCommand objCmd = new SqlCeCommand(SQL, sqlConn);
        count = objCmd.ExecuteNonQuery();
        return count;
    }

    public object ExecuteScalar(String SQL)
    {
        WriteToTraceLogForDebugging(SQL);
        SqlCeConnection sqlConn = new SqlCeConnection(connectionString);
        sqlConn.Open();
        SqlCeCommand objCmd = new SqlCeCommand(SQL, sqlConn);
        //calling code needs to cast data (to integer, string, double, etc.) upon receipt. 
        return objCmd.ExecuteScalar();
    }

    private void WriteToTraceLogForDebugging(string sql)
    {
        System.Web.HttpContext.Current.Trace.Write("sql****", sql);
        System.Web.HttpContext.Current.Trace.Write("CONNECTION*****", connectionString);
    }

}

Answer 1

You are using the System.Data.SqlConnection classes, for SQL Server Compact you must use the System.Data.SqlServerCe classes, like SqlCeConnection.

And then you are constructing the SqlCeDataAdapter wrong, you should use:

 SqlCeDataAdapter objAdapter = new SqlCeDataAdapter(SQL, sqlConn);

or

 SqlCeDataAdapter objAdapter = new SqlCeDataAdapter(SQL, connectionString);

Answer 2

Your enclosing quotes are off:

Data Source="C:\Program Files\Windows Server\Bin\WebApps\Site\App_Data\PHSInventory.sdf;"

Should be:

"Data Source=C:\Program Files\Windows Server\Bin\WebApps\Site\App_Data\PHSInventory.sdf;"

Note that your initial string tries to place Data Source as a literal before the actual connection string:

connectionString=Data Source="

Where is should be part of the connection string.