It appears that the Filter property of ODAC datasets can't handle field names with single quotes. A simple example would be:
select 'Testing' from dual; (Field name will be 'Testing' with the single quotes.)
But setting the Filter properly to "TESTING' = 'Testing'" does not appear to work (internally the single quotes are stripped.)
Another example might be:
select 'Testing' "Mark's Field" from dual;
Setting the Filter properly to Mark's Field = 'Testing' will cause an error.
From looking at the code it looks like we can add square brackets around the name field, but unfortunately the single quotes still cause problems.
Thanks for any tips or thoughts.
Can't filter on a field name that contains a single quote.
Re: Can't filter on a field name that contains a single quote.
Hello,
If there are single or double quotes in the filter, we consider it as the string beginning, but not the field name, as strings must be quoted. Therefore, in the filter expression for such fields, you should use square brackets.
If there are single or double quotes in the filter, we consider it as the string beginning, but not the field name, as strings must be quoted. Therefore, in the filter expression for such fields, you should use square brackets.
Re: Can't filter on a field name that contains a single quote.
I'm happy to use square brackets, but what if the field name contains a single quote? It appears that they are not preserved and therefore the internal FindField call fails. Is there a way to escape a single quote between square brackets such that it is preserved?
Re: Can't filter on a field name that contains a single quote.
Thank you for the information. We have fixed this behavior.
Re: Can't filter on a field name that contains a single quote.
Excellent. Thanks for the help.
Re: Can't filter on a field name that contains a single quote.
You are welcome. Feel free to contact us if you have any further questions.