C# insert in table doesn't work but select works perfect

Question

I work at a Windows Form C# application but i have some troubles. When I want to select from table everything works perfect ,but when i want to insert data in my table have not error and my application says "The data was inserted in database", but my table is empty.

This is my connection :

SqlConnection conexiune = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Baza-de-date.mdf;Integrated Security=True");

This is my code:

 try
     {
         SqlCommand insert = new SqlCommand("INSERT INTO [dbo].[Tabel] ([ID], [Nume], [Prenume], [Varsta]) VALUES (@id, @nume,@prenume,@varsta)", conexiune);
         insert.Parameters.AddWithValue("@id",textBox1.Text);
         insert.Parameters.AddWithValue("@nume", textBox2.Text);
         insert.Parameters.AddWithValue("@prenume", textBox3.Text);
         insert.Parameters.AddWithValue("@varsta", textBox4.Text);
         conexiune.Open();
        insert.ExecuteReader();
         MessageBox.Show("Datele au fost introduse cu succes in baza de date!", "Succes!", MessageBoxButtons.OK, MessageBoxIcon.Information);

     }
     catch
     {
         MessageBox.Show("A avut loc o eroare! Te rog sa incerci din nou", "Eroare!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
     }
     finally
     {
         conexiune.Close();
         this.Close();
     }

I'm using Visual studio 2013 and Microsoft SQL Server version 11.00.3000.

Sorry for my English

Let me know if this is your case: http://stackoverflow.com/questions/17147249/why-dont-changes-to-database-save/17147460#17147460 — Steve, Jul 27 '14 at 08:35
I think that, while the correct method to call is ExecuteNonQuery, also the ExecuteReader executes the command that is passed. The difference is in the infrastructure build by the methods before and after the execution. The problem is the usual "I don't know what my tools are doing" I.E. DataDirectory - Copy To Destination Directory — Steve, Jul 27 '14 at 08:42
I solved the problem. I replaced my connection adresse with this connection adresse and now everything works perfect. Data Source= (LocalDB)\v11.0;AttachDbFilename=C:\Users\Alex\Desktop\Aplicatie-baza-de-date\Aplicatie-baza-de-date\Aplicatie-baza-de-date\Baza-de-date.mdf;Integrated Security=True — Edward Kenway, Jul 27 '14 at 11:06

Jon Skeet · Answer 1 · 2014-07-27T11:19:46.553

5

This is a problem, at least:

insert.ExecuteReader();

That's meant for queries, because you're reading data. (As noted by Steve, it would still work - but it's not

Call ExecuteNonQuery instead:

insert.ExecuteNonQuery();

Additionally, I'd advise using using statements instead of manually closing resources. (You should close the SqlCommand too.)

However, the real problem was found by Steve in comments - you were copying a fresh copy of the database on each run. So I'd expect you to see the results of the insert within one execution of the application, but they'd be lost next time you started the app.

edited Jul 27 '14 at 11:19

answered Jul 27 '14 at 08:35

Jon Skeet

1,421,763
867
9,128
9,194

Ok , i replaced "insert.ExecuteReader();" with "insert.ExecuteNonQuery();" ,but i have same problem. thanks for answer! – Edward Kenway Jul 27 '14 at 08:39
1

@Edward: check the return value of the method call. That will tell you how many rows were inserted. Are you using transactions anywhere? If so, maybe the problem is that you aren't committing it. – Jon Skeet Jul 27 '14 at 08:43
@ Jon , when i'm trying to check return value my application show Catch error. – Edward Kenway Jul 27 '14 at 08:51
As I have said in my comments above the ExecuteReader and ExecuteNonQuery both execute the command. The problem, in my thinking, lies in the DataDirectory and in which database the record has been inserted. – Steve Jul 27 '14 at 09:43
Ok. Thanks , Jon , I will rebuild the application – Edward Kenway Jul 27 '14 at 09:53

score 0 · Answer 2 · answered Jul 27 '14 at 09:34

Check your query by running the same in sql server maybe the issue is you are writing the wrong table or column names somewhere And i guess as you are using ID column you must have kept is as identity If this is the case then you cannot insert a value t that column it'll take the value automatically..

Return your generated query in a label or something and check it in sql server if its working properly or not..

C# insert in table doesn't work but select works perfect

2 Answers2