Select a dataset field with an apostrophe - Escape characters don't appear to work

Question

Hi there,

I have a dataset containing a table that has multiple rows of data. The table holds columns "Unit_Name" and "Unit_Ref".

The data that has populated my dataset table has some Unit_Name values which have apostrophes (') in them.

I am trying to select all the row fields where a Unit_Name matches but I get an exception because I can't escape the apostrophe in the name.

Below is some example code:

// Create a query string                        <br/>
string ExistingProjQuery = string.Format("UnitName = '{0}' OR UnitRef = '{1}'", formal_nm, project_code);<br/>
<br/>
// At this point I have a query string:<br/>
//"UnitName = 'Children's Centre' OR UnitRef = '9999WNH'"<br/>
<br/>
// Select the data from the dataset table which generates the exception<br/>
MyDataSet.OrgStructureRow[] existingProj = (MyDataSet.OrgStructureRow[])m_DS.OrgStructure.Select(ExistingProjQuery);<br/>
<br/>
// The exception message is: <br/>
//{Syntax error: Missing operand after 's' operator."}	System.Exception {System.Data.SyntaxErrorException}<br/>

Can anyone offer any insight on how to resolve this?

---

There are only 10 types of people. Those who understand binary and those who don't

Answer 1

Hi,

Did you try to escape the query using @

string query = @"UnitName = 'Children's Centre' OR UnitRef = '9999WNH'";

Shival

Answer 2

Double-up on the internal apostrophes.  I know you said escape characters don't seem to work, but you didn't indicate what you tried.

"UnitName = 'Children''s Centre' OR UnitRef = '9999WNH'"

Answer 3

Building my query like this is syntactically correct
string Query = string.Format("UnitName = \"{0}\" OR UnitRef = \"{1}\"", formal_nm, project_code);

and gives this resultant string
"UnitName = \"Children's Centre\" OR UnitRef = \"9999WNH\""

but throws this exception
{"Syntax error: Missing operand after 'Children' operator."}    System.Exception {System.Data.SyntaxErrorException}

If I use the debugger to step through and manually update the query to be "UnitName = 'Children''s Centre' OR UnitRef ='9999WNH'"
This works…

but because I build the string from the contents of variables I'm not sure how to do this with string.Format

I also tried using the @ but again because the string is built from variables string.Format I'm not sure how to implement it.

---

There are only 10 types of people. Those who understand binary and those who don't

Answer 4

I've found a way round the problem. It's not exactly elegant but it works.

when I assign my member variable with the unit name I replace a single ' with 2
string formal_nm = nodeList[2].InnerText.Replace("'", "''");

Then I build my query
string Query = string.Format("UnitName = '{0}' OR UnitRef = '{1}'", formal_nm, project_code);

This gives me a query string like as below
"UnitName = 'Children''s Centre' OR UnitRef = '9999WNH'"

Thanks for your input guys. It's helped getting me on the right track.

---

There are only 10 types of people. Those who understand binary and those who don't