Recent

Author Topic: [SOLVED] Dubble records  (Read 670 times)

madref

  • Hero Member
  • *****
  • Posts: 895
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
[SOLVED] Dubble records
« on: December 04, 2022, 12:34:27 pm »
I have a database and when I open the form the DBGrid is filled correctly.


But when I am opening the form for a second time I get the records twice and if I open the form for a third time...you guess it's 3 times the same records...


What am I doing wrong .
Code: Pascal  [Select][+][-]
  1. procedure TForm_Straffen_Keuze.FormShow(Sender: TObject);
  2. var cSQL: string;
  3. begin
  4.   // Tijdelijke tabel verwijderen als deze bestaat.
  5.   Form_Lint.Connect_RefereeDB.ExecuteDirect('DROP TABLE IF EXISTS tbl_Temp_Spelers');
  6.   Form_Lint.Trans_RefereeDB.Commit;
  7.   cSQL := '';
  8.   cSQL := 'CREATE TABLE tbl_Temp_Spelers (' +
  9.           'P_ID WORD NOT NULL PRIMARY KEY ASC, ' +
  10.           'P_Team VARCHAR(100), ' +
  11.           'P_Team_ID WORD, ' +
  12.           'P_Naam VARCHAR(100), ' +
  13.           'P_BondsNummer VARCHAR(10), ' +
  14.           'P_Penalty_Naam VARCHAR(10), ' +
  15.           'P_Min WORD' +
  16.           ')';
  17.   Form_Lint.Connect_RefereeDB.ExecuteDirect(cSQL);
  18.   Form_Lint.Trans_RefereeDB.Commit;
  19. //  Form_Lint.Connect_RefereeDB.ExecuteDirect('TRUNCATE TABLE IF EXISTS tbl_Temp_Spelers');
  20.   Form_Lint.Connect_RefereeDB.ExecuteDirect('DELETE FROM tbl_Temp_Spelers');
  21.   Form_Lint.Trans_RefereeDB.Commit;
  22.  
  23.  
  24.   Bepaal_Penalties;  // this sets the records in the table
  25. end;     // FormShow
  26.  
« Last Edit: December 05, 2022, 02:55:46 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.3.0 (rev main-2_3-2740-g4c53c90895) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: Dubble records
« Reply #1 on: December 04, 2022, 06:48:40 pm »
I‘m not even going to pretend to understand, why you
Drop a table, you create it, then you delete from this empty table….

As to your question: what‘s the recordcount (expected and after you pull the records)?
And what‘s the SELECT-query look like?
And do you clear the DBGrid resp. Query-Object when loading/showing the form?
Sounds like some cached Data
« Last Edit: December 04, 2022, 06:51:03 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

tt

  • Full Member
  • ***
  • Posts: 239
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Dubble records
« Reply #2 on: December 04, 2022, 07:00:37 pm »
I have a database and when I open the form the DBGrid is filled correctly.


But when I am opening the form for a second time I get the records twice and if I open the form for a third time...you guess it's 3 times the same records...


What am I doing wrong .
Code: Pascal  [Select][+][-]
  1. procedure TForm_Straffen_Keuze.FormShow(Sender: TObject);
  2. var cSQL: string;
  3. begin
  4.   // Tijdelijke tabel verwijderen als deze bestaat.
  5.   Form_Lint.Connect_RefereeDB.ExecuteDirect('DROP TABLE IF EXISTS tbl_Temp_Spelers');
  6.   Form_Lint.Trans_RefereeDB.Commit;
  7.   cSQL := '';
  8.   cSQL := 'CREATE TABLE tbl_Temp_Spelers (' +
  9.           'P_ID WORD NOT NULL PRIMARY KEY ASC, ' +
  10.           'P_Team VARCHAR(100), ' +
  11.           'P_Team_ID WORD, ' +
  12.           'P_Naam VARCHAR(100), ' +
  13.           'P_BondsNummer VARCHAR(10), ' +
  14.           'P_Penalty_Naam VARCHAR(10), ' +
  15.           'P_Min WORD' +
  16.           ')';
  17.   Form_Lint.Connect_RefereeDB.ExecuteDirect(cSQL);
  18.   Form_Lint.Trans_RefereeDB.Commit;
  19. //  Form_Lint.Connect_RefereeDB.ExecuteDirect('TRUNCATE TABLE IF EXISTS tbl_Temp_Spelers');
  20.   Form_Lint.Connect_RefereeDB.ExecuteDirect('DELETE FROM tbl_Temp_Spelers');
  21.   Form_Lint.Trans_RefereeDB.Commit;
  22.  
  23.  
  24.   Bepaal_Penalties;  // this sets the records in the table
  25. end;     // FormShow
  26.  

I should post all the code in order to understand all the operations done on the table and/or on the graphical compenent.

When you posted shows only

1. drop a table if exists
2. recreate the same table
3. delete all records from the table jsut created (there is no record so this operation does nothing, unless betwee line 18 and 20 someone else writes data in the table)
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

madref

  • Hero Member
  • *****
  • Posts: 895
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Dubble records
« Reply #3 on: December 04, 2022, 08:37:56 pm »
i am dropping the table because it's a temporary table and I want to keep my database as small as possible.
The delete was a failed attemp to clear the table.


But the problem keeps coming back.


And yes the DBGrid and the Query is cleared.
The Query is just
Code: SQL  [Select][+][-]
  1. SELECT * FROM tbl_Temp_Spelers


How do you mean cached data when everything should be cleared of data


Recordcount is the first time 3 then 6 (2nd time) and 9 the third time.
« Last Edit: December 04, 2022, 08:43:52 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.3.0 (rev main-2_3-2740-g4c53c90895) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

tt

  • Full Member
  • ***
  • Posts: 239
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Dubble records
« Reply #4 on: December 04, 2022, 09:34:30 pm »
i am dropping the table because it's a temporary table and I want to keep my database as small as possible.
The delete was a failed attemp to clear the table.


But the problem keeps coming back.


And yes the DBGrid and the Query is cleared.
The Query is just
Code: SQL  [Select][+][-]
  1. SELECT * FROM tbl_Temp_Spelers


How do you mean cached data when everything should be cleared of data


Recordcount is the first time 3 then 6 (2nd time) and 9 the third time.

Relationship between Form_Lint.Connect_RefereeDB wher you do the operations and Form_Lint.Trans_RefereeDB where you do the commits?
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

GetMem

  • Hero Member
  • *****
  • Posts: 3953
Re: Dubble records
« Reply #5 on: December 04, 2022, 09:36:11 pm »
@madref

Most likely table "tbl_Temp_Spelers" is in use for some reason and  the drop fails. Unfortunately method ExecuteDirect does not return any error message. As a quick test replace it with a query and use the ExecuteSQL method:
Code: Pascal  [Select][+][-]
  1. // Form_Lint.Connect_RefereeDB.ExecuteDirect('DROP TABLE IF EXISTS tbl_Temp_Spelers');
  2.    Form_Lint.qTest.SQL.Text := 'DROP TABLE IF EXISTS tbl_Temp_Spelers';
  3.    Form_Lint.qTest.ExecSQL; //this should raise an exception if the table is in use
  4.    Form_Lint.Trans_RefereeDB.Commit;

PS:  I inserted a new record in table A, without commiting it. Then I tried to drop the table. ExecuteDirect didn't return any error message, however ExecuteSQL returned the following message: "table A is in use".
 

Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: Dubble records
« Reply #6 on: December 05, 2022, 10:01:13 am »
@madref

Most likely table "tbl_Temp_Spelers" is in use for some reason and  the drop fails. Unfortunately method ExecuteDirect does not return any error message. As a quick test replace it with a query and use the ExecuteSQL method:
Code: Pascal  [Select][+][-]
  1. // Form_Lint.Connect_RefereeDB.ExecuteDirect('DROP TABLE IF EXISTS tbl_Temp_Spelers');
  2.    Form_Lint.qTest.SQL.Text := 'DROP TABLE IF EXISTS tbl_Temp_Spelers';
  3.    Form_Lint.qTest.ExecSQL; //this should raise an exception if the table is in use
  4.    Form_Lint.Trans_RefereeDB.Commit;

PS:  I inserted a new record in table A, without commiting it. Then I tried to drop the table. ExecuteDirect didn't return any error message, however ExecuteSQL returned the following message: "table A is in use".
Ahh.... yes, the "locked" table-problem.
OTOH, this smells like missing constraints on the table/columns, if you can insert the SAME records again, though with a different Primary key, since that one is AUTOINCREMENT (at least i think it's Autoincrement, since i've never seen the "ASC" in a Create Table).
What DBMS is that?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

madref

  • Hero Member
  • *****
  • Posts: 895
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Dubble records
« Reply #7 on: December 05, 2022, 02:55:35 pm »
Wow.....i have been so dumb %) .


I know now why I had double or triple records.
I created the records that I got from a variable that I declared but never cleared after I was finished with it.
So every time I added to this variable and the code just extracted it from this variable.


So all in al it was my own fault in my own coding that did it all.


Thanks everyone for the help

You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.3.0 (rev main-2_3-2740-g4c53c90895) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

tt

  • Full Member
  • ***
  • Posts: 239
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: Dubble records
« Reply #8 on: December 05, 2022, 03:01:26 pm »
So all in al it was my own fault in my own coding that did it all.

If you had posted your entire code maybe we could have quickly found the error.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 1604
Re: [SOLVED] Dubble records
« Reply #9 on: December 05, 2022, 03:58:39 pm »
Quote from: madref
I created the records that I got from a variable that I declared but never cleared after I was finished with it.
So every time I added to this variable and the code just extracted it from this variable.
Quote from: Zvoni
Sounds like some cached Data
:):)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018