Skip to content

Darn the Apostrophe in ADO.NET for SQL

December 15, 2015

If you have the ever slight problem of not being able to detect the apostrophe when placing this character in an SQL SELECT, UPDATE, or DELETE, ADO.NET will throw an error {“Incorrect syntax near ‘S’.\r\nUnclosed quotation mark after the character string ‘,’ )’.”}:

Escape the apostrophe (i.e. double-up the single quote character) in your SQL:

INSERT INTO Person
    (First, Last)
VALUES
    ('Joe', 'O''Brien')
              /\
          right here  

The same applies to SELECT queries:

SELECT First, Last FROM Person WHERE Last = 'O''Brien'

In C# its a tiny bit of work, you therefore need to find the index of this character ', then append another apostrophe at this index, so to do this, code as: - 

 if (ObjectInfo.Name != null)
 {
 if (query.Contains("'"))
 {
 int index = query.IndexOf("'");
 query.Insert(index, "'");
 }
 query = query + "'" + ObjectInfo.Name + "',";
 }
 else
 {
 query = query + "'',";
 }
Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: