0

I try to add my value in local database (created by Add -> Service-based database).

My code:

//string connectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\VS_MyProjects\DBAppliaction\DBAppliaction\Database1.mdf;Integrated Security=True";
            string connectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;";

            string query = "INSERT INTO [dbo].[Table] (Test) " +
                   "VALUES (@Test) ";


            // create connection and command
            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = new SqlCommand(query, cn))
            {
                // define parameters and their values
                cmd.Parameters.Add("@Test", SqlDbType.NChar, 10).Value = "test";

                // open connection, execute INSERT, close connection
                cn.Open();
                cmd.ExecuteNonQuery();
                cn.Close();
            }

When I use full path to my db it's all okay. In "Show Data Table" appear my added value. But when I use in path |DataDirectory| and press button "Update" in "Show Data Table" for verify added my value or no I have seen the error: "This database cannot be imported. It is either an unsupported SQL Server verison or an unsopported database compatibility". Why?

Maxim
  • 1
  • 1
  • 1
  • Check if you have this situation http://stackoverflow.com/questions/17147249/why-dont-changes-to-database-save/17147460#17147460 – Steve Nov 16 '14 at 18:05
  • @Steve Copy if newer - the result is same Do not copy - I get an exception: `Additional information: An attempt to attach an auto-named database for file D:\VS_MyProjects\DBAppliaction\DBAppliaction\bin\Debug\Database1.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.` – Maxim Nov 16 '14 at 18:20

3 Answers3

0

In your connection string the Initial Catalog key/value pair is missing. This means that when the database file MDF is attached by LocalDB its full path name is used and in subsequent runs of your program the attach fails. Instead if you provide an Initial Catalog value the subsequent runs simply use that catalog

So change your connection string to

string connectionString = @"Data Source=(LocalDB)\v11.0;
                            AttachDbFilename=|DataDirectory|\Database1.mdf;
                            Initial Catalog=LogicalNameOfYourDatabase
                            Integrated Security=True;";

Of course the first problem (data apparently not saved) is caused by the behavior of DataDirectory substitution string coupled with the way in which Visual Studio handles the MDF files attached to your project (as explained in another answer here)

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • **LogicalNameOfYourDatabase** - what is it? What should I write here? About Properties->Copy To Output Directory: Copy if newer - the result is same Do not copy - I get an exception: `Additional information: An attempt to attach an auto-named database for file D:\VS_MyProjects\DBAppliaction\DBAppliaction\bin\Debug\Database1.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.` – Maxim Nov 16 '14 at 18:49
  • It is the logical name of the database. You could use any name that makes sense for the content of the database: _Customers, Books, School_ whatever. Adding a logical name helps the code to find and reuse a previous attached database. The `Copy to output directory ` values have no part in your current problem. – Steve Nov 16 '14 at 19:00
  • I try this: `string connectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database1.mdf;Initial Catalog=Test;Integrated Security=True;";` But the error is still there (( – Maxim Nov 16 '14 at 19:07
0

I ran into a similar problem with VS2012 and SQL2014. Essentially, your SQL tools in VS are not current with the SQL server. The fix was to install SQL2014 management tools locally on my development PC.

Webmentorman
  • 41
  • 4
0

Try this:

string connectionString = @"Data Source=(LocalDB)\v11.0;
                        AttachDbFilename="+System.IO.Path.GetFullPath("Database1.mdf")+";
                        Initial Catalog=LogicalNameOfYourDatabase
                        Integrated Security=True;";
Dimitrie Vatra
  • 3
  • 2