[SOLVED] Using a Dataset to exploit the data

[pjtuloup]

Hi all,

I am having difficulty using data from a dataset: I would like to search the dataset for a particular record located in a field of the source query to be able to display it in an Edit of the form.

The code could be as follows:

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

1. //Request
2. Req:='SELECT CHP1,CHP2 From Table';
3. with DataModule1.SQLQuery1 do
4.     begin
5.        Close;
6.        SQL.Text:=Req;
7.        Open;
8.     end;
9.  
10. //SearchKey is an Edit.Text
11.  
12. if (SearchKey<>'') then
13.         begin
14.         for i:=0 to DataModule1.SQLQuery1.RecordCount-1 do
15.            if ClefSages.Text=DataModule1.SQLQuery1. ??? (what ?) then
16.               Edit.Text= ? (what?);
17.         end;
18.  
19.  

In short, I don't know how to browse the Dataset records and access a particular field of these records... I searched on the Internet but didn't find much (except that RecordCount is unreliable, but for now that's not the problem...)

If you had a clue to give me (or an example), I would be very grateful.

[cdbc]

Hi
How about 'Locate' implemented in 'TDataset' (I think)?!?
Try looking it up...
...or

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

1. DataModule1.SQLQuery1.First;
2. while not DataModule1.SQLQuery1.EOL do begin
3.   // example code, not tested much, only ~25 years :o)
4.   if DataModule1.SQLQuery1.FieldByName('Surname').AsString = Searchkey then
5.     break; // out of the loop and leaves sqlquery at that record...
6.   DataModule1.SQLQuery1.Next; //<- very important!
7. end;
8.  

But I would figure out how locate works, if I were you.
The above example, was added later
Regards Benny

[pjtuloup]

Oh Thanks a lot, cbc ! I will try that tomorrow.

Added: Thanks again, it works perfectly !

[Zvoni]

Oh Thanks a lot, cbc ! I will try that tomorrow.

And don't use RecordCount....

[pjtuloup]

Well noted, thank you, my research on the Internet had also warned me!

[pjtuloup]

Hi all, it's me again... sorry...

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

1. DataModule1.SQLQuery1.First;
2. while not DataModule1.SQLQuery1.EOL do begin
3.   // example code, not tested much, only ~25 years :o)
4.   if DataModule1.SQLQuery1.FieldByName('Surname').AsString = Searchkey then
5.     break; // out of the loop and leaves sqlquery at that record...
6.   DataModule1.SQLQuery1.Next; //<- very important!
7. end;

You wrote that "break" get out of the loop and leaves sqlquery at that record...

But I placed in my form an UpDown intended to access the next or previous record, and, in fact, when I click on Up or Down I see that the dataset is not on the record found by the test on the key

Voici mon code:

First, the first search on the dataset

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

1. if Ok=true then
2.    begin
3.      if (KeySearch.Text<>'') then
4.         begin
5.         DataModule1.SQLQuery1.First;
6.         while not DataModule1.SQLQuery1.EOF do
7.            begin
8.            if copy(DataModule1.SQLQuery1.FieldByName('IDLUGES').AsString,4,7)=KeySearch.Text then
9.               break;
10.            DataModule1.SQLQuery1.Next;
11.            end;
12.         end;
13.        ShowRecord();
14.    end;
15.  

(ShowRecord is a procedure which put fields contents in Edits)

Then the procedure when clicking on UpDown

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

1. procedure TFormSaisMvtEmp.UpDown1Click(Sender: TObject; Button: TUDBtnType);
2. begin
3.     case Button of
4.          btPrev:
5.            begin
6.            DataModule1.SQLQuery1.Prior;
7.            if copy(DataModule1.SQLQuery1.FieldByName('IDLUGES').AsString,4,7)=KeySearch.Text then
8.               ShowRecord();
9.            end;
10.          btNext:
11.            begin
12.            DataModule1.SQLQuery1.Next;
13.            if copy(DataModule1.SQLQuery1.FieldByName('IDLUGES').AsString,4,7)=KeySearch.Text then
14.               ShowRecord();
15.            end;
16.     end;
17. end;
18.  

(I know a "while" would be needed, but it's just a attempt here)

Maybe I would be better off putting a filter corresponding to the search key on the dataset from the start, but I haven't found an example and I don't see what mistake is mine.

Thanks !

[cdbc]

Hi
Sorry mate, the 'break' example, isn't particularly good, read the following.
Right, your not finding much, comes down to 'what you search for'... 
This f.i.: https://wiki.freepascal.org/Lazarus_Tdbf_Tutorial doesn't deal with SqlQuery per se, but "TDbf" is based in "TDataset" and that's what you need, in order to familiarize yourself, with the basic 'TDataset' functionality.
Once you've setup your Query with e.g.:

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

1. Query1.Close;
2. Query1.Sql.text:= 'select id, name, surname from tblFamily;';
3. Query1.Open;

You can use most of the 'TDataset' methods, found in the article above(most), so try and play with that for a while.
There's also this: https://wiki.freepascal.org/TSQLQuery
...and this: https://wiki.freepascal.org/Working_With_TSQLQuery
After a while, you might turn your attention towards this: https://wiki.freepascal.org/mORMot 
...and finally, you might wander into something like MVC, MVP(see another thread in this forum) or MVVM -frameworks, where the database code is hidden in a part of the 'Model' and accessed only through the 'Presenter' etc... (mORMot & tiObjects both implements something like this)
When you get to here, you've earned the right, to call yourself 'A seasoned database developer' 
Regards Benny

[Zvoni]

To stay with your attempt: in your case button of
In prev you need to loop backwards,
In next you loop forward

again breaking out the loop if something found.

In both cases you need to move one record prior/next before starting the loop

Think about it: you’re searching for something specific in say 100 records.
Your first hit is record 57.
„Prev“ doesn’t make sense, since you already passed the first 56 records.
Now you hit „next“
It’s not guaranteed that record 58 will match your criteria
Looping forward you find your next match in record, say, 78.
Now with „prev“ looping backwards you would find record 57 again

[pjtuloup]

Thank you all, you are truly a valuable and tireless help (I hope).

cbc is right, I have so much to learn about everything, and so quickly...

The program I'm writing I've already written in C++ Builder but I'm updating it by rewriting everything in Free Pascal for cost reasons and so much is so different... I'm not even at it yet comfortable with begin/end and ends. end; and end... I regret my { and } a little!

but I'm more and more experienced... as seasoned bugs maker !

I'll post tomorrow the code I came up, which works, in case other beginners need it.

But in any case I remain amazed at the progress that Lazarus has made (when I discovered it at the very beginning, it was practically unusable) which makes it a wonderful, completely mature tool.

Thanks again ! Without you it would take ten times longer...

[pjtuloup]

As promised, my code for those who need it

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

1. procedure TFormSaisMvtEmp.UpDown1Click(Sender: TObject; Button: TUDBtnType);
2. var
3.   FoundPrev: Boolean;
4.   FoundNext: Boolean;
5. begin
6. FoundPrev:=false;
7. FoundNext:=false;
8.     case Button of
9.          btPrev:
10.            begin
11.            while not DataModule1.SQLQuery1.BOF do
12.               begin
13.               DataModule1.SQLQuery1.Prior;
14.               if copy(DataModule1.SQLQuery1.FieldByName('IDLUGES').AsString,4,7)=SearchKey.Text then
15.                  begin
16.                  FoundPrev:=true;
17.                  break;
18.                  end
19.               end;
20.            if FoundPrev=false then
21.               begin
22.               Application.MessageBox('You are already at the first record corresponding to the search key!', 'User alert');
23.               end
24.            else
25.               ShowRecord();
26.            end;
27.          btNext:
28.            begin
29.            while not DataModule1.SQLQuery1.EOF do
30.               begin
31.               DataModule1.SQLQuery1.Next;
32.               if copy(DataModule1.SQLQuery1.FieldByName('IDLUGES').AsString,4,7)<>SearchKey.Text then
33.                  begin
34.                     FoundNext:=true;
35.                     break;
36.                  end
37.               end;
38.            if FoundNext=false then
39.               begin
40.               Application.MessageBox('You are already at the last record corresponding to the search key!', 'User alert');
41.               end
42.            else
43.               ShowRecord();
44.            end;
45.     end;
46. end;
47.