[SOLVED] Can't update record
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2015-03-31 11:41

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

----------------------------------------------------------------------
rvk · 2015-03-31 13:21

Probably because of this:
http://wiki.freepascal.org/Working_With_TSQLQuery#Cached_Updates
QuoteCached 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;

----------------------------------------------------------------------
madref · 2015-03-31 15:23

Doesn't work   .


Raises an error: No update query specified and failed to generate one
(no field for inclusion in Where statement found)

----------------------------------------------------------------------
fpcoder · 2015-03-31 15:40

You seem to miss the SQL update statement. Add it?

----------------------------------------------------------------------
rvk · 2015-03-31 15:45

You probably have Upda]"]>Blockedde := 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 Upda]"]>Blockedde := upWhereAll but I would
choose the primary key way.

See also: http://wiki.freepascal.org/SqlDBHowto#How_does_SqlDB_send_th
e_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.

Quote from: kapibara on March 31, 2015, 05:40:10 pmYou 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 · 2015-03-31 15:53

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

----------------------------------------------------------------------
rvk · 2015-03-31 15:55

Quote from: madref on March 31, 2015, 05:53:11 pmAnd 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.

----------------------------------------------------------------------
madref · 2015-03-31 16:27

Thanks for the suggestion

----------------------------------------------------------------------
madref · 2015-03-31 18:30

Since i added primary keys your option (RVK) also works. Will stick to
yours because it's easier to use (in the future).

----------------------------------------------------------------------
madref · 2015-03-31 18:33

Quote SQLTransaction1.CommitRetaining;Don't need this