• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 928
  • Last Modified:

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
0
jrmn
Asked:
jrmn
1 Solution
 
KasparsCommented:
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.
0
 
jswebyCommented:
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.
0
 
geobulCommented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
trex_fireCommented:
Use another TQuery with parameters to do your search by passing a parameter who would look like:

select blabla from MyTable
where TheString like :SearchString

and put this string into it :  

Query1.ParamByName('SearchString').AsString := '%some text%;

This way, your server will do the job for you...

T-Rex
0
 
jrmnAuthor Commented:
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

0
 
geobulCommented:
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
0
 
jrmnAuthor Commented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now