### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: "While not Recordset.EOF" does not work  (Read 721 times)

#### Weitentaaal

• Full Member
• Posts: 158
##### "While not Recordset.EOF" does not work
« on: January 19, 2021, 02:42:04 pm »
Hello,

So i have this Code :

Code: Pascal  [Select][+][-]
1. wgrExecute(dbCalc, 'SELECT * FROM GERAET WHERE ZEICHNUNG = ' + IntToStr(ZeichNr) + ' AND ZLAL = 0 AND KOMP = 0 AND ART > 0 AND ART <> 16 ORDER BY ZARTNUM ASC;');
2.    dbCalc.First;
3.    ShowMessage(dbCalc.RecordCount.ToString); //--> 2 Records
4.    while not dbCalc.EOF do begin
5.       if dbCalc.FieldByName('Art').AsInteger = Btl_SW then
6.          BereSW.ShowModal
7.       else if dbCalc.FieldByName('Art').AsInteger = Btl_A then
8.          BereA.ShowModal
9.       else if dbCalc.FieldByName('Art').AsInteger = Btl_M then
10.          BereM.ShowModal
11.       else if dbCalc.FieldByName('Art').AsInteger = Btl_L then
12.          BereL.ShowModal
13.       else if dbCalc.FieldByName('Art').AsInteger = Btl_KF then
14.          BereKF.ShowModal
15.       else if dbCalc.FieldByName('Art').AsInteger = Btl_TF then
16.          BereTF.ShowModal
17.       else if dbCalc.FieldByName('Art').AsInteger = Btl_AF then
18.          BereAF.ShowModal
19.       else if dbCalc.FieldByName('Art').AsInteger = Btl_AKF then
20.          BereAKF.ShowModal
21.       else if dbCalc.FieldByName('Art').AsInteger = Btl_RF then
22.          BereRF.ShowModal
23.       else if dbCalc.FieldByName('Art').AsInteger = Btl_EH then
24.          BereEH.ShowModal
25.       else if dbCalc.FieldByName('Art').AsInteger = Btl_EE then
26.          BereEE.ShowModal
27.       else if dbCalc.FieldByName('Art').AsInteger = Btl_KH then
28.          BereKH.ShowModal
29.       else if dbCalc.FieldByName('Art').AsInteger = Btl_TA then
30.          BereTa.ShowModal
31.       else if dbCalc.FieldByName('Art').AsInteger = Btl_FS then
32.          BereFS.ShowModal
33.       else if dbCalc.FieldByName('Art').AsInteger = Btl_P then
34.          BereP.ShowModal
35.       else if dbCalc.FieldByName('Art').AsInteger = Btl_SD then
36.          BereSD.ShowModal
37.       else if dbCalc.FieldByName('Art').AsInteger = Btl_OB then
38.          BereOB.ShowModal
39.       else if dbCalc.FieldByName('Art').AsInteger = Btl_DB then
40.          BereDB.ShowModal
41.       else if dbCalc.FieldByName('Art').AsInteger = Btl_LW then
42.          BereLW.ShowModal
43.       else if dbCalc.FieldByName('Art').AsInteger = Btl_PT then
44.          BerePT.ShowModal
45.       else if dbCalc.FieldByName('Art').AsInteger = Btl_RO then
46.          BereRO.ShowModal
47.       else if dbCalc.FieldByName('Art').AsInteger = Btl_KV then
48.          BereKV.ShowModal;
49.       dbCalc.Active:= True;
50.       dbCalc.Next;
51.    end;
52.

wich worked fine last time i used it.
Today i tryed again and it did not work how it has last time.
it Shows my First Form then The Second but after the Second there is an Endless Loop when i Return Modal result mrOK.
It Should just go through this twice.

What did i wrong ? i didn't change anything in this class. Was there an update maybe of the IDE ?

Pls Help
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

#### rvk

• Hero Member
• Posts: 4472
##### Re: "While not Recordset.EOF" does not work
« Reply #1 on: January 19, 2021, 02:52:48 pm »
Why do you have that dbCalc.Active:= True; in there? (line 49 in your snippet)
Remove it. Period.

If for some reason your dbCalc isn't active anymore, the dbCalc.Next won't work anymore anyway.
In that case you need to find the reason the dbCalc is closed.
If the dbCalc is closed it will lose its position and you can't use dbCalc.Next anymore.

#### Weitentaaal

• Full Member
• Posts: 158
##### Re: "While not Recordset.EOF" does not work
« Reply #2 on: January 19, 2021, 03:01:13 pm »
Ah ok Thanks, my dbCalc closes itself after i insert Something in my Db with a Commit
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

#### rvk

• Hero Member
• Posts: 4472
##### Re: "While not Recordset.EOF" does not work
« Reply #3 on: January 19, 2021, 03:06:20 pm »
Ah ok Thanks, my dbCalc closes itself after i insert Something in my Db with a Commit
Then don't commit until you have completed the loop.
Or use CommitRetaining without closing the dataset.
Bet even then you can't be sure of the position after the insert. It can be at the end or at the inserted record and NOT at the record you expected.

You could also use a different dataset for inserting (which might be the better choice not to interrupt the loop).

Inserting during a loop (in the same dataset) is unpredictable to say the least.
Will the loop include the just inserted record or not? etc.

So using a different dataset-component to insert the record might be the best choice.

#### Weitentaaal

• Full Member
• Posts: 158
##### Re: "While not Recordset.EOF" does not work
« Reply #4 on: January 19, 2021, 03:09:14 pm »
OK Thanks,

can i Do Something like this?:

dbInsert.SQL.Text := dbCalc.SQL.TEXT
dbinsert.Append ...
...
dbinsert.clear

then repeat it if i need again
« Last Edit: January 19, 2021, 03:12:54 pm by Weitentaaal »
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

#### rvk

• Hero Member
• Posts: 4472
##### Re: "While not Recordset.EOF" does not work
« Reply #5 on: January 19, 2021, 03:17:50 pm »
can i Do Something like this?:

dbInsert.SQL.Text := dbCalc.SQL.TEXT
dbinsert.Append ...
...
dbinsert.clear

then repeat it if i need again
Yes. But you would need to open dbInsert, then dbInsert.Insert, commit and close the dbInsert.
Or you could use dbInsert.SQL.Text := 'INSERT INTO ...' etc for direct insertion and use dbInsert.ExecSQL.
In that case there doesn't have to be a complete dataset opened.

It depends on what and how you do things in the modal-form.
If you use DBEdit components there, it's easier to use dbInsert with a dataset (and open and close/commit).
If you don't use DBEdit components and just create an INSERT INTO statement you can use dbInsert.ExecSQL.

#### Weitentaaal

• Full Member
• Posts: 158
##### Re: "While not Recordset.EOF" does not work
« Reply #6 on: January 19, 2021, 03:25:46 pm »
I Always have to insert 1 Row with some attributes.
i did it like this before:

Code: Pascal  [Select][+][-]
1.    dbCalc.Edit; //or Append
2.    dbCalc.FieldByName('DATA').AsInteger := Abs(dbCalc.FieldByName('DATA').text.ToInteger);
3.    dbCalc.FieldByName('Zubehoer').AsString := ZubStr;
4.    If InZlAl = 0 then Begin
5.       dbCalc.FieldByName('ZlLumeng').AsInteger := StrToInt(TxLM.Text);
6.       dbCalc.FieldByName('ZlPa').AsInteger := StrToInt(TxBtDp.Text);
7.       dbCalc.FieldByName('AlLumeng').AsInteger := 0;
8.       dbCalc.FieldByName('AlPa').AsInteger := 0;
9.       dbCalc.FieldByName('ZPInn').AsString := Label34.Caption;
10.       dbCalc.FieldByName('ZPBod').AsString := Label35.Caption;
11.       dbCalc.FieldByName('ZlBed').AsInteger := CmbAccess.ItemIndex;
12.    End Else Begin
13.       dbCalc.FieldByName('AlLumeng').AsInteger := StrToInt(TxLM.Text);
14.       dbCalc.FieldByName('AlPa').AsInteger := StrToInt(TxBtDp.Text);
15.       dbCalc.FieldByName('ZlLumeng').AsInteger := 0;
16.       dbCalc.FieldByName('ZlPa').AsInteger := 0;
17.       dbCalc.FieldByName('APInn').AsString := Label34.Caption;
18.       dbCalc.FieldByName('APBod').AsString := Label35.Caption;
19.       dbCalc.FieldByName('AlBed').AsInteger := CmbAccess.ItemIndex;
20.    End;
21.
22.  dbCalc.Post;
24. TransWGR.Commit;
25.

But i had to Activate the Query after every Commit.

Edit: But isn't it a thing that if i use Commit, every Query Commits the changes (even if it hasn't any) ?

Edit2: Maybe i could even count and use Record Size to check at which point i was ? so i don't have to use dbCalc.next
« Last Edit: January 19, 2021, 03:53:52 pm by Weitentaaal »
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

#### rvk

• Hero Member
• Posts: 4472
##### Re: "While not Recordset.EOF" does not work
« Reply #7 on: January 19, 2021, 04:07:08 pm »
With a dbCalc.Edit you can use .Post and .Applyupdates without losing your position.
But don't commit because then the dataset is closed. You can set 'don't_close_on_commit' in the options but I'm not sure if it will retain the cursor position.

You can't use dbCalc.Insert of dbCalc.Append because then you WILL loose your position.

You can begin by removing the dbCalc.Commit and moving that to outside the loop of dbCalc.
(you don't need to commit the changes directly because you still have an active dataset)
Then you can see what it does with .Insert and .Append (I think you will loose the position).

That's why it might be best to use a separate dataset to edit and insert records.

BTW you have lots of FieldByName := StrToInt etc.
Why are you not working with db-aware components like TDBEdit.
In that case the component is automatically filled with the values and you only need to do a .Post.

Can you elaborate on why you have the loop?
Isn't it easier for a userinterface to show a DBGrid and the user clicks on a record or insert to edit or insert a record. In that case you don't need the loop and you could do it all in the same dataset (and it's easier to program).

#### Weitentaaal

• Full Member
• Posts: 158
##### Re: "While not Recordset.EOF" does not work
« Reply #8 on: January 19, 2021, 04:52:04 pm »
The Idea behind this:

I Have Some Forms wich Have Properties of Different Objects.

For Example 1 Form Calculates everything i need to know about a certain Object.

in the main Form i can construct a Device wich exists of this Object (Obj1 + Obj2 + Obj1 + Obj3 = 1 Device)

And btw. i Used CommitRetaining and kept the Position .. worked like i was expecting it to work
« Last Edit: January 19, 2021, 04:54:35 pm by Weitentaaal »
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

#### rvk

• Hero Member
• Posts: 4472
##### Re: "While not Recordset.EOF" does not work
« Reply #9 on: January 19, 2021, 04:59:13 pm »
And btw. i Used CommitRetaining and kept the Position .. worked like i was expecting it to work
Ok, then it works ok for dbCalc.Edit / commit
Did you also test with dbCalc.Append or .Insert / commit if the position is retained.
(I always thought the new position was the just inserted record in which case you are directly at the end of the dataset and skipped all other records)

#### Weitentaaal

• Full Member
• Posts: 158
##### Re: "While not Recordset.EOF" does not work
« Reply #10 on: January 19, 2021, 05:04:28 pm »
Yee ... worked too for Append but didn't tested it for insert bc i barely to never used insert. maybe its different with more than 2 records.

But anyway ... Thanks u solved the Problem again
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0

#### JanRoza

• Hero Member
• Posts: 618
##### Re: "While not Recordset.EOF" does not work
« Reply #11 on: January 19, 2021, 06:20:34 pm »
Wouldn't it be better to use CommitRetaining instead of plain Commit?
That way you don't need to set dbCalc to Active because it will remain active.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
Laz: Lazarus 2.0.12 FPC 3.2.0
CodeTyphon 7.4 FPC 3.3.1

#### Weitentaaal

• Full Member
• Posts: 158
##### Re: "While not Recordset.EOF" does not work
« Reply #12 on: January 20, 2021, 10:34:34 am »
i think u didn't read the last reply's. I used CommitRetaining and it work how it should . But thanks .. have a nice Day
Lazarus: 2.0.6 x86_64-win64-win32/win64
FPC-Version: 3.0.4
Compiler Version: 3.2.0