Table.Select

  • Thread starter Thread starter Jim Heavey
  • Start date Start date
J

Jim Heavey

How do you search for a field value which has and
embedded "'".

Say for instance I have a field named "Title" and it has a
value of "It's now or never". When I use this as search
criteria, it fails with a "Missing operand after 't'
operator. So this tells me that it does not like the '
mark.

So How can I include this value in my search?
 
I am assuming, that just as in SQL you need to escape the string's single
quote character, by using two.
 
Hi;

According to David Sceppa the author of Microsoft ADO.net
if you have a single quote i.e. Lastname = 'O'Rourke'
you must double them i.e. LastName = 'O''Rourke'.

Also recommended is using the replace method for the
above. Ex.

strHold = Lastname = '" & strLastName.Replace("'","''")
& "'".

Hope this helps you.
 
Hi

As the other respondents stated you can replace the ' with two '' e.g.

string SearchText = "It's now or never";

SqlCommand.CommandText = "SELECT * FROM mytable WHERE SearchedField =
'"+SearchText.Replace("'","''") +"'";

If you use Command Prameters, this get's done for you. e.g.

SqlCommand.CommandText = "SELECT * FROM mytable WHERE SearchedField =
@SearchText";
SqlCommand.Parameters.Add("@SearchText",SqlDbType.VarChar);

......

SqlCommand.Parameters["@SearchText"].Value = SearchText;

I feel that this is a beter method of solving the problem. If M$ decide to
change the way that SQL behaves, or you decide to use a deferent data
provider that behaves deferently, you do not need to change your code.

Gary
 
When I change "It's Now or Never" to "It''s Now or Never" and use that
with the Table.Select ("Title = '" & myTitle & "'") I get the exact same
error.

Using string.Replace method works fine if there is only as single "'"
within the text field, but if there are multpile, or if you want to test
for multiple, it becomes more complicated.

You would think that "It''s Now or Never" would work, but It is not
doing that for me. I will try again, but no luck on the first try
neither did "It'''s Now or Never".
 
Back
Top