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.

Sandwich

Please show your ConnectionString too. – Oddity

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. – Crenelate

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. – Vomitory

You should always use parameterized queries. This kind of string concatenations are open for 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? – Mindszenty

Can you run a Sql Profiler and see what command is executed exactly? Also: Do the textboxes have values when you debug? – Fawnfawna

Also you should use using construction for SqlConnection (and for other IDisposables). – Huh

Most probably you are looking for data in a wrong database. – Transponder

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 ? – Sandwich

@Fawnfawna - 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 ? – Sandwich

Answer 1

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.

Oddity

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 ? – Sandwich

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.meta.stackexchange.com/questions/5234/… – Oddity

Yes, finally I am manage to get all the things done and right. Thank you! – Sandwich