error connecting to sql ce in asp.net sqldatasource

Question

I keep getting an error while trying to connect my asp.net project to sql ce 4.

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible."

UPDATE - If you are getting this error make sure you specify the proper provider name in both the connection string and the sqldatasource.

providerName = "System.Data.SqlServerCe.4.0"

How connect to SQL Server Compact 4.0 in ASP.NET?

Answer 1

It's also worth knowing that you need to ensure a correct provider name if you wish to use the 'GetFactory' methods on a connection string to decouple your db connection code from your business logic.

Answer 2

I had the problem too, because I switched from SQL Server Express to SQL Server Compact 4.0. My code:

            SqlDataSource SqlDataSource2 = new SqlDataSource();
            SqlDataSource2.ID = "SqlDataSource2";
            SqlDataSource2.ConnectionString = @"Data Source=|DataDirectory|\database.sdf;Password=passwordddddDDD;Persist Security Info=False;";
            SqlDataSource2.SelectCommand = "SELECT * FROM [table1]";
            SqlDataSource2.ProviderName = "System.Data.SqlServerCe.4.0"; // THIS line solved my problem!
            GridView1.DataSource = SqlDataSource2;

Thank you, this helped me a lot!