Data not saving permanently to SQL table

Question

I am using C# for a WPF application in Visual Studio Express 2012. I followed the tutorial found here. I created a testDb.mdf local Service-based database. I open the application, enter text, hit add, and the data adds to the db. I only know this because I have the one field setup as a primary key and unique. If I try to add the same thing again I get an error saying it already exists.

When I exit my application nothing shows in the database. The data I entered is gone. Why is the data not permanent?

Here is the code I'm using for my button click:

private void Add_Click(object sender, RoutedEventArgs e)
{
    SqlConnection cn = new SqlConnection(global::testdb.Properties.Settings.Default.testDBConnectionString);
    try 
    { 
        string sql = "INSERT INTO Test (TestInsert) Values('" + txtName.Text + "')";
        SqlCommand cmd = new SqlCommand(sql, cn);
        cn.Open();
        cmd.ExecuteNonQuery();

        MessageBox.Show("Added new record", "Message", MessageBoxButton.OK);
    }
    catch (Exception ex) 
    {
        MessageBox.Show(ex.Message,"Error", MessageBoxButton.OK);
    }
    finally
    {
        cn.Close();
    }
}

Connection String:

<connectionStrings>
    <add name="testdb.Properties.Settings.testDBConnectionString"
        connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\testDB.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Isn't it normal? You try to add another primary key with the same value in your table if I clearly understand. — Soner Gönül, May 06 '14 at 13:44
You can not add the same thing , same value in PK field column — Aj_sari, May 06 '14 at 13:45
I'm sorry I didn't make it clear. I add a value from a text box with the button. Right now I have no way of making sure it's there so I just add the same thing again and it gives me the primary key error. so I believe that first value added successfully. I can add multiple items as long as they are different, which makes sense. — mkmitchell, May 06 '14 at 13:49
I only have one database with one table inside it. In visual studio I right click the table, refresh, click preview data. I only see Null — mkmitchell, May 06 '14 at 13:50

Steve · Accepted Answer · 2020-02-16T20:38:44.420

It is a common scenario. You have a connection string that uses the substitution string |DataDirectory|. In a desktop application this directory is usually the same directory where your program runs. Inside Visual Studio, your program runs in the BIN\DEBUG (or x86 variant) directory. Thus the Visual Studio copies your MDF file from the project directory to the BIN\DEBUG folder. You add records to this copy, not to the one in the project folder. However, the Visual Studio Server Explorer window has a connection that points to the Project Folder database that, of course, remains empty.

You could add another connection to the Server Explorer pointing to the folder BIN\DEBUG and check that your database has been updated or not.

To complicate the matter, there is the property Copy to the Output Directory associated with the MDF file. If this property is set to Copy Always everytime you start a new session within Visual Studio, the file is copied again from the project folder to the output directory (BIN\DEBUG) overwriting the copy already there with a new empty one. So the first run succeds, the second one fails.
The symptoms that you observed are a clear sign of this situation.

Simply change the property Copy to the Output directory to Copy if newer, the code works well.
(Peraphs it is too early, but remember to change your query to a parameterized query. As is, you could break your code simply inserting a single quote in the txtName textbox like O'Malley, not to mention the Sql Injection hack)

Yep, you nailed it. Now I just have to figure out how to make this db embedded so that it always uses the same thing. Thanks for the help! — mkmitchell, May 06 '14 at 14:08

score 2 · Answer 2 · answered May 06 '14 at 13:45

2

I think your issue is not with the insert code. It's with the way you're checking the database/table yourself. Particularly because you say you're getting primary key errors so something's being added to the table.

Are you sure you're refreshing your view of the table properly? Are you sure you're checking the right table in the right database?

answered May 06 '14 at 13:45

inkieweb

23
4

I start the program, enter the value 'test' and hit enter. It adds. I try to enter the value 'test' again and it gives me the error. I close the program and reopen it. I try to enter the value 'test' again and it adds fine. – mkmitchell May 06 '14 at 13:57

apollosoftware.org · Answer 3 · 2014-05-06T13:56:39.187

Make sure your initial catalog is set in your connection string, and be sure you are pointing to the right server/ instance of SQL. You may have multiple instances of SQL Server on the same server or whatever DB Server you are using. Also ensure you're going to the right table, of course.

using (SqlConnection cn = new SqlConnection(@"Persist Security Info=False;Integrated Security=true;Initial Catalog=testDB;server=(local)"))
{           
            string sql = "INSERT INTO Test (TestInsert) Values('" + txtName.Text + "')";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cn.Open();
            cmd.ExecuteNonQuery();
            MessageBox.Show("Added new record", "Message", MessageBoxButton.OK);

}

Check in SQL Server Studio that the database exists. I don't recommend using the filename mdf in your code directly using AttachDbFilename. Use the initial catalog.

score 0 · Answer 4 · answered Mar 03 '17 at 19:14

0

Well, i found the solution. I solved it by installing SSDT(SQL server data tools) for visual studio. Install it according to your visual studio version. Go to following link to download the SSDT for visual studio. https://msdn.microsoft.com/en-us/mt186501

answered Mar 03 '17 at 19:14

Tanver Hasan

1,687
13
12

SSDT is not required to interact with an SQL database from a WPF application. That is just if you want to be able to use the tools within Visual Studio to interact with the database. – dub stylee Mar 03 '17 at 19:24

score 0 · Answer 5 · answered Oct 05 '18 at 16:49

In the following of the Steve's answer you can get your database full path in properties window and substitute it with |DataDirectory| in string connection.

your code will be like

conn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=G:\MhD\c#\phonebook\phonebook\phonebook\tellbook.mdf;Integrated Security=True";

Data not saving permanently to SQL table

5 Answers5

Linked

Related