Connectionstring from app.config

[pizzaboy]

Through the Server Explorer, I'm able to test the connection, and a successful connection is made.

The connectionstring added to app.config was generated automatically, however when I try to open a sqlConnection, the connection seems to fail!?!?


The connectionstring declared in the Server Explorer is: "Data Source=D:\My Docs\Documents\Visual Studio 2008\Projects\POS\POS\Data\posDB.sdf" with the provider set to: ".NET Framework Data Provider for Microsoft SQL Server Compact 3.5".

The connectionstring in app.config is:
<connectionStrings>
<add name="POS.My.MySettings.posDBConnectionString"
connectionString="Data Source=|DataDirectory|\Data\posDB.sdf"
providerName="Microsoft.SqlServerCe.Client.3.5" />
</connectionStrings>


Retrieving the connectionstring through app.config using:
Dim dt As New DataTable
Dim sql As String = "SELECT * FROM Branch"
Using sqlCon As New SqlConnection(My.Settings.posDBConnectionString)
Using sqlCmd As New SqlCommand(sql, sqlCon)
Try
sqlCon.Open()
Dim sqlRdr As SqlDataReader = sqlCmd.ExecuteReader()
If sqlRdr.HasRows Then dt.Load(sqlRdr, LoadOption.PreserveChanges)
sqlRdr.Close()
Catch ex As Exception
result = False
End Try
End Using
End Using

causes an error at "sqlCon.Open()", I presume its the connectionstring as I've tried running the query (sql variable value at that point) directly in Server Explorer and that returned with no problems.

The value of "My.Settings.posDBConnectionString" returns "Data Source=|DataDirectory|\Data\posDB.sdf". The exception I'm getting complains "A network-related or instance-specific error occurred while establishing a connection to SQL Server"?!?

Any ideas?


Any help would be greatly appreciated.

Thanks.

 

[JohnH]

Use the SqlServerCE provider for CE databases, and not SqlClient provider. System.Data.SqlServerCe Namespace Overview

 

[pizzaboy]

Changing to SqlServerCe has managed to resolve the connection.

However, I'll be installing SQL Server 2005 Express Edition on the client pc, would the SqlServerCe still operate under that condition?

I do need to install a version of SQL on the client when deploying? I can't just copy my database to the client right?

Bear with me, very newbie!

Cheers.

 

[JohnH]

Those are different databases, that operate differently. Both have their own prerequisite, which you can select in Prerequisites dialog for deployment. The installer checks that what is required is present, and if not installs that first.

 

[pizzaboy]

Cheers.

Guess I'll find out soon enough, when I can't get it working on the client pc.

Wish me luck!


Thanks again.