How to "escape" a single quote in SQL Server CE select statement

  • Thread starter Thread starter Malcolm McGrath
  • Start date Start date
M

Malcolm McGrath

I was wondering how to escape a single quote in an SQL Server CE
SELECT statement.

eg (obviously does not work):
SELECT * from companies c
WHERE c.company_name LIKE 'Someone's Company%'


but the following which probably should work does not
SELECT * from companies c
WHERE c.company_name LIKE 'Someone''s Company%'

Any help/advice would be greatly appreciated.

M
 
From SQL CE Query Analyzer:

INSERT INTO test VALUES('conan','o''brien')
SELECT * FROM test WHERE lname = 'o''brien'
SELECT * FROM test WHERE lname LIKE 'o''brien'

C# .NET CF:

string sql = @"SELECT * FROM test WHERE lname = 'o''brien'";
SqlCeDatareader dtr = cmd.ExecuteQuery(sql);

Finally - you're in the wrong newsgroup. For SQL CE questions,
head to microsoft.public.sqlserver.ce.
--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
 
Doubled quotation mark works for me. In any case, if you use SqlCeParameter,
the provider will escape the string for you correctly.
By the way, what is the error you are getting?
 
Doubled quotation mark works for me. In any case, if you use SqlCeParameter,
the provider will escape the string for you correctly.
By the way, what is the error you are getting?
Click to expand...

An unhandled exception of type
'System.Data.SqlServerCe.SqlCeException' occurred in
System.Data.SqlServerCe.dll

I ended up discovering that my function to add the extra quote, while
it was being called fine, and was returning the correct value, it was
not updating the one that is used in the SELECT statement. *redface*.

Cheers for your help, without following the train of thought I did as
a result I doubt I would have found the problem so quick.

M
 
Back
Top