SQL command INSERT is working but the data not appear in table

Question

I am executing SQL command INSERT like this in my Visual C#.NET using MS VS 2010 Express Edition:

private void btnAdd_Click(object sender, EventArgs e)
{
     SqlConnection con = new SqlConnection(Properties.Settings.Default.loginDBConnectionString);
     con.Open();
     SqlCommand cmd = new SqlCommand("INSERT INTO tblEmp (ID, firstname, lastname, email, position) VALUES ('"+textBox1.Text+"','"+textBox2.Text+"', '"+textBox3.Text+"', '"+textBox4.Text+"', '"+comboBox1.Text+"')", con);
     cmd.ExecuteNonQuery();
     con.Close();
     MessageBox.Show("Data Added!");
}

When executing this, the MessageBox showed up which means the execution was successful. But, when I checked on the table , the data that I am trying to insert before isn't appear at all.

I have one database (loginDB.mdf) with 2 tables inside : - TblLogin - contains username and password for login purpose which executed successfully. - tblEmp - contains employee data, this is the one that I tried to insert data to.

What I don't understand is why the MessageBox appear when in fact none inserted into my tblEmp.

EDIT : ConnectionString to loginDB.mdf :

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\Andreas\documents\visual studio 2010\Projects\LoginApplication\LoginApplication\loginDB.mdf";Integrated Security=True;User Instance=True

The database name is loginDB.mdf instead of logindatabase.mdf as previously written. I changed it to loginDB.mdf just to test it, but still no changes appear.

Is `ID` an identity column which auto generates it's value on insert? Always use parameterized queries instead of string concatenation to build your sql queries. Otherwise ypou are vulnerable to sql injection. — Tim Schmelter, Jul 24 '15 at 07:51
First thing to do apart from anything else: stop building SQL like that. Use parameterized SQL to avoid SQL injection attacks, avoid conversion problems, and make your code more readable. — Jon Skeet, Jul 24 '15 at 07:51
You should **always** use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. Have you ever check as a return value for `ExecuteNonQuery` method? Is it returns `1` exactly? What is your variables? Have you ever tried your query in your database manager first? — Soner Gönül, Jul 24 '15 at 07:51
Can you run a Sql Profiler and see what command is executed exactly? Also: Do the textboxes have values when you debug? — MeanGreen, Jul 24 '15 at 07:52
Also you should use `using` construction for `SqlConnection` (and for other `IDisposable`s). — , Jul 24 '15 at 07:52
Thx guys for all the answer you provided for me. I am still new to programming and did this simple C# app from youtube. How to use parameterized query ? — Andreas Adhiguna Hartono, Jul 24 '15 at 08:40
@MeanGreen - yes, it has value when I debug it by attaching break point on the sqlcommand line. For Nikolay - the database is located in the same folder with the project, is it wrong ? — Andreas Adhiguna Hartono, Jul 24 '15 at 08:53

score 5 · Accepted Answer · edited Sep 02 '17 at 16:55

5

If your c# code executes without any exceptions, it updates the database too. You have probably used AttachDbFilename=|DataDirectory|\yourDB.mdf in your ConnectionString, that means the databse that is updated is located in the subfolder BIN\DEBUG folder of your project. If you want to see the updated data just attach the database located in the bin/debug folder in ssms. for more details read this post. Also make sure your table in server explorer is not already open, if it is already open you must refresh it to show updated data. Please note:as mentioned in the comments you should always use parameterized queries to avoid Sql Injection.

edited Sep 02 '17 at 16:55

Sameer Basil

366
5
11

answered Jul 24 '15 at 07:54

Salah Akbari

39,330
10
79
109

Update : It is indeed like what you said, I am adding the database in **Bin\Debug** version and after testing, it appears. It just only 1 data that appear, like the database only write the latest inserted data and throw off the previous one so how many times I add data, it will only show 1 row of data in the database. How to change it ? – Andreas Adhiguna Hartono Jul 24 '15 at 13:44
if you included your database as a file in your project, try set `Copy To Output Directory` property of DataBase to `Copy if newer` may help you. Please mark my answer as "accepted" if it was helpful for you. Also if you need more help let me know.http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Salah Akbari Jul 24 '15 at 16:03
Yes, finally I am manage to get all the things done and right. Thank you! – Andreas Adhiguna Hartono Jul 25 '15 at 06:45

SQL command INSERT is working but the data not appear in table

1 Answers1

Linked

Related