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
– TomTom, Commented May 27, 2012 at 4:28

Community · Accepted Answer · 2017-05-23 12:09:51Z

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, 2017 at 12:09

CommunityBot

11 silver badge

answered May 26, 2012 at 20:01

Jim D'Angelo

3,9523 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dsynkd Over a year ago

Hi. I knew about the SQL Injection but I thought: How can somebody inject SQL statements into a desktop application?

dsynkd Over a year ago

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.

Michael Buen Over a year ago

@V0R73X "Hi. I knew about the SQL Injection but I thought: How can somebody inject SQL statements into a desktop application?" Crackers are everywhere

marc_s · Accepted Answer · 2012-05-26 20:12:47Z

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, 2012 at 20:12

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

5 Comments

dsynkd Over a year ago

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.

marc_s Over a year ago

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

dsynkd Over a year ago

Any other methods for registering the database without using the management studio? Maybe some T-SQL code...

marc_s Over a year ago

@V0R73X: if you insist on not using the Mgmt Studio, you could always fall back on SQLCMD.exe on the command line ...

TomTom Over a year ago

@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.

Saroop Trivedi · Accepted Answer · 2012-05-26 20:11:46Z

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.

TEST · Accepted Answer · 2012-05-27 03:00:52Z

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.. ;)

Collectives™ on Stack Overflow

ADO.NET objects in C# not working properly

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related