[SOLVED] AfterScroll Select Query Failing

[guest48180]

This is driving me crazy. I have a simple SELECT statement that I can't make work. I have two records stored in each Table: customers and sites. But the SELECT statement in the AfterScroll event does nothing. It keeps the DBGrid2 stuck on showing the corresponding record for the first entry of DBGrid1. Edit1 shows me that:

Code: Pascal  [Select][+][-]

1. id := SQLQuery1.Fields[0].AsInteger;

is working, as it changes to reflect the cust_id of the two records in customers table.

Code: Pascal  [Select][+][-]

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, db, sqldb, sqlite3conn, Forms, Controls, Graphics, Dialogs,
9.   DBGrids, DBCtrls ,ComCtrls ,StdCtrls;
10.  
11. type
12.  
13.   { TForm1 }
14.  
15.   TForm1 = class(TForm)
16.     DataSource1: TDataSource;
17.     DataSource2 : TDataSource ;
18.     DBGrid1: TDBGrid;
19.     DBGrid2 : TDBGrid ;
20.     DBNavigator1: TDBNavigator;
21.     Edit1 : TEdit ;
22.     SQLQuery1 : TSQLQuery ;
23.     SQLQuery2 : TSQLQuery ;
24.     ToolBar1 : TToolBar ;
25.     ToolButton1 : TToolButton ;
26.     procedure DBNavigator1Click(Sender: TObject; Button: TDBNavButtonType);
27.     procedure FormActivate (Sender : TObject );
28.     procedure SQLQuery1AfterScroll (DataSet : TDataSet );
29.   private
30.  
31.   public
32.  
33.   end;
34.  
35. var
36.   Form1: TForm1;
37.  
38. implementation
39.  
40. {$R *.lfm}
41. uses
42.   Unit2;
43.  
44. { TForm1 }
45.  
46. procedure TForm1.DBNavigator1Click(Sender: TObject; Button: TDBNavButtonType);
47. begin
48.   case Button of
49.   nbPost:
50.     begin
51.       SQLQuery1.ApplyUpdates;
52.       DM1.SQLTransaction1.CommitRetaining;
53.     end;
54.   nbDelete:
55.     begin
56.       SQLQuery1.ApplyUpdates;
57.       DM1.SQLTransaction1.CommitRetaining;
58.     end;
59.  
60.   end;
61. end;
62.  
63. procedure TForm1.FormActivate (Sender : TObject );
64. begin
65.   DM1.SQLite3Connection1.DatabaseName := 'data.db';
66.   DM1.SQLite3Connection1.Params.Add('foreign_keys=1');;
67.   DM1.SQLite3Connection1.Connected := True;
68.   DM1.SQLTransaction1.Active := True;
69.  
70.   SQLQuery1.SQL.Text :=
71.     'CREATE TABLE IF NOT EXISTS customers(' +
72.     'cust_id INTEGER PRIMARY KEY AUTOINCREMENT, ' +
73.     'cust_name VARCHAR(30) NOT NULL)';
74.   SQLQuery1.ExecSQL;
75.   DM1.SQLTransaction1.Commit;
76.  
77.   SQLQuery1.SQL.Text :=
78.     'CREATE TABLE IF NOT EXISTS sites(' +
79.     'site_id INTEGER PRIMARY KEY AUTOINCREMENT, ' +
80.     'site_name VARCHAR(30) NOT NULL, ' +
81.     'site_cust INTEGER NOT NULL, ' +
82.     'FOREIGN KEY (site_cust) REFERENCES customers(cust_id) '  +
83.     'ON UPDATE CASCADE ON DELETE CASCADE)';
84.   SQLQuery1.ExecSQL;
85.   DM1.SQLTransaction1.Commit;
86.  
87.   SQLQuery1.SQL.Text := 'SELECT * FROM customers';
88.   SQLQuery1.Open;
89.  
90.   SQLQuery2.SQL.Text := 'SELECT * FROM sites';
91.   SQLQuery2.Open;
92.  
93.   DBGrid1.Columns[0].Visible := False;
94. end;
95.  
96. procedure TForm1.SQLQuery1AfterScroll (DataSet : TDataSet );
97. var
98.   id: Integer;
99. begin
100.   id := SQLQuery1.Fields[0].AsInteger;
101.   Edit1.Text := id.ToString();
102.  
103.   SQLQuery2.SQL.Text := 'SELECT * FROM sites WHERE site_cust = :cid';
104.   SQLQuery2.Params.ParamByName('cid').AsInteger := id;
105.   SQLQuery2.Open;
106. end;
107.  
108. end.
109.  

What am I doing wrong?

[guest48180]

Rookie mistake 

Code: Pascal  [Select][+][-]

1. procedure TForm1.SQLQuery1AfterScroll (DataSet : TDataSet );
2. var
3.   id: Integer;
4. begin
5.   id := SQLQuery1.Fields[0].AsInteger;
6.   Edit1.Text := id.ToString();
7.  
8.   SQLQuery2.Close; // HAVE TO FIRST CLOSE THE OLD QUERY TO RUN THE NEW ONE.
9.   SQLQuery2.SQL.Text := 'SELECT * FROM sites WHERE site_cust = :cid';
10.   SQLQuery2.Params.ParamByName('cid').AsInteger := id;
11.   SQLQuery2.Open;
12. end;