Recent

Author Topic: [SOLVED] Can't update record  (Read 8152 times)

madref

  • Hero Member
  • *****
  • Posts: 823
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
[SOLVED] Can't update record
« on: March 31, 2015, 01:41:57 pm »
I am using the following code to try and update a record.
But nothing updates What am i doing wrong.
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.Open;
      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 := lkq.Fields[0].AsInteger;
    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
  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 := Null
  else
    lkq.FieldByName('Geboortedatum').AsDateTime := StrToDate (Edit_Geboortedatum.Text);
  lkq.FieldByName('Email').AsString := Edit_Email.Text;

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

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

rvk

  • Hero Member
  • *****
  • Posts: 4296
Re: Can't update record
« Reply #1 on: March 31, 2015, 03:21:51 pm »
Probably because of this:
http://wiki.freepascal.org/Working_With_TSQLQuery#Cached_Updates
Quote
Cached Updates
The TSQLQuery component caches all updates. That is, the updates are not sent immediately to the database, but are kept in memory till the ApplyUpdates method is called. At that point, the updates will be transformed to SQL update statements, and will be applied to the database. If you do not call ApplyUpdates, the database will not be updated with the local changes.

I'm not seeing any ApplyUpdate in your code. So put a lkq.ApplyUpdate; after your lkq.Post;
And I'm not sure if you also need a lkq.Transaction.CommitRetaining after it all.
(And I'm also not sure you need the lkq.UpdateRecord; )

So try:
Code: [Select]
  //Sla het op in de tabel
  lkq.Post;
  lkq.ApplyUpdates;
  SQLTransaction1.CommitRetaining;
« Last Edit: March 31, 2015, 03:26:30 pm by rvk »

madref

  • Hero Member
  • *****
  • Posts: 823
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't update record
« Reply #2 on: March 31, 2015, 05:23:09 pm »
Doesn't work  %) .


Raises an error: No update query specified and failed to generate one (no field for inclusion in Where statement found)
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.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

kapibara

  • Hero Member
  • *****
  • Posts: 528
Re: Can't update record
« Reply #3 on: March 31, 2015, 05:40:10 pm »
You seem to miss the SQL update statement. Add it?
Lazarus trunk / fpc 3.0.4 / Debian 10 - 64 bit

rvk

  • Hero Member
  • *****
  • Posts: 4296
Re: Can't update record
« Reply #4 on: March 31, 2015, 05:45:52 pm »
You probably have UpdateMode := upWhereKeyOnly on your lkq.

This will use your primary key to update your table. BUT... do you have a primary key on tbl_Scheidsrechters? (Seeing at your other post I don't think you have a primary key).

Without primary key Lazarus can't create an update-statement.
Best is to create a primary key on Scheids_ID.
Otherwise you can set UpdateMode := upWhereAll but I would choose the primary key way.

See also: http://wiki.freepascal.org/SqlDBHowto#How_does_SqlDB_send_the_changes_to_the_database_server.3F

One small note: I would also change this line:
Code: [Select]
maxid := lkq.Fields[0].AsInteger;
to this:
Code: [Select]
maxid := strtointdef(lkq.Fields[0].AsString,0);
SQLite3 seems to stumble on AsInteger when there is no previous record found.

You seem to miss the SQL update statement. Add it?
No need to do this manually with such a simple SELECT statement. the SQL-library of Lazarus will do this for you.

madref

  • Hero Member
  • *****
  • Posts: 823
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't update record
« Reply #5 on: March 31, 2015, 05:53:11 pm »
I googled a bit and changed it to:
Code: [Select]

  cSQL := 'UPDATE tbl_Scheidsrechters SET ' +
          'Voornaam=' + QuotedStr(Edit_Voornaam.Text) + ', ' +
          'Tussenvoegsel=' + QuotedStr(Edit_Tussen.Text) + ', ' +
          'Achternaam=' + QuotedStr(Edit_Achternaam.Text) + ', ' +
          'Adres=' + QuotedStr(Edit_Adres.Text) + ', ' +
          'Postcode=' + QuotedStr(Edit_Postcode.Text) + ', ' +
          'Woonplaats=' + QuotedStr(Edit_Plaats.Text) + ', ' +
          'Telefoon=' + QuotedStr(Edit_Telefoon.Text) + ', ' +
          'Mobiel=' + QuotedStr(Edit_Mobiel.Text) + ', ' +
//          '=' + QuotedStr(Edit_.Text) + ', ' +
          'Email=' + QuotedStr(Edit_Email.Text) + ' ' +
          'WHERE Scheids_ID=' + IntToStr(Referee_ID) + ';';
  showmessage (cSQL);
  lkq.Active:=False;
  lkq.SQL.Clear;
  lkq.SQL.Text := cSQL;
  lkq.ExecSQL;
And this 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.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

rvk

  • Hero Member
  • *****
  • Posts: 4296
Re: Can't update record
« Reply #6 on: March 31, 2015, 05:55:41 pm »
And this works
Over-complicated and not necessary.
But ok, if it works for you and you're happy... :)


Doing this in the other topic would have solved your problem (I checked):
Code: [Select]
  cSQL := cSQL + 'CREATE TABLE tbl_Scheidsrechters ';
  cSQL := cSQL + '(';
  cSQL := cSQL + 'Scheids_ID Word PRIMARY KEY ASC,';
All your tables should have primary keys on their _ID fields.
« Last Edit: March 31, 2015, 06:09:13 pm by rvk »

madref

  • Hero Member
  • *****
  • Posts: 823
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't update record
« Reply #7 on: March 31, 2015, 06:27:47 pm »
Thanks for the suggestion
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.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

madref

  • Hero Member
  • *****
  • Posts: 823
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't update record
« Reply #8 on: March 31, 2015, 08:30:52 pm »
Since i added primary keys your option (RVK) also works. Will stick to yours because it's easier to use (in the future).
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.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

madref

  • Hero Member
  • *****
  • Posts: 823
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't update record
« Reply #9 on: March 31, 2015, 08:33:38 pm »
Quote
SQLTransaction1.CommitRetaining;
Don't need this :)
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.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

 

TinyPortal © 2005-2018