SQL Query with single quotes retrieves no record

Question

I am currently fetching a dataset from following query select * from TableName WHERE ColumnName ='values''s' query executes without any error and return dataset was empty rows. When i execute the same in SQL Worksheets it return data

Following code for ref.

string sqlQuery = "select * from TableName WHERE Name ='McNaught''s'";
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sqlQuery;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();            
conn.Open();
adapter.Fill(ds);
conn.Close();

What is the actual value that you want to include in the where condition? — sujith karivelil, Feb 22 '17 at 07:04
in where i included ColumnName = 'McNaught''s and Fertilizers' — SAA, Feb 22 '17 at 07:06
Do you want query a fixed sql or dynamic one(concatenating some C# string)? — Lei Yang, Feb 22 '17 at 07:12
The best way is to use input in parameter. http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements — A3006, Feb 22 '17 at 07:14
Lei Yang, Its a dynamic query where as TableName, ColumnName and Value is passed dynamically — SAA, Feb 22 '17 at 07:20

score 2 · Answer 1 · edited Feb 22 '17 at 08:01

2

Please try this:

string sqlQuery = 'SELECT * FROM TableName WHERE Name ="McNaught\'s"'

edited Feb 22 '17 at 08:01

Ilyes

14,640
4
29
55

answered Feb 22 '17 at 07:17

Kashish Agarwal

303
1
15

score 0 · Answer 2 · answered Feb 22 '17 at 07:26

The Problem is you need to provide Escape Sequence before the second ' so that compiler can distinguish between the previous apostrophe. Try this.

string sqlQuery = "select * from TableName WHERE Name ='McNaught'\'s'";
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sqlQuery;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();            
conn.Open();
adapter.Fill(ds);
conn.Close();

score 0 · Answer 3 · answered Feb 22 '17 at 07:26

0

Try this it will work with \ escape sequence.

string sqlQuery = "select * from TableName WHERE Name ='McNaught\"s'";

answered Feb 22 '17 at 07:26

Bharat

2,441
3
24
36

SQL Query with single quotes retrieves no record

3 Answers3