Solved

Problem with Tquery's LOCATE method???

Posted on 2001-06-26
7
910 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

617 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