Error with apostrophe in with mySQL connector
Posted by: marco zecchi
Date: October 04, 2017 03:50AM

I'm having problems, with MySQL, with the following code:

Imports MySql.Data.MySqlClient 'LAst version ADO.Net driver for MySQL
Dim commandSQL As MySqlCommand
commandSQL = connMySQL.CreateCommand
commandSQL.Connection = connMySQL

commandSQL.CommandText = "UPDATE t_tesordacq SET COD_FOR=@COD_FOR WHERE YEAR_DOC = 2017 And NUM_DOC = 123"
With commandSQL.Parameters
.AddWithValue("@COD_FOR", txtCodFor.Text)
End With

When I insert a text in the txtCodFor field, which contains an apostrophe, the code goes wrong 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 '\'test' WHERE YEAR_DOC = 2017 And NUM_DOC = 123, ).

I also tried replacing the text with double apostrophe: txtCodFor.Text.Replace("'", "''") but there was nothing to do.
Also, if I insert a backslash, it is duplicated!

I've seen that if I remove the "NO_BACKSLASH_ESCAPES" option, from sql_mode in my.ini, everything works correctly.
Unfortunately I can not permanently remove this option for compatibility with a written application in VB6 (ODBC-connected)

Thanks for your help

Options: ReplyQuote

Written By
Error with apostrophe in with mySQL connector
October 04, 2017 03:50AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.