CODEWITHSUNDEEP
sqlsql-serversql-update
MononcLabroue
MononcLabroue

Reputation: 1

SQL Server update in C#

I try to UPDATE data in my SQL Server database and I get this error:

System.Data.SqlClient.SqlException
Incorrect syntax near 'de'
Unclosed quotation mark after the character string ')'

    private void BtEnrMod_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=.\\BD4X4;Initial Catalog=BD4X4;Integrated Security=True");
        con.Open();

        SqlCommand cmd = new SqlCommand("UPDATE Service SET Type = " + TxBxService.Text + ", Prix = " + TxBxPrix.Text + "WHERE Code = " + LbCodeAff.Text + "')", con);

        int i = cmd.ExecuteNonQuery();

        if (i != 0)
        {
            MessageBox.Show("Service Modifié");
        }
        else
        {
            MessageBox.Show("Erreur");
        }

        this.Close();
        con.Close();
    }

Upvotes: 0

Views: 75

Answers (1)

Caius Jard
Caius Jard

Reputation: 74595

Replace the one liner that declares your command with this code block:

SqlCommand cmd = new SqlCommand("UPDATE Service SET Type = @t, Prix = @p WHERE Code = @c", con);
cmd.Parameters.AddWithValue("@t", TxBxService.Text);
cmd.Parameters.AddWithValue("@p", TxBxPrix.Text);
cmd.Parameters.AddWithValue("@t", LbCodeAff.Text);

Always avoid writing an sql where you string concatenate in a value provided by the user in a text box; it's the number one security horror you can make with sql. Always use parameters to put values in, like you see here. For more info on this SQL injection hacking, see http://bobby-tables.com

If you ever fin yourself in a situation where you think you have to concatenate to make an sql, don't concatenate a value in; concatenate a parameter in and add the value into the parameters collection. Here's a hypothetical example:

var cmd = new SqlCommand("","connstr");
strSql = "SELECT * FROM table WHERE col IN (";
string[] vals = new[]{ "a", "b", "c" };
for(int x = 0; x<vals.Length; x++){
  strSql += ("@p"+x+",");
  cmd.Parameters.AddWithValue("@p"+x, vals[x]);
}
cmd.CommandText = strSql + ")";

This uses concatenation to make an sql of SELECT * FROM table WHERE col IN (@p0, @p1, @p2) and a nicely populated parameters collection

When you're done grokking that, read the link Larnu posted in the comments. There are good reasons to avoid using AddWithValue in various scenarios but it will always be preferable to concatenation of values. Never ditch the use of parameters "because I read a blog one time about how AddWithValue is bad" - form parameters using the new parameter constructor, or use AddWithValue shortcut, but never concat values

Or better still than all of this, use an ORM like Entity Framework, nHibernate or Dapper and leave most of this boring boilerplate low level SQL drudgery behind. These libraries do most of this wrangling for you; EF and nH even write th sql too, dapper you write it yourself but it takes care of everything else

Using a good ORM is like the difference between writing creating a UI manually line by line of position, font, anchor, event code for every button, label and text box versus using the windows forms designer; a world apart and there's no sense in taking hours to create manually what software can do more comprehensively, faster and safer for you in seconds

Upvotes: 3

Related Questions