DBF edit & append problem

[Petrus Vorster]

Greetings all.

Please help me before i go off my rockers.
I made this simple procedure that will connect to a simple tdbf inside a datamodule.

It will match TWO strings to find the required field.If exist, then write on that record. If not, then append.
If finds the required record but it will not edit the contents.
The append when not found works well.

Code: Pascal  [Select][+][-]

1. procedure tpowerbox5.MatchAndSaveStrings(DBF: TDBF; Str1, Str2:string);
2. var
3.   Found: Boolean;
4.   I: Integer;
5.  
6. begin
7.   Found := False;
8.   DBF.First;
9.   begin
10.   if DBF.Locate('Boxnum', str1, []) and DBF.Locate('Branch', str2, []) then
11.   begin
12.       Found := True;
13.       DBF.Edit;
14.       dbf.FieldByName('Boxnum').AsString:=edit1.Text;
15.       dbf.FieldByName('Branch').AsString:=Combobox3.Text;
16.       dbf.FieldByName('Surname').AsString:=edit2.Text;
17.       dbf.FieldByName('Init').AsString:=edit3.Text;
18.       dbf.FieldByName('Title').AsString:=Combobox4.Text;
19.       dbf.FieldByName('Idnum').AsString:=edit4.Text;
20.       dbf.FieldByName('Cell1').AsString:=edit5.Text;
21.       dbf.FieldByName('Cell2').AsString:=edit6.Text;
22.       dbf.FieldByName('Email').AsString:=edit7.Text;
23.       dbf.FieldByName('Address').AsString:=edit8.Text;
24.       dbf.FieldByName('Town').AsString:=edit9.Text;
25.  
26.       DBF.Post;
27.  
28.  
29.   end
30.   else
31.   begin
32.       dbf.Append;
33.       dbf.FieldByName('Boxnum').AsString:=edit1.Text;
34.       dbf.FieldByName('Branch').AsString:=Combobox3.Text;
35.       dbf.FieldByName('Surname').AsString:=edit2.Text;
36.       dbf.FieldByName('Init').AsString:=edit3.Text;
37.       dbf.FieldByName('Title').AsString:=Combobox4.Text;
38.       dbf.FieldByName('IDnum').AsString:=edit4.Text;
39.       dbf.FieldByName('Cell1').AsString:=edit5.Text;
40.       dbf.FieldByName('Cell2').AsString:=edit6.Text;
41.       dbf.FieldByName('Email').AsString:=edit7.Text;
42.       dbf.FieldByName('Address').AsString:=edit8.Text;
43.       dbf.FieldByName('Town').AsString:=edit9.Text;
44.       dbf.FieldByName('Rentstatus').AsString:='Rented';
45.  
46.       dbf.Post;
47.   end;
48.   end;
49.   end;                            

Where in the world have I gone wrong?

Regards, Peter

[cdbc]

Hi
I dunno, but the following doesn't sit right with me:

Code: Pascal  [Select][+][-]

1. if DBF.Locate('Boxnum', str1, []) and DBF.Locate('Branch', str2, []) then

I don't think you can do that with 'Locate'...
Regards Benny

[Zvoni]

Hi
I dunno, but the following doesn't sit right with me:

Code: Pascal  [Select][+][-]

1. if DBF.Locate('Boxnum', str1, []) and DBF.Locate('Branch', str2, []) then

I don't think you can do that with 'Locate'...
Regards Benny

Yes and no.....
https://www.freepascal.org/docs-html/fcl/db/tdataset.locate.html

public function TDataSet.Locate(
  const KeyFields: string;
  const KeyValues: Variant;
  Options: TLocateOptions
):Boolean; virtual;
Arguments

KeyFields  List of fields, separated by semicolons
KeyValues Single value or array of values.
Options    Options to take into account when searching for the record.

Untested

Code: Pascal  [Select][+][-]

1. if DBF.Locate('Boxnum;Branch', [str1,str2], []) Then

[cdbc]

Hi
@Zvoni: Thanks mate =^ (thumbs up)
Regards Benny

[Zvoni]

On a sidenote: Just looking at the intended result of the code:
From a DB-Perspective, "BoxNum" and "Branch" would have to be a composite Primary Key of that Table.
Because you'll get Problems, if you have multiple records that satisfy your search-criteria.

And since i know, Peter is currently trying/considering to port a DBF-App to SQlite (see other threads)...

Code: Pascal  [Select][+][-]

1. MySQLQuery.Close;
2. MySQLQuery.SQL.Text:='SELECT * FROM MyDBFTable WHERE BoxNum=:str1 AND Branch=:str2';
3. MySQLQuery.ParamByName('str1').AsString:=str1;
4. MySQLQuery.ParamByName('str2').AsString:=str2;
5. MySQLQuery.Open;
6.  

Check Result if RecordCount=0 (or EOF and BOF both equal True), and you have your boolean which branch of the If-Then-Else to take

[JanRoza]

And maybe doing a commit or commitretaining after the post will help too?

[Zvoni]

And maybe doing a commit or commitretaining after the post will help too?

It‘s DBF.
No commit or rollback or whatever

[JanRoza]

Sorry, I was thinking with my SQLite hat on.

[Petrus Vorster]

Thank you all.

Let me go test some of the suggestions.

-Peter

[Petrus Vorster]

After some playing around, this seem to work:

Code: Pascal  [Select][+][-]

1. procedure tpowerbox5.MatchAndSaveStrings(DBF: TDBF; Str1, Str2:string);
2. var
3.   Found: Boolean;
4.   I: Integer;
5.  
6. begin
7.   Found := False;
8.   DBF.First;
9.   begin
10.  
11.   if dbf.locate('Boxnum',str1,[]) then
12.   begin
13.       if Dbf.FieldByName('Branch').AsString = Str2 then
14.       begin
15.       Found := True;
16.       DBF.Edit;
17.       dbf.FieldByName('Boxnum').AsString:=edit1.Text;
18.       dbf.FieldByName('Branch').AsString:=Combobox3.Text;
19.       dbf.FieldByName('Surname').AsString:=edit2.Text;
20.       dbf.FieldByName('Init').AsString:=edit3.Text;
21.       dbf.FieldByName('Title').AsString:=Combobox4.Text;
22.       dbf.FieldByName('Idnum').AsString:=edit4.Text;
23.       dbf.FieldByName('Cell1').AsString:=edit5.Text;
24.       dbf.FieldByName('Cell2').AsString:=edit6.Text;
25.       dbf.FieldByName('Email').AsString:=edit7.Text;
26.       dbf.FieldByName('Address').AsString:=edit8.Text;
27.       dbf.FieldByName('Town').AsString:=edit9.Text;
28.  
29.       DBF.Post;
30.       end;
31.  
32.   end
33.   else
34.   begin
35.       dbf.Append;
36.       dbf.FieldByName('Boxnum').AsString:=edit1.Text;
37.       dbf.FieldByName('Branch').AsString:=Combobox3.Text;
38.       dbf.FieldByName('Surname').AsString:=edit2.Text;
39.       dbf.FieldByName('Init').AsString:=edit3.Text;
40.       dbf.FieldByName('Title').AsString:=Combobox4.Text;
41.       dbf.FieldByName('IDnum').AsString:=edit4.Text;
42.       dbf.FieldByName('Cell1').AsString:=edit5.Text;
43.       dbf.FieldByName('Cell2').AsString:=edit6.Text;
44.       dbf.FieldByName('Email').AsString:=edit7.Text;
45.       dbf.FieldByName('Address').AsString:=edit8.Text;
46.       dbf.FieldByName('Town').AsString:=edit9.Text;
47.       dbf.FieldByName('Rentstatus').AsString:='Rented';
48.  
49.       dbf.Post;
50.   end;
51.   end;
52.   end;                            

Thank you all.

-Peter

[Zvoni]

After some playing around, this seem to work:

Code: Pascal  [Select][+][-]

1. procedure tpowerbox5.MatchAndSaveStrings(DBF: TDBF; Str1, Str2:string);
2. var
3.   Found: Boolean;
4.   I: Integer;
5.  
6. begin
7.   Found := False;
8.   DBF.First;
9.   begin
10.  
11.   if dbf.locate('Boxnum',str1,[]) then
12.   begin
13.       if Dbf.FieldByName('Branch').AsString = Str2 then
14.       begin
15.       Found := True;
16.       DBF.Edit;
17.       dbf.FieldByName('Boxnum').AsString:=edit1.Text;
18.       dbf.FieldByName('Branch').AsString:=Combobox3.Text;
19.       dbf.FieldByName('Surname').AsString:=edit2.Text;
20.       dbf.FieldByName('Init').AsString:=edit3.Text;
21.       dbf.FieldByName('Title').AsString:=Combobox4.Text;
22.       dbf.FieldByName('Idnum').AsString:=edit4.Text;
23.       dbf.FieldByName('Cell1').AsString:=edit5.Text;
24.       dbf.FieldByName('Cell2').AsString:=edit6.Text;
25.       dbf.FieldByName('Email').AsString:=edit7.Text;
26.       dbf.FieldByName('Address').AsString:=edit8.Text;
27.       dbf.FieldByName('Town').AsString:=edit9.Text;
28.  
29.       DBF.Post;
30.       end;
31.  
32.   end
33.   else
34.   begin
35.       dbf.Append;
36.       dbf.FieldByName('Boxnum').AsString:=edit1.Text;
37.       dbf.FieldByName('Branch').AsString:=Combobox3.Text;
38.       dbf.FieldByName('Surname').AsString:=edit2.Text;
39.       dbf.FieldByName('Init').AsString:=edit3.Text;
40.       dbf.FieldByName('Title').AsString:=Combobox4.Text;
41.       dbf.FieldByName('IDnum').AsString:=edit4.Text;
42.       dbf.FieldByName('Cell1').AsString:=edit5.Text;
43.       dbf.FieldByName('Cell2').AsString:=edit6.Text;
44.       dbf.FieldByName('Email').AsString:=edit7.Text;
45.       dbf.FieldByName('Address').AsString:=edit8.Text;
46.       dbf.FieldByName('Town').AsString:=edit9.Text;
47.       dbf.FieldByName('Rentstatus').AsString:='Rented';
48.  
49.       dbf.Post;
50.   end;
51.   end;
52.   end;                            

Thank you all.

-Peter

This is a disaster in waiting.....
you find the first record for BoxNum.
In this found record you compare Branch if it's equal, if yes, edit, if no Append......
....and you completely missed that the NEXT record (or any other after that one) might equal both criteria

Have you tried my answer in post 3 (Reply #2)?

[CharlyTango]

Locate is pretty much the slowest search you can use (even in DBF)
if you don't use indexes, it's just a miserable wait. You can also search quickly with DBF, even with high record numbers (>1 million). But the last time I did something like this was 35 years ago.

if you want to search over two fields, you either create a (redundant) field in which both values are combined or you just create a combined index over the two fields.
I have no idea which index forms the DBF support of Lazarus supports because I don't use DBF for a good reason.
There was IDX NDX MDX and probably several others. The capabilities of an index are determined by the corresponding support by Lazarus.

Air code:

Code: Pascal  [Select][+][-]

1. // Create index
2. MyDbf.Exclusive := True;
3. MyDbf.Open;
4. MyDbf.AddIndex('idxLastName', 'LastName', [ixCaseInsensitive]);
5. MyDbf.Close;
6.  
7. // Use index
8. MyDbf.Open;
9. MyDbf.IndexName := 'idxLastName';
10. if MyDbf.Locate('LastName', 'Miller', [loCaseInsensitive]) then
11.  ShowMessage('Found!');

Nowadays I would no longer recommend DBF, because SQLite is now available on practically every platform.

[Petrus Vorster]

Great!

Thank you very much. Will update the tool.

While we are still here, is there a simple way to focus a TDBGRIDVIEW to the top-left cell.
I run a Filter on a specific dataset, and it uses a new form with the Dbgridview on it.

I like things to be neat and it annoys me greatly when you open the form a few times during the day and it is where you left off or right of the bottom and you need to scroll up first.
From my readings that appears to be some hidden function.

A.I gave me a rubbish answer. 

Its probably not going to be a real issue in real time, but the OCD will kick in real soon....

Thanks for all the help fellows, it is much appreciated.

-Peter

[Petrus Vorster]

To answer some of the questions above.

Zvoni : Yes, i luckily picked up that disaster. I made many modifications.
It works perfectly now. Thank you.

The using of two string to locate a record works perfectly now. Thank you a million times.

I am however going to implement all your suggestions.
My Pascal had improved 300 fold in just a few months.

-Peter