C# - Can't connect to my local DB, using SQLServer

Question

I'm new to C# and I'm trying to connect my application to DB, so I tried this :

public ActionResult ConnexionBD(){

        SqlConnection cnx;
        cnx = new SqlConnection("Data Source=C:\\Users\\Antoine\\Documents\\Visual Studio 2012\\Projects\\Application\\Application\\App_Data\\Database1.sdf;Initial Catalog=tstado;Integrated Security=True;Pooling=False");
        cnx.Open();
        SqlCommand cmd;
        cmd = new SqlCommand();
        cmd.CommandText="INSERT INTO Objet VALUES(1,'F',"+DateTime.Now+",'Moi')";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = cnx;

        cmd = new SqlCommand();
        cmd.CommandText = "SELECT * FROM Objet";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = cnx;
        SqlDataReader r;
        r = cmd.ExecuteReader();
        String chaine="";
        while (r.Read())
        {
            string nom = (string)r["Nom"];
            string date = (string)r["Date"];
            string user = (string)r["User"];

            chaine+=nom+"\t"+date+"\t"+user+"\n";
        }
        cnx.Close();
        return View(chaine);
    }

It breaks at line 4 : cnx.Open(); . The error said that network is unfindable or inaccessible, due to the network. (Exact error in French :"Une erreur liée au réseau ou spécifique à l'instance s'est produite lors de l'établissement d'une connexion à SQL Server. Le serveur est introuvable ou n'est pas accessible. Vérifiez que le nom de l'instance est correct et que SQL Server est configuré pour autoriser les connexions distantes.")

English translation;

An error related to the network or instance-specific occurred when 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

I think the string connection may be wrong, but I'm not sure.

If someone could help me ..!

Can you put breakpoints to see where it breaks? – JustAPup May 04 '15 at 14:46 — JustAPup, May 04 '15 at 14:46

score 0 · Answer 1 · answered May 04 '15 at 14:52

0

In your Solution Explorer you should mark your Db file's property "Copy to Output Directory" as "copy always" (or "if newer" depending on your needs) so it's copied to your BIN folder where your debugger has access. In your connection string then you will have to simply put Database1.sdf

answered May 04 '15 at 14:52

agfc

852
1
6
13

I did it, and I still having the same error. I may try IIS maybe – Antoine Ravier May 04 '15 at 14:57

gws2 · Accepted Answer · 2015-05-04T15:39:13.763

0

Since you are using local database (.sdf) you'll need to reference System.Data.SqlServerCe (found in Add Reference > Extensions) and change your code accordingly (see below).

public ActionResult ConnexionBD()
    {
        SqlCeConnection cnx = new SqlCeConnection(@"Data Source=C:\Users\Antoine\Documents\Visual Studio 2012\Projects\Application\Application\App_Data\Database1.sdf");
        cnx.Open();
        SqlCeCommand cmd = new SqlCeCommand
        {
            CommandText = "INSERT INTO Objet VALUES(1,'F'," + DateTime.Now + ",'Moi'",
            CommandType = CommandType.Text,
            Connection = cnx
        };

        cmd = new SqlCeCommand {CommandText = "SELECT * FROM Objet", CommandType = CommandType.Text, Connection = cnx};
        SqlCeDataReader r = cmd.ExecuteReader();
        String chaine = "";
        while (r.Read())
        {
            string nom = (string)r["Nom"];
            string date = (string)r["Date"];
            string user = (string)r["User"];

            chaine += nom + "\t" + date + "\t" + user + "\n";
        }
        cnx.Close();
        return View(chaine);
    }

edited May 04 '15 at 15:39

answered May 04 '15 at 15:18

gws2

599
3
8

Now the problem is "key word not supported : 'initial catalog' ". Thank you for your help – Antoine Ravier May 04 '15 at 15:37
Sorry, remove the connectionstring elements after *.sdf, they are not needed for local db instance. I've updated the code above to reflect this. – gws2 May 04 '15 at 15:39
No problem. One thing to keep in mind: this only applies to Visual Studio 2012 and below. Visual 2013+ have moved to using LocalDB. See here: http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx – gws2 May 04 '15 at 15:48
Ok, i'm using 2012 anyway – Antoine Ravier May 04 '15 at 15:51

C# - Can't connect to my local DB, using SQLServer

2 Answers2