ADO.NET objects in C# not working properly

Question

SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\CustomersDB.mdf;Integrated Security=True;User Instance=True");

SqlCommand cmd = new SqlCommand("INSERT INTO Customers (ID, Date, GUIA, SName, SAddress, SCity, SState, SZipCode, SPhone, SEmail, RName, RAddress, RCity, RState, RZipCode, RPhone, REmail) VALUES (1,'"+textBox1.Text + "','" + textBox2.Text+"','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "','" + textBox9.Text + "','" + textBox16.Text + "','" + textBox15.Text + "','" + textBox14.Text + "','" + textBox13.Text + "','" + textBox12.Text + "','" + textBox11.Text + "','" + textBox10.Text +"')" , con);
cmd.CommandType = System.Data.CommandType.Text;

con.Open();
cmd.ExecuteNonQuery();
con.Close();

MessageBox.Show("Data added successfully!");

As you can see, I'm trying to add some data to the database, created inside a C# Windows Forms application.

However, after executing the code, I receive no error, but when I look at the table data, nothing has changed.

In other words, no data is being added, even though the code is executed correctly.

What's the flaw here? Any help is appreciated.

And why you think you can insert into an Identity column? Normally that is turned off and it should not be turned on. — TomTom, May 27 '12 at 04:28

score 1 · Answer 1 · edited May 23 '17 at 12:09

1

Firstly, I would like to point out that you have one giant SQL-injection sitting there. Secondly, take a look at Rows not being updated to see if it is the same issue you are facing.

edited May 23 '17 at 12:09

Community

1
1

answered May 26 '12 at 20:01

Jim D'Angelo

3,952
3
25
39

Hi. I knew about the SQL Injection but I thought: How can somebody inject SQL statements into a desktop application? – dsynkd May 26 '12 at 20:48
But it did not work! I changed the property and still the same thing. I try viewing the data by right clicking on the table in the Server Explorer tab and choosing show table data. Also, the gridview I'm using is linked to the same Database and it doesn't show new data being added. – dsynkd May 26 '12 at 21:01
1

@V0R73X "Hi. I knew about the SQL Injection but I thought: How can somebody inject SQL statements into a desktop application?" Crackers are everywhere – Michael Buen May 27 '12 at 01:34

score 1 · Answer 2 · answered May 26 '12 at 20:12

1

The main flaw is the whole User Instance and AttachDbFileName= approach. Visual Studio will be copying around the .mdf file and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

The real solution in my opinion would be to

install SQL Server Express (and you've already done that anyway)
install SQL Server Management Studio Express
create your database in SSMS Express, give it a logical name (e.g. CustomersDB)
connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:
```
Data Source=.\\SQLEXPRESS;Database=CustomersDB;Integrated Security=True
```
and everything else is exactly the same as before...

answered May 26 '12 at 20:12

marc_s

732,580
175
1,330
1,459

I'm REALLY not comfortable with the management studio, it doesn't get installed after installing SQL framework. They have to be installed altogether or Management Studio can't be added later. Also there are several versions of the management studio and I don't know which one is suitable for me. Last time I spent hours finding the right version and in the end I figured I couldn't install it. – dsynkd May 26 '12 at 21:01
Well, I just strongly believe this "attach-the-DB-file" approach is fundamentally flawed in a server-based product like SQL Server Express. You're fighting the system - instead of embracing it and making optimal use of it! If you don't need the power of the RDBMS server - then you should be looking at things like SQLite or SQL Server Compact Edition which is a file-based, basically single-user, local-machine kind of "database" system – marc_s May 26 '12 at 21:04
Any other methods for registering the database without using the management studio? Maybe some T-SQL code... – dsynkd May 26 '12 at 21:17
@V0R73X: if you insist on not using the Mgmt Studio, you could always fall back on [SQLCMD.exe](http://msdn.microsoft.com/en-us/library/ms170207%28v=sql.105%29.aspx) on the command line ... – marc_s May 26 '12 at 21:18
2

@V0R73X - what about you start getting comfortable with the tools you are supposed to use? LIke a t least a touch of semi professionalism. Whining "I am REALLY not comfortable with" (insert random professional system tool here) is not a good approach. – TomTom May 27 '12 at 04:30

score 0 · Answer 3 · answered May 26 '12 at 20:11

1 Your query will create the SQL Injection, try to use SP or LINQ for more secure execution.

[2] First of all try to execute your long query return string value with your sql server database table because here you not show your table structure so that any single quote will not execute the proper query.

string sqlstr = "INSERT INTO Customers (ID, Date, GUIA, SName, SAddress, SCity, SState, SZipCode, SPhone, SEmail, RName, RAddress, RCity, RState, RZipCode, RPhone, REmail) VALUES (1,'"+textBox1.Text + "','" + textBox2.Text+"','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "','" + textBox9.Text + "','" + textBox16.Text + "','" + textBox15.Text + "','" + textBox14.Text + "','" + textBox13.Text + "','" + textBox12.Text + "','" + textBox11.Text + "','" + textBox10.Text +"')"

[3] Last point better naming is important for coding.

score 0 · Answer 4 · answered May 27 '12 at 03:00

cn.ConnectionString = @"Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\CustomersDB.mdf;Integrated Security=True;User Instance=True";
            cn.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = cn;
            com.CommandType = CommandType.Text;

            com.CommandText = "INSERT INTO Customers (ID, Date, GUIA, SName, SAddress,               SCity, SState, SZipCode, SPhone, SEmail, RName, RAddress, RCity, RState, RZipCode, RPhone, REmail) VALUES (1,'"+textBox1.Text + "','" + textBox2.Text+"','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "','" + textBox9.Text + "','" + textBox16.Text + "','" + textBox15.Text + "','" + textBox14.Text + "','" + textBox13.Text + "','" + textBox12.Text + "','" + textBox11.Text + "','" + textBox10.Text +"')" ;
          com.ExecuteNonQuery();
          MessageBox.Show("Saving is done!");

Try this Code and See wheather its working or not i think this should work.. ;)

ADO.NET objects in C# not working properly

4 Answers4