c# - SQL update statement

Question

I need help with my SQL connection. I have this code:

                        SqlConnection myConnection2 = new SqlConnection("server=c1212\\SQLEXPRESS;" +
         "Trusted_Connection=yes;" +
         "database=SPZ; " +
         "connection timeout=30");
        try
        {
            myConnection2.Open();
            SqlCommand myCommand2 = new SqlCommand();

              myCommand2.CommandText = "UPDATE SPZ set Datum='" + textBox1.Text+"' , ČasP='"+textBox5.Text+"', ČasO='"+textBox6.Text+"', SPZ='"+textBox2.Text+"', Příjmení='"+textBox3.Text+"', Firma='"+textBox4.Text+"', Poznámka='"+textBox7.Text+"', Kontrola='"+textBox8.Text+ "' 
                where Datum='" + textBox9.Text + "' and ČasP='" + textBox13.Text + "' and ČasO='" + textBox14.Text + "' and SPZ='" + textBox10.Text + "' and Příjmení='" + textBox11.Text + "' and Firma='" + textBox12.Text + "' and Poznámka='" + textBox15.Text + "' and Kontrola='" + textBox16.Text + "'";


            myCommand2.ExecuteNonQuery();
          //  myConnection.Close();
        }
        catch (SqlException ex)
        {
            MessageBox.Show("Připojení do databáze selhalo! " + ex.Message);
        }

I can't find what is wrong, can someone help me?

Wrong is that you're not using sql parameters instead of string concatenation to avoid sql injection. — Tim Schmelter, Jan 29 '16 at 14:17
How can _we_ know if _you_ don't tell us? Did you debug your code and check your command? Did you check your connection string? Your get any exception or error message? Are your all columns character typed? 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. Also use [`using` statement](https://msdn.microsoft.com/en-us/library/yh598w02.aspx) to dispose your connection and command automatically. — Soner Gönül, Jan 29 '16 at 14:17
on top of that, there is no database tag and i'm not sure that your database tolerates all the funny column names — A ツ, Jan 29 '16 at 14:19
I would look into making a stored procedure and then call it from your front-end code. [Check out this answer](http://stackoverflow.com/a/7542564/2113548)! — Simple Sandman, Jan 29 '16 at 14:20
When I hit update button, I got error: Additional information: ExecuteNonQuery: Property Connection wasn't initialized. — Avrack, Jan 29 '16 at 14:25
You should avoid at any price using of direct string concatenation. If exception is raised in execute command, the most probably is data type cast error. For example, textBox1.Text is assumed to be a date input based on the field name. TextBox is a free input and it can have some character which prevents casting directly to date. — Shukri Gashi, Jan 29 '16 at 14:31
Voting to close as Typo as you know you were not connected. As you commented under the Answer. Then you expanded the question. — Drew, Jan 30 '16 at 19:04

score 0 · Accepted Answer · answered Jan 29 '16 at 14:27

0

You must set the Connection on your command object.

myCommand2.Connection = myConnection2;

answered Jan 29 '16 at 14:27

Andy Wiesendanger

643
9
17

Ok, its connected now, but i got this: The data types ntext and varchar are incompatible in the equel to operator – Avrack Jan 29 '16 at 14:31
Debug, and after you set CommandText property, copy that value into management studio (or whatever you use) and see what the issue is. As has been mentioned, your approach is not very good. Parameters would be better. – Andy Wiesendanger Jan 29 '16 at 14:33

c# - SQL update statement

1 Answers1