Solved

Escaping single quote in Datarow fileters

Posted on 2004-04-07
10
1,141 Views
Last Modified: 2012-05-04
I'm trying to use the select on my typed data set but i get an error when the string i'm looking for contains a single quote!

string FilterString="Title=  '"+ MyCriteria + "'";
int TotalFound =(MyDatasource.Table.Select(FilterString);
0
Comment
Question by:saturn_one
  • 4
  • 3
  • 3
10 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10779302
use a double '' to escape it
0
 

Author Comment

by:saturn_one
ID: 10779417
i tried but i don't know how to scape double quote! i tried this but doesn't work
string @test="Title=  " + Convert.ToChar(34)+ title + Convert.ToChar(34);
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 10779512


you need to use the back slash.  Also, your @ symbol is in the wrong place.'

string test = @"Title= \'" + myCriteria + "\'"


Use the same thing with double quotes.

Console.WriteLine("this is \"it\"");  //==>  this is "it"
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 10779516


actually... come to think of it... using the @ symbol negates the escape sequences.

you need to remove the @ symbol all together.
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 10779517
string test = "Title= \'" + myCriteria + "\'"

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:saturn_one
ID: 10779533
actually i tried that too!
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10779747

I believe you need to place double single quotes like in sql server ... sorry if I worded it incorrectly before ....

example 'Jone''s'

so the correct code is ...

string test = "Title= \'" + myCriteria.Replace("\'","\'\'") + "\'";

MSDN covers dates and escape sequences when used in column names (which you should also be careful of)

The following characters are special characters and must be escaped, as explained below, if they are to be used in a column name:

\n (newline)

\t (tab)

\r (carriage return)

~

(

)

#

\

/

=

>

<

+

-

*

%

&

|

^

'

"

[

]

If a column name contains one of the above characters, the name must be wrapped in brackets. For example to use a column named "Column#" in an expression, you would write "[Column#]":





USER-DEFINED VALUES

User-defined values may be used within expressions to be compared against column values. String values should be enclosed within single quotes. Date values should be enclosed within pound signs (#). Decimals and scientific notation are permissible for numeric values. For example:

"FirstName = 'John'"

"Price <= 50.00"

"Birthdate < #1/31/82#"

For columns that contain enumeration values, cast the value to an integer data type. For example:
0
 

Author Comment

by:saturn_one
ID: 10779833
actually my example is

"Jone's book"
rather than
'Jone''s'

i'm trying to escape a single quote in middle of a string!
 
0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 40 total points
ID: 10779892
try this code instead of yours ...

string FilterString="Title=  \'" + myCriteria.Replace("\'","\'\'") + "\'";
int TotalFound =(MyDatasource.Table.Select(FilterString);

assuming that myCriteria is the string that you are comparing to title.

when you are applying an expression in a select rowfilter etc it is supposed to have string wrapped in SINGLE quotes as the included MSDN text says ....

Example ....
Title = 'jon'
Title = 'Jone''s Book'
Title = 'A Good Book'

you are not supposed to use double quotes within the user data side of the expression to delimit the string.

if you do not believe me I offer the following code ...

            static void Main(string[] args)
            {
                  DataTable dt = new DataTable("test") ;
                  dt.Columns.Add("test1", typeof(System.String));
                  dt.Columns.Add("test2", typeof(System.String));
                  DataView foo = new DataView(dt);
                  foo.RowFilter = "test1 = \'jones\'\'\ssss'";
            }

if you enclose it in double quotes or do not escape the single quote you will get a syntax exception on the line where it sets the rowfilter. rowfilter uses the same expression arguments as select().

on a side note I find it humorous that in MSDN under select() they actually use a date in the format '1/1/00' and under expression state it should be encapsulated with # #.
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10779903
in case there was confusion judging from re-reading your last reply I am not using a double quote in the string ... I am using 2 single quotes. it must be replaced by 2 single quotes (the exact same operation you do with sql server if not using parameters)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now