Replacing ' in a string
-
Hi, I have a problem with strings containing ' (that is the character '). The strings need to be saved to a MySQL database, but the MySQLCommand doesn't accept the ' character. It only accept it as \' Problem is I can't make C# replace it properly (2nd line in the code):
string myString = "The world's largest program";
myString = myString.Replace("'", "\'");string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());Any ideas? Thanks a lot.
-
Hi, I have a problem with strings containing ' (that is the character '). The strings need to be saved to a MySQL database, but the MySQLCommand doesn't accept the ' character. It only accept it as \' Problem is I can't make C# replace it properly (2nd line in the code):
string myString = "The world's largest program";
myString = myString.Replace("'", "\'");string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());Any ideas? Thanks a lot.
You can do it in the initial string thus:
string myString = "The world\'s largest program";
or in your replace call thus:
myString = myString.Replace("'", "\\'");
In case 1 you need to escape the single quote with a backslash, and in case 2 you need to escape the backslash.
I must get a clever new signature for 2011.
-
Hi, I have a problem with strings containing ' (that is the character '). The strings need to be saved to a MySQL database, but the MySQLCommand doesn't accept the ' character. It only accept it as \' Problem is I can't make C# replace it properly (2nd line in the code):
string myString = "The world's largest program";
myString = myString.Replace("'", "\'");string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());Any ideas? Thanks a lot.
Use parameters instead.
-
You can do it in the initial string thus:
string myString = "The world\'s largest program";
or in your replace call thus:
myString = myString.Replace("'", "\\'");
In case 1 you need to escape the single quote with a backslash, and in case 2 you need to escape the backslash.
I must get a clever new signature for 2011.
It's case 2 we need to go for (the myString is just an example that I created for my question, in reality it's a much more complex string) But case 2 doesn't seem to work:
string myString = "The world's largest program";
myString = myString.Replace("'", "\\'");string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());Gives us the SQL string:
UPDATE myTable SET myColumn = 'The world\\'s largest program'
It has to end up like this to work (I have checked it by typing it in the program SQLyog (which give direct SQL access to a MySQL database):
UPDATE myTable SET myColumn = 'The world\'s largest program'
-
Hi, I have a problem with strings containing ' (that is the character '). The strings need to be saved to a MySQL database, but the MySQLCommand doesn't accept the ' character. It only accept it as \' Problem is I can't make C# replace it properly (2nd line in the code):
string myString = "The world's largest program";
myString = myString.Replace("'", "\'");string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());Any ideas? Thanks a lot.
Once you got the single quote in the database, it does not make sense to replace it by some escape sequence; escape sequences are used in a programming language (such as SQL), not in the data itself. So use \' in the queries when you have to. And it is always better to use SqlParameter, you won't need escape sequences then at all. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Hi, I have a problem with strings containing ' (that is the character '). The strings need to be saved to a MySQL database, but the MySQLCommand doesn't accept the ' character. It only accept it as \' Problem is I can't make C# replace it properly (2nd line in the code):
string myString = "The world's largest program";
myString = myString.Replace("'", "\'");string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());Any ideas? Thanks a lot.