Most basic example: No connection made

...I can not help this editor creating funny layout... sorry....here is my question:

Just basic exercise connecting to database: why is there no connection made

  • conString shows right value
  • message appears "nothing opened"
  • database present locally

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           SqlCeConnection con;
            // Retrieve the connection string from the settings file.
            string conString = Properties.Settings.Default.DatabaseConnectionString;
            MessageBox.Show(conString);
            // Open the connection using the connection string.
            con = new SqlCeConnection(conString);
            try
                {
                    con.Open();
                }
                catch
                {
                    MessageBox.Show("nothing opened");
                }
        }
    }
}
}

7 answers

There's an error when opening the connection and that's probably because of your connection string. Please edit your question and include your full connection string :) and also the error message.

I changed the database to Northwind.sdf, just to make sure. Next I added the full connection string as to show you the full path. I copied it from the properties as indicated by the database explorer.
Results are similar: no connection is made. I checked the database in the data explorer via "Modify Connection" > "Test Connection" and this turns ok.
I also checked in this "Modify Connection" dialog window the Data Source option saying:"Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5)".

By the way I use Visual studio 2010 express and had to add the System.Data.SqlServerCe.dll manually before being able to reference and use it. I am going to try same connection in VS Express 2013 for Desktop. Maybe it is a bug in VS 2010.......Test results in VS2013 are similar, now showing some additional error code which I will show at the end of this message (to much rubish)

Here is the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
       SqlCeConnection con;
       // Retrieve the connection string from the settings file.
       //Properties.Settings.Default.NorthwindConnectionString;
        
       string conString = "Data Source=C:\\Users\\pru005\\documents\\visual studio 2010\\Projects\\WindowsFormsApplication4\\WindowsFormsApplication4\\Northwind.sdf";
        MessageBox.Show(conString);
        // Open the connection using the connection string.
        con = new SqlCeConnection(conString);
        MessageBox.Show(con.State.ToString());

        try
            {
                con.Open();
            }
            catch
            {
                MessageBox.Show("nothing opened");
            }
    }
}

}

Output from debug in VS 2013:
The thread 0x1890 has exited with code 259 (0x103).
'WindowsFormsApplication4.vshost.exe' (CLR v4.0.30319: WindowsFormsApplication4.vshost.exe): Loaded 'C:\Users\pru005\Documents\Visual Studio 2010\Projects\WindowsFormsApplication4\WindowsFormsApplication4\bin\Debug\WindowsFormsApplication4.exe'. Symbols loaded.
'WindowsFormsApplication4.vshost.exe' (CLR v4.0.30319: WindowsFormsApplication4.vshost.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
A first chance exception of type 'System.Data.SqlServerCe.SqlCeInvalidDatabaseFormatException' occurred in System.Data.SqlServerCe.dll
The thread 0xbb0 has exited with code 259 (0x103).
The thread 0x1900 has exited with code 259 (0x103).
The program '[4576] WindowsFormsApplication4.vshost.exe' has exited with code 0 (0x0).

Pierre,

Please remove your latest duplicate posts. If you need to change something in a post, just click edit. It's much easier to follow that way.

For your problem, remove the try catch block. It's hiding the error info. The posted debug output does not tell much here...

I removed the try catch block. Running gives the following debug code in the "show output from debug" panel as showing below. At the bottom there is the text "A first chance exception of type 'System.Data.SqlServerCe.SqlCeInvalidDatabaseFormatException' occurred in System.Data.SqlServerCe.dll "

This still does not give me a clue. What is invalid ??

The complete debug text is:

'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\10.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\10.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.Sync.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.Debugger.Runtime\10.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.Debugger.Runtime.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Users\pru005\Documents\Visual Studio 2010\Projects\WindowsFormsApplication4\WindowsFormsApplication4\bin\Debug\WindowsFormsApplication4.vshost.exe', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Data.SqlServerCe\4.0.0.0__89845dcd8080cc91\System.Data.SqlServerCe.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Xml.Linq\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.Linq.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Data.DataSetExtensions\v4.0_4.0.0.0__b77a5c561934e089\System.Data.DataSetExtensions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Microsoft.CSharp\v4.0_4.0.0.0__b03f5f7f11d50a3a\Microsoft.CSharp.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Deployment\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Deployment.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The thread 'vshost.NotifyLoad' (0x173c) has exited with code 0 (0x0).
The thread 'vshost.LoadReference' (0x1f14) has exited with code 0 (0x0).
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Users\pru005\Documents\Visual Studio 2010\Projects\WindowsFormsApplication4\WindowsFormsApplication4\bin\Debug\WindowsFormsApplication4.exe', Symbols loaded.
'WindowsFormsApplication4.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The thread '' (0xabc) has exited with code 0 (0x0).
A first chance exception of type 'System.Data.SqlServerCe.SqlCeInvalidDatabaseFormatException' occurred in System.Data.SqlServerCe.dll
The thread 'vshost.RunParkingWindow' (0x1b78) has exited with code 0 (0x0).
The thread '' (0x954) has exited with code 0 (0x0).
The program '[6480] WindowsFormsApplication4.vshost.exe: Managed (v4.0.30319)' has exited with code 0 (0x0).

Somethin like this

try
            {
                con.Open();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

will get you the error message. Post it here. The debug text isn't much of help Im afraid...

Yes, that surely makes more sense. The error message shows the following text:

"The database file has been created by an earlier version of SQL Server Compat. Please upgrade using SqlCEEngine.Upgrade() method"

  • I checked how to use it but do still not understand how to apply. Some sites like msdn give loads of code. Some suggest to download a tool as plugin to VS. Others want to refere to referenes made.....etc...

  • I downloaded latest version Microsoft SQL Server Compact 4.0 (latest version?). While installing it, it is mentioned that a newer version is already installed. So I guess this is not the problem. It is only the database. But why than when creating a database with visualstudio 2010 I get the same errors. I suppose while creating the database it uses compact 4.0 or do I explicitly have to do this differently?

I haven't played a lot with CE but the error is very clear. The Northwind.sdf file you are trying to open is not compatible. Your sdf file only works with a version earlier than that of the CE you have installed.

I would look for a newer Northwind.sdf online or try (as the error message suggests) to do an Upgrade() on the file.

Good luck!