[Solved] DBGrid, SQLQuery1.ApplyUpdates Return error message

[TheLastCayen]

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

[rvk]

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]

Thank you rvk, You fixed my issue. 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.