Devart Forums

Posted: **Fri 04 Jul 2008 04:39**

Hi,

i try to use TMSQuery.LocateEx in this way:

(SQL Server DAC 4.50.0.35, BDS (Delphi) 2007, connect to northwind - DB SQL-Server 2000)

Code: Select all

Var empID:Integer;
      date:TDateTime;

MSQuery1.SQL.Text:="SELECT * FROM Orders ORDER BY EmloyeeID,OrderDate";
MSQuery1.IndexFieldNames:="EmployeeID;OrderDate";
MSQuery1.Open;

empID:=4;
date:=StrToDate('01.01.1997');
MSQuery1.locateEx('EmployeeID;Orderdate',VarArrayOf([empID,date]),[lxNearest])

Code: Select all

Sample - Data in Orders:

empID, Orderdate
...
3 , 30.04.1998
4 , 08.07.1996
....
4 , 20.12.1996
4 , 03.01.1997
...

In documentation i read:

lxNearest
LocateEx moves the cursor to a specific record in a dataset or to the
first record in the dataset that is greater than the values specified in
the KeyValues parameter. For this option to work correctly the dataset should be sorted by the fields the search is performed in. If the dataset is not sorted, the function may return a line that is not connected with the search condition

I expect, that my locateEX() - statement positions at [4, 03.01.1997], but it sets the pointer to [4,08.07.1996] - the first record with employeeID = 4

It seems, the second field is unused for finding the record.

Is it a problem especially with datetime - columns or do i sth. wrong ?

Thanks
Tino

P.S. Demo - App is available if needed.

Posted: **Mon 07 Jul 2008 08:31**

tinof wrote:P.S. Demo - App is available if needed.

Please, send me this demo to evgeniyd*devart*com.

Posted: **Tue 08 Jul 2008 08:23**

Many thank's for testing.

My Demo - App did work now, my 'really' app unfortunally not. But I've found the solution for that :

I call locateEx on a table like this:

Code: Select all

EXEC sp_addtype N'ID', N'numeric(18,0)', N'null'
go

CREATE TABLE MyTable
(
Variant_ID ID,
Date SmallDateTime,
...
)

SDAC does not recognize the user column - type "ID".
If i change the column data type to numeric(18,0) instead of 'ID' (what means the same) it works !
Well, i'ts not neccesary for SDAC to 'parse' user defined types, i have to change my database.

But, sorry, now i've a second problem:

See my Example

Code: Select all

empID, Orderdate 
... 
3 , 30.04.1998 
4 , 08.07.1996 
.... 
4 , 20.12.1996 
4 , 03.01.1997 
...

If i locate [4,31.12.1996] i get [4, 03.01.1997] - perfect.

If i locate [3 , 01.05.1998] i expect [4 , 08.07.1996] because this is the next record 'greater than the specified values'. But the record pointer does'nt moves, there is no effect if i 'search' such keys.
Is this a bug or a feature

?

Thanks again for help

Tino

Posted: **Wed 09 Jul 2008 11:32**

For values [3, 01.05.1998] the best matching record is the record with values [3, 30.04.1998] because the value of the EmployeeID field exactly matches the search condition. The record with values [4, 08.07.1996] is worse because both field values differ.

Devart Forums

LocateEx and segmented keys

LocateEx and segmented keys

Re: LocateEx and segmented keys