SQL Server Express inserts doesn't work anymore

Question

In my C# application, I do the following:

using (SqlConnection connection = new SqlConnection(connectionstr))
{
    connection.Open();
    SqlCommand com1 = new SqlCommand("insert into Anfangstierbestand(bestand, jahr, loginid) VALUES (" + bestand + ", " + jahr + ", " + loginid + ");", connection);
    SqlCommand com2 = new SqlCommand("select * from Anfangstierbestand;", connection);

    com1.Connection = connection;
    int ferksum = 0;

    com1.ExecuteNonQuery();
    connection.Close();

    connection.Open();
    SqlDataReader read = com2.ExecuteReader();

    while (read.Read())
    {
        ferksum += Convert.ToInt32(read[2]);
    }

    // MessageBox.Show("Fehler beim Erstellen des Tierbestandes", "Fehler"); }
    MessageBox.Show(ferksum.ToString());
}

It's a simple insert to a database. I added com2 to check, if the insert works.

Unfortunately, the the value of com2 tells me, that it works, but when I go to the Server Explorer and press Execute SQL, there are no new values.

I don´t know the reason. The code above is just an example, since a few days, no insert works anymore(before, the same code works).

Does anybody know what the reason can be?

EDIT:

C#:

string connectionstr = "Data Source=.\\SQLEXPRESS;" + "AttachDbFilename=|DataDirectory|\\Datenbank\\FarmersCalc.mdf;" + "Integrated Security=True;" + "User Instance=true;";

EDIT2:

Server Explorer:

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\user\Desktop\Farmer´s Calc\Programmierung\WPF\Finanz_WPF\Finanz_WPF\Datenbank\FarmersCalc.mdf";Integrated Security=True;User Instance=True

EDIT3:

Here are the columns:

id int,
jahr int,
anzahl int,
loginid int

EDIT4:

Is it possible that it´s a problem that I opened my project with expression blend? Normally, I work with VS 2010...

EDIT5:

Even because I can not answer my question(<100 reputations) I write it here:

Shame on me. Some of you were right, it was the wrong database-file. But I´m still wondering, why this happened, because I did not change anything since a few days and before this, it worked!

Thanks to all for helping!

Are you anywhere near the 4GB or 10GB size-limit -- or is this a small, new DB? If this happened to me, I'd first check that the DB/server specified in the conn string was in fact the same DB that I was executing the SQL against to check for new values. Things like that can sometimes happen. — mikey, Feb 08 '12 at 15:20
Too many details missing here. What are the columns data type? Do you insert string data without checking for single quote? — Steve, Feb 08 '12 at 15:25

score 2 · Accepted Answer · answered Feb 08 '12 at 15:39

You're using user instances, why? Isn't it possible that the instance you're connecting to in Server Explorer is not the same as the instance where your app is inserting data? What happens when you select data from within the app, does it reflect your insert(s)? It seems to me based on your connection strings that these are two completely different MDF files - they have the same name but they are in different locations.

score 1 · Answer 2 · answered Feb 08 '12 at 15:17

1

Is the issue implicit transactions? Do you need to issue a commit of the SQL Insert? If com2 tells you that it's there then it may only see it since it's in the context of the current SQL transaction.

You should share your connection string for both Server Explorer and your SqlConnection (connectionstr). Ensure that you setup your Server Explorer connection with User Instance=true;.

answered Feb 08 '12 at 15:17

SliverNinja - MSFT

31,051
11
110
173

as you can see in my EDIT, my connection string includes `User Instance = true;` – user896692 Feb 08 '12 at 15:23
Since your connection strings are different - maybe you don't need `User Instance=true;` for server explorer. The issue is your connection strings. – SliverNinja - MSFT Feb 08 '12 at 15:24

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

1

As a diagnostic,

 connection.Open();
 int n = com1.ExecuteNonQuery();
 // log or show 'n' , the nr of rows affected

Edit, after seeing the connectionstrings:

local db files in |DataDirectory|\ are very prone to be overwritten by the next run or build command.

Make sure you set them to 'copy never' or 'copy if newer' in the VS Solution explorer.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 08 '12 at 15:18

H H

263,252
30
330
514

`n` tells me that one row is affected – user896692 Feb 08 '12 at 15:20

score 1 · Answer 4 · answered Feb 08 '12 at 15:33

1

Perhaps somewhere in the callstack above this code, someone has added a TransactionScope.

This would cause the connections to enroll automatically in the transaction, and the connections could see the data inserted via that not-yet-committed transaction. When the scope is exitted without calling scope.Complete, the transaction gets rolled back and no data is saved.

answered Feb 08 '12 at 15:33

Amy B

108,202
21
135
185

I don't think this is a rolled back transaction. The app shows that the data was inserted. I just don't think he's looking in the right place for the new data when he connects with Server Explorer. – Aaron Bertrand Feb 08 '12 at 15:40

SQL Server Express inserts doesn't work anymore

4 Answers4

Edit, after seeing the connectionstrings: