I am working on a C# windows form which is connected to MySQL and updates strings within the form. I have everything working properly except for a small issue.
Say you want to update the "notes" field to read "The dog's bone", that apostrophe is causing the SQL query to end and cause an error. How can I get around this please?
UPDATE `database`
SET `notes` = 'The dog's bone'
WHERE `Pet` = 'Dog';
Thanks!
You can escape ' character in MySQL with doubling it like ''.
Other than that, if you use parameterized queries, you will not need this at all. Just pass your The dog's bone string directly to your parameterized query and you will be fine.
Also I strongly suspect you try to use UPDATE instead of SELECT statement. In MySQL, SELECT syntax doesn't have any SET part.
And using a reserved keyword as a column name is a bad idea. As a best practice, change your database column name to non-reserved word.
using(var con = new MySqlConnection(conString))
using(var cmd = con.CreateCommand())
{
cmd.CommandText = @"UPDATE `database` SET notes = @notes
WHERE Pet = @pet";
cmd.Parameters.AddWithValue("@notes", "The dog's bone");
cmd.Parameters.AddWithValue("@pet", "Dog");
con.Open();
cmd.ExecuteNonQuery();
}
I used AddWithValue method as an example in my code since I didn't know your column types but you don't use it. This method may generate unexpected and surprising results sometimes. Use Add method overloads to specify your parameter type and it's size.
Escape it with another single quote ':
SELECT `database`
SET `notes` = 'The dog''s bone'
WHERE `Pet` = 'Dog';
