Board index » delphi » Re: ADOQuery Problems Again. Thanks.

Re: ADOQuery Problems Again. Thanks.


2005-10-16 12:37:15 PM
delphi17
Hi,
I Already Do Test. (// escape the ' with '') Also Found Error :Syntax
error (Missing Opertor)In Query Expression. How To Solve ?
(escape the [ with [[]) Is OK. But I can not Add The Command 'WHERE Note
like '%[[]SomeText%';' To 'ADOQuery1.SQL.Add( )' Function. So Compiled
Error. How To Solve ?
Thanks. Hope Help Again.
dd writes:
Quote
Oscar writes:

>Hi,
>I'm Using delphi5 + ADODatabase With Ado Query.
>I Do A Search Statement (Source On Bottom). But Where I Try To Search A
>Char "[" And "'", That Show Me An Error "invalid pattern string" And
>"syntax error in query expression".
>So, Any Ideal Or Slove This Problems ? Some Time I Want To Search This
>Symbol("[" And "'")
>
>Hope Help. Thanks.
>
>ADOQuery1.Close;
>ADOQuery1.SQL.Clear;
>ADOQuery1.SQL.Add('Select * FROM Address');
>ADOQuery1.SQL.Add('WHERE Note LIKE ''%'+Edit1.Text+'%''');
>Adoquery1.Open;
>
>**"Edit1.Text" Is A Search Box.


You'll need to check for these characters and escape them.

Here is how the SQL statement should look

// escape the [ with [[]
SELECT *
FROM Address
WHERE Note like '%[[]SomeText%';

// escape the ' with ''
SELECT *
FROM Address
WHERE Note like '%''SomeText%';

You can use the StringReplace function to replace the instances.

There are other special search characters that you will need to escape.
Look in MS Access help for "LIKE" to find the special characters. I
believe you can escape the characters by doubling them up, "[" is an
exception, but there may be others.

 
 

Re: ADOQuery Problems Again. Thanks.

Oscar writes:
Quote
Hi,

I Already Do Test. (// escape the ' with '') Also Found Error :Syntax
error (Missing Opertor)In Query Expression. How To Solve ?

(escape the [ with [[]) Is OK. But I can not Add The Command 'WHERE Note
like '%[[]SomeText%';' To 'ADOQuery1.SQL.Add( )' Function. So Compiled
Error. How To Solve ?

Thanks. Hope Help Again.
Just modify the search text.
var
text: string;
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * FROM Address');
text := Edit1.Text;
text := StringReplace(text, '''', '''''', [rfReplaceAll, rfIgnoreCase]);
text := StringReplace(text, '[', '[[]', [rfReplaceAll, rfIgnoreCase]);
// do similar with other special chars
ADOQuery1.SQL.Add('WHERE Note LIKE ''%' + text + '%''');
Adoquery1.Open;
 

Re: ADOQuery Problems Again. Thanks.

Thanks. I Will Try Again.
dd writes:
Quote
Oscar writes:

>Hi,
>
>I Already Do Test. (// escape the ' with '') Also Found Error :Syntax
>error (Missing Opertor)In Query Expression. How To Solve ?
>
>(escape the [ with [[]) Is OK. But I can not Add The Command 'WHERE Note
>like '%[[]SomeText%';' To 'ADOQuery1.SQL.Add( )' Function. So Compiled
>Error. How To Solve ?
>
>Thanks. Hope Help Again.


Just modify the search text.

var
text: string;
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * FROM Address');

text := Edit1.Text;
text := StringReplace(text, '''', '''''', [rfReplaceAll, rfIgnoreCase]);
text := StringReplace(text, '[', '[[]', [rfReplaceAll, rfIgnoreCase]);
// do similar with other special chars

ADOQuery1.SQL.Add('WHERE Note LIKE ''%' + text + '%''');
Adoquery1.Open;