* * *

Author Topic: [Solved] DBGrid, SQLQuery1.ApplyUpdates Return error message  (Read 322 times)

TheLastCayen

  • New member
  • *
  • Posts: 16
[Solved] DBGrid, SQLQuery1.ApplyUpdates Return error message
« on: October 29, 2018, 01:13:16 pm »
Hi,

I am using

Windows 7 64b
Lazarus 1.8.4
FPC 3.0.4
sqlite-dll-win64-x64-3250200

I have a DBGrid and 3 DBEdit connect to my Table. I am able to Append new record But I can't update existing one . I am following this tutorial http://wiki.freepascal.org/SQLdb_Tutorial2 and I can't figure what I am missing. I am using the same Save Button to save the record I append and the record I try modify. The following 2 lines return the error message:  An Error Occurred while applying the updates in a record: DBConnection: No Update query specified and failed to generate one.(No fields for inclusion in where statement found)

Code: Pascal  [Select]
  1.   SQLQuery1.ApplyUpdates;
  2.   SQLTransaction1.CommitRetaining;
  3.  



My SQL:
Code: Pascal  [Select]
  1.         'CREATE TABLE IF NOT EXISTS "Software" (' +
  2.         '"ID" INTEGER PRIMARY KEY AUTOINCREMENT,' +
  3.         '"Name"  varchar(100) UNIQUE,' +
  4.         '"AddonPath" TEXT,' +
  5.         '"TempPath" TEXT);'  
  6.  

My Code:
Code: Pascal  [Select]
  1. Procedure TFSoftware.RefreshDB;
  2. begin
  3.   SQLQuery1.Close;
  4.   SQLQuery1.SQL.Text := 'SELECT Name,AddonPath,TempPath FROM Software;';
  5.   DBConnection.Connected := True;
  6.   SQLTransaction1.Active := True;
  7.   SQLQuery1.Open;
  8.  
  9.   DBGrid1.Options := DBGrid1.Options - [dgTitles, dgIndicator];
  10.   DBGrid1.ScrollBars:=ssAutoVertical;
  11.   DBGrid1.Columns[0].Width:=DBGrid1.Width-20;
  12.   DBGrid1.Columns[1].Visible:=False;
  13.   DBGrid1.Columns[2].Visible:=False;
  14. end;    
  15.  
  16. Procedure TFSoftware.CloseDB;
  17. begin
  18.   SQLQuery1.Close;
  19.   SQLTransaction1.Active := False;
  20.   DBConnection.Connected := False;
  21. end;  
  22.  
  23. procedure TFSoftware.FormActivate(Sender: TObject);
  24. begin
  25.   DBConnection.DatabaseName := Form1.DBNAME;
  26.   DBConnection.Transaction := SQLTransaction1;
  27.   SQLQuery1.DataBase := DBConnection;
  28.   DataSource1.DataSet := SQLQuery1;
  29.   DBGrid1.DataSource := DataSource1;
  30.  
  31.   DBEdit1.DataSource := DataSource1;
  32.   DBEdit2.DataSource := DataSource1;
  33.   DBEdit3.DataSource := DataSource1;
  34.   DBEdit1.DataField := 'Name';
  35.   DBEdit2.DataField := 'AddonPath';
  36.   DBEdit3.DataField := 'TempPath';
  37.  
  38.   RefreshDB;
  39. end;  
  40.  
  41. procedure TFSoftware.BTAddClick(Sender: TObject);
  42. begin
  43.   DBGrid1.DataSource.DataSet.AppendRecord([null]);  
  44. end;    
  45.  
  46. procedure TFSoftware.BTCancelClick(Sender: TObject);
  47. begin
  48.   RefreshDB;
  49. end;  
  50.  
  51. procedure TFSoftware.BTSaveClick(Sender: TObject);
  52. begin
  53.   SQLQuery1.ApplyUpdates;
  54.   SQLTransaction1.CommitRetaining;
  55. end;  
  56.  
  57.  

Anyone see what I am doing wrong?

Thank you


« Last Edit: October 29, 2018, 08:11:07 pm by TheLastCayen »

rvk

  • Hero Member
  • *****
  • Posts: 3467
Re: DBGrid, SQLQuery1.ApplyUpdates Return error message
« Reply #1 on: October 29, 2018, 01:23:08 pm »
The following 2 lines return the error message:  An Error Occurred while applying the updates in a record: DBConnection: No Update query specified and failed to generate one.(No fields for inclusion in where statement found)
What is the UpdateMode for the SQLQuery1?
If it's upWhereKeyOnly you need to add the ID in your SELECT. Otherwise SQLQuery1 doesn't know what ID it must update.

So you can either add the primary key to the SELECT (preferred) or set the UpdateMode to upWhereAll.
(You can hide or delete the ID-column from the DBGrid if you want.)

TheLastCayen

  • New member
  • *
  • Posts: 16
Re: DBGrid, SQLQuery1.ApplyUpdates Return error message
« Reply #2 on: October 29, 2018, 08:10:28 pm »
Thank you rvk, You fixed my issue. :D I am happy to know I have to add the ID. I think I lost a few hair on that issue  ;)

For future Reference, this is what I changed in my code:
Code: Pascal  [Select]
  1. Procedure TFSoftware.RefreshDB;
  2. begin
  3.   SQLQuery1.Close;
  4.   SQLQuery1.SQL.Text := 'SELECT * FROM Software;';
  5.   DBConnection.Connected := True;
  6.   SQLTransaction1.Active := True;
  7.   SQLQuery1.Open;
  8.  
  9.   DBGrid1.Options := DBGrid1.Options - [dgTitles, dgIndicator];
  10.   DBGrid1.ScrollBars:=ssAutoVertical;
  11.   DBGrid1.Columns[0].Visible:=False;
  12.   DBGrid1.Columns[1].Width:=DBGrid1.Width-20;
  13.   DBGrid1.Columns[2].Visible:=False;
  14.   DBGrid1.Columns[3].Visible:=False;
  15.  
  16. end;  
  17.  

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus