Connecting C# to SQL Server Compact database

Question

Hi I'm trying to connect an SQL server compact database to my program and I want a button that deletes all entries from the database, when I Press said button the program throws an exception and gives the following error message "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)"

Help Please? =]

Sorry, Code is Below =]

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.SqlClient;
using System.Data.SqlServerCe;


namespace Booking_system_Final
{
    public partial class PendingJobs : Form
    {
        SqlConnection sc = new SqlConnection("Data Source=C:\\Users\\Administrator\\My Documents\\BMS_Data.sdf");
        public PendingJobs()
        {
            InitializeComponent();
        }

        private void PendingJobs_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'bMSDataSet.Bookings' table. You can move, or remove it, as needed.
            this.bookingsTableAdapter.Fill(this.bMSDataSet.Bookings);
            // TODO: This line of code loads data into the 'bMS_DataDataSet1.Bookings' table. You can move, or remove it, as needed.


        }

        private void button1_Click(object sender, EventArgs e)
        {
            sc.Open();
            SqlCommand cmd = new SqlCommand("DELETE FROM Bookings");
            cmd.Connection = sc;
            cmd.ExecuteNonQuery();
            sc.Close();
            MessageBox.Show("Database Cleared");


        }
    }
}

Well let's see some code then. Also, can you open it up in SQL Server Management Studio? — Arran
– Arran, Commented Mar 1, 2013 at 12:09
Are you passing correct credentials, pointing to correct SQL server instance and Database? — Monie corleone
– Monie corleone, Commented Mar 1, 2013 at 12:15
I'm pointing to a locally stored SQL server Compact database and the directory name is correct — Colonel_Custard
– Colonel_Custard, Commented Mar 1, 2013 at 12:17
Yeah it does I checked the Directory in Windows Explorer and the SDF file is there and the Connection String Points to the file directly, I can't see where I've gone wrong here — Colonel_Custard
– Colonel_Custard, Commented Mar 1, 2013 at 12:27

www · Accepted Answer · 2013-03-01 19:13:19Z

10

Try use SqlCeConnection class rather than SqlConnection:

SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = "Data Source = C:\\Users\\Administrator\\My Documents\\BMS_Data.sdf;Persist Security Info=False";

answered Mar 1, 2013 at 19:13

www

4,4011 gold badge26 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zeeshan Over a year ago

How to modify the data source in case of LAN? Let's say, the .sdf is located on shared network as \\xyz-PC\db\dbfile.sdf

ErikEJ · Accepted Answer · 2013-03-01 13:40:00Z

6

If you want to connect to SQL Server Compact, use SqlCeConnection, SqlCeCommand etc. Add a reference to the SQL Server Compact ADO.NET provider, System.Data.SqlServerCe.dll

answered Mar 1, 2013 at 13:40

ErikEJ

42.1k5 gold badges82 silver badges120 bronze badges

Comments

Jens H · Accepted Answer · 2013-03-01 12:26:40Z

0

Have a look at this blog article: SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

This goes step-by-step through what you might need to do:

In short:

SQL Server should be up and running.

Enable TCP/IP in SQL Server Configuration

Open Port in Windows Firewall

Enable Remote Connection

Enable SQL Server Browser Service

Create exception of sqlbrowser.exe in Firewall

Recreate Alias

About the where and what to do in each step, you will find more in-depth information in the article.

You may also want to have a look at the Connection strings for SQL Server Compact. There you can find other variations of the connection string you could try to play with.

answered Mar 1, 2013 at 12:26

Jens H

4,6422 gold badges28 silver badges39 bronze badges

2 Comments

Colonel_Custard Over a year ago

thanks for the detailed answer, I have found the source of the problem which is that SQL server for some reason cannot connect and I am now getting an error saying "the remote procedure call failed"

Jens H Over a year ago

@ReeceCottam: I am not completely familiar with this, is a remote procedure the same as a stored procedure? If so, this is a feature that might possibly NOT supported by the CE edition: Have a look at the bottom of this feature comparison table: (Comparison of SQL Server Compact, SQL Server Express 2012 and LocalDB )[erikej.blogspot.de/2011/01/… where it says "Stored procedures, views, triggers: No"

Retired_User · Accepted Answer · 2013-03-01 13:03:28Z

0

You seems to be using a bad connection string. (Or the file path is wrong). Check out http://www.connectionstrings.com/sql-server-ce for connection string options.

answered Mar 1, 2013 at 13:03

Retired_User

1,59511 silver badges17 bronze badges

Collectives™ on Stack Overflow

Connecting C# to SQL Server Compact database

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related