[SOLVED] Can't update record

[madref]

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]

Probably because of this:
http://wiki.freepascal.org/Working_With_TSQLQuery#Cached_Updates

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;


[madref]

Doesn't work  .


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

[kapibara]

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

[rvk]

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]

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]

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.

[madref]

Thanks for the suggestion

[madref]

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

[madref]

SQLTransaction1.CommitRetaining;

Don't need this