apostraphe SQLexception

Carmen

I'm inserting and retreiving values from and SQL table to an ASP.net web application.
For inserting values i used .Replace("'", "''") to replace apostraphe's.
As a test I inserted strawberries' as a value and this worked. But when I try to retrieve this record using an SqlDataReader, i get a SQL exception message as follows:
Unclosed quotation mark after the character string 'strawberries''. Incorrect syntax near 'strawberries''.
The sql command I am using in the command.ExecuteReader() appears as follows:
"select * from table where fieldname = 'strawberries''"
How do I correct this error?

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Eamonn17y ago
Did you test this SQL in a query editor?
"select * from table where fieldname = 'strawberries''"

is missing an escaped quote, it should be:
"select * from table where fieldname = 'strawberries'''"
That's 3 single quotes before the closing double quote, 2 to escape the ' at the end of strawberries, and then 1 more to close the SQL char literal