Recent

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

Weitentaaal

  • Hero Member
  • *****
  • Posts: 503
  • Weitental is a very beautiful garbage depot.
"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.12 x86_64-win64-win32/win64
Compiler Version: 3.2.2

rvk

  • Hero Member
  • *****
  • Posts: 6056
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

  • Hero Member
  • *****
  • Posts: 503
  • Weitental is a very beautiful garbage depot.
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.12 x86_64-win64-win32/win64
Compiler Version: 3.2.2

rvk

  • Hero Member
  • *****
  • Posts: 6056
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

  • Hero Member
  • *****
  • Posts: 503
  • Weitental is a very beautiful garbage depot.
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.12 x86_64-win64-win32/win64
Compiler Version: 3.2.2

rvk

  • Hero Member
  • *****
  • Posts: 6056
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

  • Hero Member
  • *****
  • Posts: 503
  • Weitental is a very beautiful garbage depot.
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;
  23.  dbCalc.ApplyUpdates;
  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.12 x86_64-win64-win32/win64
Compiler Version: 3.2.2

rvk

  • Hero Member
  • *****
  • Posts: 6056
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

  • Hero Member
  • *****
  • Posts: 503
  • Weitental is a very beautiful garbage depot.
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.12 x86_64-win64-win32/win64
Compiler Version: 3.2.2

rvk

  • Hero Member
  • *****
  • Posts: 6056
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

  • Hero Member
  • *****
  • Posts: 503
  • Weitental is a very beautiful garbage depot.
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 :D
Lazarus: 2.0.12 x86_64-win64-win32/win64
Compiler Version: 3.2.2

JanRoza

  • Hero Member
  • *****
  • Posts: 672
    • http://www.silentwings.nl
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)
       Lazarus 3.2 FPC 3.2.2
       CodeTyphon 8.40 FPC 3.3.1

Weitentaaal

  • Hero Member
  • *****
  • Posts: 503
  • Weitental is a very beautiful garbage depot.
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.12 x86_64-win64-win32/win64
Compiler Version: 3.2.2

 

TinyPortal © 2005-2018