Question:
I have a problem with the add and update query in SQL. I am trying to update the database with a string which contains one or more apostrophes. SQL reads it like a syntax in query and gives an error message. What I can do? Are there other text delimiters? I have tried “”,[], or {} or double apostrophe without success.
Answer:
Before sending data to your SQL Server, convert all your apostrophes (‘) in data to double apostrophes (“).
So, if a user name is O’Brian, the apostrophe will cause a problem in your SQL Statement:
Update Table Set NameField = 'O'Brian'
Instead, you need to convert it to double apostrophes:
Update Table Set NameField = 'O"Brian'
To convert single apostrophes to double, use the Replace function.
strName = Replace(strName, " '" , """ )