Solved

Problem with Tquery's LOCATE method???

Posted on 2001-06-26
7
900 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Accepted Solution

by:
trex_fire earned 80 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now