Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem with Tquery's LOCATE method???

Posted on 2001-06-26
7
Medium Priority
?
925 Views
Last Modified: 2008-03-04
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
Comment
Question by:jrmn
7 Comments
 
LVL 1

Expert Comment

by:Kaspars
ID: 6230271
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
 
LVL 4

Expert Comment

by:jsweby
ID: 6230288
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
 
LVL 17

Expert Comment

by:geobul
ID: 6231859
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!

 
LVL 1

Accepted Solution

by:
trex_fire earned 240 total points
ID: 6233566
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
 
LVL 4

Author Comment

by:jrmn
ID: 6233752
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
 
LVL 17

Expert Comment

by:geobul
ID: 6234241
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
 
LVL 4

Author Comment

by:jrmn
ID: 6247241
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question