Board index » delphi » Locate problem

Simon Bowyer

Delphi Developer
Tue, 23 Mar 2004 17:32:26 GMT

Locate problem

I am using the locate method to check if a record exists before adding it
again. I found a bug in my code and had to change the fields passed in
locate. Now, the locate method fails to find the row even though it is in
the database.  My code is listed below. Both loops fail to find any matching
rows. The fGang table is the amster of fGangResource and is on the correct
GangId. The key to fGangResource is GangId, GangResourceId with ResourceId
being another field. This is so a resource can exist in more than one gang.
Does locate require the fields to be actual keys? Does locate require the
fields to be in field order? Both tables are TADOTables and I'm using D5
Pro, ADO Express and Access 2000 as the db.

Thanks in advance
Simon

           if fGangTable.Locate('GangId',fGang.GangId,[]) then begin

              // See if its been changed
              for i := 0 to fGang.Employees.Count - 1 do begin
                 fGangResourceTable.First;
                 if not
fGangResourceTable.Locate('GangId;ResourceId',VarArrayOf([fGang.GangId,TReso
urce(fGang.Employees[i]).ResourceId]),[]) then begin
                    fGangResourceTable.Insert;
                    fGangResourceTable.FieldByName('ResourceType').AsString
:= TResource(fGang.Employees[i]).ResourceType;
                    fGangResourceTable.FieldByName('ResourceId').AsInteger
:= TResource(fGang.Employees[i]).ResourceId;
                    fGangResourceTable.Post;
                 end;
              end;

              for i := 0 to fGang.Vehicles.Count - 1 do begin
                 fGangResourceTable.First;
                 if not
fGangResourceTable.Locate('GangId;ResourceId',VarArrayOf([fGang.GangId,TReso
urce(fGang.Employees[i]).ResourceId]),[]) then begin
                    fGangResourceTable.Insert;
                    fGangResourceTable.FieldByName('ResourceType').AsString
:= TResource(fGang.Vehicles[i]).ResourceType;
                    fGangResourceTable.FieldByName('ResourceId').AsInteger
:= TResource(fGang.Vehicles[i]).ResourceId;
                    fGangResourceTable.Post;
                 end;
              end;

 
rb

Delphi Developer
Tue, 23 Mar 2004 19:15:45 GMT

Re:Locate problem


It would be helpful if we knew which database server (if any) do you use and
if it's case sensitive.

Nonetheless, first thing that poked my eye is the fact that you run Locate
and if it returns True (record found), you proceed with insert. Is that what
you really wanted?

Otherwise, you may want to check for trailing spaces. You may also want to
try Locate with loPartialKey and/or loCaseInsesitive options (instead of
[]).

If you use database server, you may want to run insert without checking anb
catch the error in except block. The error code (PK or FK violation) would
tell you whether to continue or not.

rb

Simon Bowye

Delphi Developer
Tue, 23 Mar 2004 19:30:56 GMT

Re:Locate problem


Thanks for your reply. See below for my answers.

Thanks
Simon

Quote
"rb" <ra...@killspam-videotron.ca> wrote in message news:3bbd9662_1@dnews...

> It would be helpful if we knew which database server (if any) do you use
and
> if it's case sensitive.

As I said in my original post I'm using Access 2000 as the DB and the fields
are integers so case sensitive is irrelavant

Quote

> Nonetheless, first thing that poked my eye is the fact that you run Locate
> and if it returns True (record found), you proceed with insert. Is that
what
> you really wanted?

The locate line starts if not locate..... therefore it should only go into
the then part if it doesn;t find a row.

Quote

> Otherwise, you may want to check for trailing spaces. You may also want to
> try Locate with loPartialKey and/or loCaseInsesitive options (instead of
> []).

As I said earlier the fields are integer so there are no trailing spaces and
case insensitive and partial key will have no meaning.

Quote

> If you use database server, you may want to run insert without checking
anb
> catch the error in except block. The error code (PK or FK violation) would
> tell you whether to continue or not.

The key to the file is GangId and GangResourceId, the later being generated
when needed and therefore not known. The ResourceId I'm searching for is a
non key field as I want ot be able to have the same resource in different
gangs.
Quote
> rb

Thérèse Hanque

Delphi Developer
Tue, 23 Mar 2004 19:45:32 GMT

Re:Locate problem


Hi Simon,

Quote
> I am using the locate method to check if a record exists before adding it
> again. I found a bug in my code and had to change the fields passed in
> locate. Now, the locate method fails to find the row even though it is in
> the database.

First, the Locate method of ADOExpress is not very reliable, particularly when
searching on multiple fields. You might try Vassil Nazarov's TBetterADODataSet
that corrects several bugs of this method:

http://web.orbitel.bg/vassil/

Second, you do not indicate the provider you are using. For example, using the
ODBC provider instead of the Jet 4 provider for Access 2000 might prevent some
functions to behave as expected.

Finally, in your second loop you search on the value
TResource(fGang.Employees[i]).ResourceId inside a loop on fGang.Vehicles- maybe
a typo?

Thrse

Simon Bowye

Delphi Developer
Tue, 23 Mar 2004 20:49:33 GMT

Re:Locate problem


Thrse

Thanks for your reply.

I will have a look at TBetterADODataSet. Is it well supported?

I am using Microsoft.Jet.OLEDB.4.0 as my provider. Does locate have problems
with this provider?

And thanks for spotting that typo!

Thanks
Simon

Quote
"Thrse Hanquet" <therese.hanq...@skynet.be> wrote in message

news:3bbd9d55$1_2@dnews...
Quote
> Hi Simon,

> > I am using the locate method to check if a record exists before adding
it
> > again. I found a bug in my code and had to change the fields passed in
> > locate. Now, the locate method fails to find the row even though it is
in
> > the database.

> First, the Locate method of ADOExpress is not very reliable, particularly
when
> searching on multiple fields. You might try Vassil Nazarov's
TBetterADODataSet
> that corrects several bugs of this method:

> http://web.orbitel.bg/vassil/

> Second, you do not indicate the provider you are using. For example, using
the
> ODBC provider instead of the Jet 4 provider for Access 2000 might prevent
some
> functions to behave as expected.

> Finally, in your second loop you search on the value
> TResource(fGang.Employees[i]).ResourceId inside a loop on fGang.Vehicles-
maybe
> a typo?

> Thrse

Emiliano Sos

Delphi Developer
Tue, 23 Mar 2004 22:57:04 GMT

Re:Locate problem


Quote
> I am using Microsoft.Jet.OLEDB.4.0 as my provider. Does locate have
problems
> with this provider?

We've used locate with a M/D relationship of 2 TADOTables and JetEngine 4.0
with MsAccess2000, and if you try a Locate on the Detail table, it will find
records not corresponding to that Master...  It seems it's more a bug of
TADOTable and TADODataset than a problem with the provider...

By the way, if somebody knows more about that problem (other than
suggestions to use BetterADODataset) i'd appreciate....

Thérèse Hanque

Delphi Developer
Wed, 24 Mar 2004 00:52:03 GMT

Re:Locate problem


Hi Simon,

Quote
> I will have a look at TBetterADODataSet. Is it well supported?

From what I see on this newsgroup, TBetterADODataSet has a better support than
ADOExpress components. Vassil often answers questions and comments about its
component in this newsgroup, and updates it regularly according to the needs. Of
course, to make the best of it, you better have some background about ADO /
database programming.

Quote
> I am using Microsoft.Jet.OLEDB.4.0 as my provider. Does locate have problems
> with this provider?

If it has, they should not be due to the provider which is supposed to be the
most appropriate.
Locate is not an ADO method but a Delphi method, using ADO methods and
properties in order to reproduce - as much as possible - the inherited
TDataSet's Locate method. You may have wondered why the loCaseInsensitive option
does not do anything with ADO - it is because it was implemented for use with
BDE and not removed in the ADO method.

Thrse

Thérèse Hanque

Delphi Developer
Wed, 24 Mar 2004 01:07:01 GMT

Re:Locate problem


Hi Emiliano,

Quote
> We've used locate with a M/D relationship of 2 TADOTables and JetEngine 4.0
> with MsAccess2000, and if you try a Locate on the Detail table, it will find
> records not corresponding to that Master...  It seems it's more a bug of
> TADOTable and TADODataset than a problem with the provider...
> By the way, if somebody knows more about that problem (other than
> suggestions to use BetterADODataset) i'd appreciate....

I don't know how the M/D relationship is implemented within ADOExpress, but this
seems logical, because Locate works on a cloned copy of the Recordset and does
not seem to take into account all modifications to the original Recordset -
that's why Vassil implemented a fix for the case where the original Recordset is
filtered. Maybe the M/D case is similar...

Thrse

Henry H

Delphi Developer
Thu, 25 Mar 2004 00:45:23 GMT

Re:Locate problem


Quote
"Emiliano Sosa" <es...@gosierra.com> wrote in message

news:3bbdc93c_2@dnews...
Quote
> > I am using Microsoft.Jet.OLEDB.4.0 as my provider. Does locate have
> problems
> > with this provider?

> We've used locate with a M/D relationship of 2 TADOTables and JetEngine
4.0
> with MsAccess2000, and if you try a Locate on the Detail table, it will
find
> records not corresponding to that Master...  It seems it's more a bug of
> TADOTable and TADODataset than a problem with the provider...

  I have encountered this problem too, it seem like a BUG with following
case,
1. Use M/D relation with MasterField and IndexFieldNames
2. Detail Table use an Order by clause.
  I'm using SQL OLEDB provider, MDAC2.7, I change the M/D relation to
Parameter connection
to avoid this problem.

Henry He

Other Threads

1. Locate problem

2. Update on Locate Problem

3. Locate problem with accented chars

4. ADO Locate Problem after Filtering

5. The Ubiquituous Locate Problem

6. TAdoQuery.Locate problem

7. Locate problem

8. ADOTable.Locate problem

9. Locate Problems

10. TADOQuery Locate problem