Updating Table from C# Winforms Not working

Question

I am getting this error message:

--------------------------- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(title) VALUES (hi)' at line 1

--------------------------- OK

And yeah, I know it's something to do with the syntax. The problem is, I have tried many variations of it and still can't get it to work, so then I went to 5 sites I found on google with tutorials, and still the same problem. Here's my code below:

Note: The values inside the Text Property of the text box controls listed below is just plain text, all letters and numbers.

try
            {
                label1.Text = "Trying to save. Wait.";
                conn.Open();
                string sql = "UPDATE " + pagelist.Text + "SET " + itemlist.Text + "=" + sitetext.Text;
                MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception en)
            {
                MessageBox.Show(en.Message);
            }

About the table:

table has 3 columns "heading, title and text". all are varchars and the length of each one is 255

Can someone please help me figure out what's wrong about this? Any help at all is appreciated.

Thank you

Mitch Wheat · Answer 1 · 2011-02-05T16:28:55.520

3

You need to quote varchar values:

string sql = 
 "UPDATE " + pagelist.Text + " SET " + itemlist.Text + " = '" + sitetext.Text + "'";

Better still, use a parameterised query to avoid SQL Injection attacks.

edited Feb 05 '11 at 16:28

answered Feb 05 '11 at 16:22

Mitch Wheat

295,962
43
465
541

Sorry, but that code above gives me this error: **--------------------------- --------------------------- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'hi'' at line 1 --------------------------- OK --------------------------- ** – Feb 05 '11 at 16:27
table has 3 columns "heading, title and text". all are varchars and the length of each one is 255. – Feb 05 '11 at 16:31
Thank you so much @Mitch Weat! It worked after I added the space before SET after you posted the edit comment. Thanks :) – Feb 05 '11 at 16:33
@Lucifer: could I suggest you perhaps accept @gaearon answer, as I think they spotted the missing space first. – Mitch Wheat Feb 05 '11 at 16:34
Really? I didn't realise. Sure, no problemo :) – Feb 05 '11 at 16:36

score 3 · Answer 2 · edited May 23 '17 at 12:34

In addition to Mitch's suggestion, I'd also wrap the conn and cmd objects in using blocks:

using ( var conn = new Connection( connString ) )
{
  conn.Open();
  var sql = "my sql";
  using( var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn) )
  {
    cmd.ExecuteNonQuery();
  }
}

This will make sure that the resources are released appropriately, connections closed, etc.

Here's a SO answer relating to using parameterized queries: Parameterized Query for MySQL with C#

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

I think you forgot a space before SET.
If it is text and not number field, you must also surround value with single quotes.
But please throw this code away and write a different one that uses parameters.

edited May 23 '17 at 10:33

Community

1
1

answered Feb 05 '11 at 16:26

Dan Abramov

264,556
84
409
511

score 2 · Answer 4 · answered Feb 05 '11 at 16:23

String literals need to surrounded in quotes and correctly escaped when used directly in a query, but that would be the wrong option here; because you can't trust the input, you should use a parameter in the assignment. Depending on the provider this may mean ... = ? or ... = @argName etc - and adding the value into the .Parameters collection on your command object.

Using a parameter here will save you from SQL injection - a ridiculously easy way to kill or abuse a badly written app.

score 0 · Answer 5 · answered Feb 05 '11 at 16:23

0

please check the syntax

Update syntax:

Update tablename set feildname = value where condition ;

are you using this correctly?

if you are passing a string pass it in single quotes

beware of sql injection!

answered Feb 05 '11 at 16:23

Nighil

4,099
7
30
56

I do not require a where condition. There are a total of 3 columns, and I only update 1 column. There is no condition. – Feb 05 '11 at 16:26

Updating Table from C# Winforms Not working

--------------------------- OK

5 Answers5