Board index » delphi » ADOQuery 'invalid pattern string' Error. Pls Help. Thanks.

ADOQuery 'invalid pattern string' Error. Pls Help. Thanks.


2005-10-14 10:00:01 AM
delphi35
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.
 
 

Re:ADOQuery 'invalid pattern string' Error. Pls Help. Thanks.

Oscar writes:
Quote
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 'invalid pattern string' Error. Pls Help. Thanks.

Thanks. I Will Try.
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.