Recent

Author Topic: [SOLVED] Database won't update  (Read 2062 times)

madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
[SOLVED] Database won't update
« on: April 01, 2015, 12:04:55 pm »
i have managed to create a database using SQLite. I can fill the database and change data.
All the changes are visibly seen, but the changed aren't permanent. Because when i close my application and thus the database none of the changes are saved.


How to do that?


This is the code i use for making the change in a subform.
Code: [Select]
procedure TForm_RefereeInformation.Button_Referee_OKClick(Sender: TObject);
var lkq: TSQLQuery;
    maxid: integer;
begin
  lkq := TSQLQuery.Create(self);
  lkq.DataBase:=Form_RefereeMain.SQLite3Connection_RefereeDB;


  if Referee_ID <> 0 then   // een bestaande wedstrijd wordt/is aangepast
    begin
      lkq.SQL.Clear;
      lkq.SQL.Text := 'SELECT * FROM tbl_Scheidsrechters ' +
                      'WHERE Scheids_ID=' + IntToStr(Referee_ID) + ';';
      lkq.Active:=True;
      lkq.Edit;
      lkq.FieldByName('Scheids_ID').AsInteger := Referee_ID;
    end
  else begin      // een nieuwe scheidsrechter aanmaken
    // Bepaal de laatse Scheids_ID
    lkq.SQL.Clear;
    lkq.SQL.Text := 'SELECT MAX(Scheids_ID) AS MaxID FROM tbl_Scheidsrechters;';
    lkq.Open;
    maxid := strtointdef(lkq.Fields[0].AsString,0);
    lkq.Close;
    lkq.SQL.Clear;
    lkq.SQL.Text := 'SELECT * FROM tbl_Scheidsrechters;';
    lkq.Open;
    lkq.Insert;
    lkq.FieldByName('Scheids_ID').AsInteger := maxid+1;
  end;  // if Referee_ID
  if lkq.Active = False then lkq.Active:=True;
  lkq.FieldByName('Voornaam').AsString := Edit_Voornaam.Text;
  lkq.FieldByName('Tussenvoegsel').AsString := Edit_Tussen.Text;
  lkq.FieldByName('Achternaam').AsString := Edit_Achternaam.Text;
  lkq.FieldByName('Adres').AsString := Edit_Adres.Text;
  lkq.FieldByName('Postcode').AsString := Edit_Postcode.Text;
  lkq.FieldByName('Woonplaats').AsString := Edit_Plaats.Text;
  lkq.FieldByName('Telefoon').AsString := Edit_Telefoon.Text;
  lkq.FieldByName('Mobiel').AsString := Edit_Mobiel.Text;
  if Edit_Geboortedatum.Text = '' then
    lkq.FieldByName('Geboortedatum').AsString := ''
  else
    lkq.FieldByName('Geboortedatum').AsDateTime := StrToDate (Edit_Geboortedatum.Text);
  lkq.FieldByName('Email').AsString := Edit_Email.Text;


  //Sla het op in de tabel
  lkq.Post;
  lkq.ApplyUpdates;
  // Query uit het geheugen halen.
  lkq.Active := False;
  lkq.Free;
  Form_RefereeMain.SQLQuery_GridReferees.Refresh;
  Close;
end;     // Button_Referee_OKClick


And this is the code i use to close the main form.
Code: [Select]

procedure TForm_RefereeMain.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  SQLQuery_GridLicenties.Close;
  SQLQuery_GridReferees.Close;
  SQLTransaction_RefereeDB.Active := False;
  SQLite3Connection_RefereeDB.Connected := False;
end;     // FormClose


« Last Edit: April 04, 2015, 12:11:19 am 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.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

rvk

  • Hero Member
  • *****
  • Posts: 3842
Re: Database won't update
« Reply #1 on: April 01, 2015, 12:08:23 pm »
From the other topic:
Quote
SQLTransaction1.CommitRetaining;
Don't need this :)
So you do need the commitRetaining-line  :)
Code: [Select]
SQLTransaction1.CommitRetaining;

When you don't commit a transaction it will be rolled back automatically at the end of your program. So you do need to manually issue a commit (or set the transaction to autocommit).

DonAlfredo

  • Hero Member
  • *****
  • Posts: 1171
Re: Database won't update
« Reply #2 on: April 01, 2015, 12:24:55 pm »
Please forgive me to add a sidenote to this discussion, but have you seen the mORMot-framework ?

The author and I have worked very hard to make mORMot fully FPC compatible.
It is now in use on Windows, Linux (i386 and ARM), OSX and Android.

It will abstract all your storage work. No low level SQL needed.
Datasets will easy your GUI.
RTTI used to ease implementation.
Switch database backend in one line of code.
Rest server/access in one line of code.

In your case (look at samples 1 and 2). Just define:
Code: [Select]
TSQLReferee=class(TSQLRecord)
protected
  fVoornaam:RawUTF8
  ......
published
  Voornaam:RawUTF8 read fVoornaam write fVoornaam;
  ......
end

and off you go !
Just my 2 cents.

http://synopse.info/fossil/wiki?name=SQLite3+Framework



madref

  • Hero Member
  • *****
  • Posts: 700
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Database won't update
« Reply #3 on: April 01, 2015, 01:16:40 pm »
Yep it seems i do ;)

And now it works!!
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave