Link to home
Start Free TrialLog in
Avatar of jrmn
jrmn

asked on

Problem with Tquery's LOCATE method???

Hi Experts,

I'm trying to locate a particular table entry on my MSSQL 7 table.
For a very valid reason, I have to use a TQuery component on my application to connect to this table.
With this, the only way (I guess) is to use the LOCATE method as there seems to be no other option.

The problem that I encounter is when I issue this code line:

--------------------------------------------------------------------

FieldToSearch := 'This is the text to search on my my MSSQL 7 table'; // String value of 50 characters or more.

if MyQuery.Locate('MySearchField', FieldToSearch, []) then
begin
:
:
:
end;

--------------------------------------------------------------------

I got an "Operation not applicable" error msg.

The FieldToSearch is a string variable. Sometime I need to store large text here (more than 50 characters mostly). Then, try to locate this on my MSSQL table.

The MySearchField field is varchar(30) only.

It is during those times when the FieldToSearch variable contains more than or equal to 50 characters that the error above occurs when using the Tquery's LOCATE method.

Is this error due to the fact that the MYSEARCHFIELD field is only varchar(30) and the text to search is more than what is allocated?

Is this a known problem with LOCATE method and using long text?

Is there another way to deal with this problem using functions or something?

Please show me some ways to deal with this one.

TIA,

jrmn
Avatar of Kaspars
Kaspars

I don't know if error is because of different lengths, but i often use KeyFields+GotoKey (or whatever the method is called) if Locate doesn't work (e.g., if there ar several fields to search for).
Just insert Field name into KeyFields at runtime and try that.
I think Locate, for some databases, also requires the fields you are searching for to be indexed; I know the Help says it will use an index if it is there, but I've had problems with Access in the past where my locate field wasn't indexed.

J.
Hi,
if you want to find something at all, try:

if MyQuery.Locate('MySearchField', Copy(FieldToSearch,1,30), [loCaseInsensitive]) then
begin
:
:
:
end;

btw, is your MyQuery opened?

Regards, Geo
ASKER CERTIFIED SOLUTION
Avatar of trex_fire
trex_fire

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jrmn

ASKER

Hi Experts,

Thanks for all the replies.
Anyway, I want to clear the problem I presented.
The Query, will act as a lookup.
If ever the SearchString exists on my mssql table (the field in particular), I'll get the key of that record and use it in executing other functions in my application.

Kaspars : I think GotoKey can't be applied with TQuery and it only works with TTable. right?

jsweby : I think the table doesn't have to indexed to use locate. I've tried this before with tables without indexes and it still works as long as the text you pass as a search string is not long or large. I also tried this with PARADOX table and it still works.

geobul : Yes the table/query is open. Could you explain some more on the syntax you presented? I'm not going to search for a particular portion of the string but rather the whole string...is this what you mean by the code you have?

trex_fire : You have a point my friend. I'm actually thinking about this solution beforehand but I decided to to hear first from other experts just like you what possible solutions they think is the best. Thanks for the time.


Cheers,
jrmn

Well, locate method compares two strings (in this case). If the first string is 30 chars length and the second is 50 or more, then you will never reach matching. That's why I'm cutting the longer string to be equal to the shorter one. On the other hand [loCaseInsensitive] option forces the comparing to ignore the difference between small and capital letters, i.e. 'THIS Is' = 'This is'.

Regards, Geo
Avatar of jrmn

ASKER

Thanks for all the inputs experts!
I guess, the best solution that is presented here for my problem is from trex_fire. Yup, the Tquery parameter is (for me) the best approach...thanks.

jrmn