[SOLVED] Dubble records lazarus / 16-Databases ---------------------------------------------------------------------- madref · 2022-12-04 11:34 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm_Straffen_Keuze.FormShow(Sender: TObject);var cSQL: string;begin  // Tijdelijke tabel verwijderen als deze bestaat.  Form_Lint.Connect_RefereeDB.ExecuteDirect('DROP TABLE IF EXISTS tbl_Temp_Spelers');  Form_Lint.Trans_RefereeDB.Commit;  cSQL := '';  cSQL := 'CREATE TABLE tbl_Temp_Spelers (' +          'P_ID WORD NOT NULL PRIMARY KEY ASC, ' +          'P_Team VARCHAR(100), ' +          'P_Team_ID WORD, ' +          'P_Naam VARCHAR(100), ' +          'P_BondsNummer VARCHAR(10), ' +          'P_Penalty_Naam VARCHAR(10), ' +          'P_Min WORD' +          ')';  Form_Lint.Connect_RefereeDB.ExecuteDirect(cSQL);  Form_Lint.Trans_RefereeDB.Commit;//  Form_Lint.Connect_RefereeDB.ExecuteDirect('TRUNCATE TABLE IF EXISTS tbl_Temp_Spelers');  Form_Lint.Connect_RefereeDB.ExecuteDirect('DELETE FROM tbl_Temp_Spelers');  Form_Lint.Trans_RefereeDB.Commit;    Bepaal_Penalties;  // this sets the records in the tableend;     // FormShow ---------------------------------------------------------------------- Zvoni · 2022-12-04 17:48 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 ---------------------------------------------------------------------- Чебурашка · 2022-12-04 18:00 Quote from: madref on December 04, 2022, 12:34:27 pmI 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm_Straffen_Keuze.FormShow(Sender: TObject);var cSQL: string;begin  // Tijdelijke tabel verwijderen als deze bestaat.  Form_Lint.Connect_RefereeDB.ExecuteDirect('DROP TABLE IF EXISTS tbl_Temp_Spelers');  Form_Lint.Trans_RefereeDB.Commit;  cSQL := '';  cSQL := 'CREATE TABLE tbl_Temp_Spelers (' +          'P_ID WORD NOT NULL PRIMARY KEY ASC, ' +          'P_Team VARCHAR(100), ' +          'P_Team_ID WORD, ' +          'P_Naam VARCHAR(100), ' +          'P_BondsNummer VARCHAR(10), ' +          'P_Penalty_Naam VARCHAR(10), ' +          'P_Min WORD' +          ')';  Form_Lint.Connect_RefereeDB.ExecuteDirect(cSQL);  Form_Lint.Trans_RefereeDB.Commit;//  Form_Lint.Connect_RefereeDB.ExecuteDirect('TRUNCATE TABLE IF EXISTS tbl_Temp_Spelers');  Form_Lint.Connect_RefereeDB.ExecuteDirect('DELETE FROM tbl_Temp_Spelers');  Form_Lint.Trans_RefereeDB.Commit;    Bepaal_Penalties;  // this sets the records in the tableend;     // FormShow  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) ---------------------------------------------------------------------- madref · 2022-12-04 19:37 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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. ---------------------------------------------------------------------- Чебурашка · 2022-12-04 20:34 Quote from: madref on December 04, 2022, 08:37:56 pmi 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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? ---------------------------------------------------------------------- balazsszekely · 2022-12-04 20:36 @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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i // Form_Lint.Connect_RefereeDB.ExecuteDirect('DROP TABLE IF EXISTS tbl_Temp_Spelers');   Form_Lint.qTest.SQL.Text := 'DROP TABLE IF EXISTS tbl_Temp_Spelers';   Form_Lint.qTest.ExecSQL; //this should raise an exception if the table is in use   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 · 2022-12-05 09:01 Quote from: GetMem 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i // Form_Lint.Connect_RefereeDB.ExecuteDirect('DROP TABLE IF EXISTS tbl_Temp_Spelers');   Form_Lint.qTest.SQL.Text := 'DROP TABLE IF EXISTS tbl_Temp_Spelers';   Form_Lint.qTest.ExecSQL; //this should raise an exception if the table is in use   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? ---------------------------------------------------------------------- madref · 2022-12-05 13:55 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 ---------------------------------------------------------------------- Чебурашка · 2022-12-05 14:01 Quote from: madref on December 05, 2022, 02:55:35 pmSo 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. ---------------------------------------------------------------------- Zvoni · 2022-12-05 14:58 Quote from: madrefI 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: ZvoniSounds like some cached Data