[SOLVED] AfterScroll Select Query Failing
lazarus / 16-Databases

----------------------------------------------------------------------
guest48180 · 2019-07-26 15:48

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, db,
sqldb, sqlite3conn, Forms, Controls, Graphics, Dialogs,  DBGrids,
DBCtrls ,ComCtrls ,StdCtrls; type   { TForm1 }   TForm1 =
class(TForm)    DataSource1: TDataSource;    DataSource2 : TDataSource
;    DBGrid1: TDBGrid;    DBGrid2 : TDBGrid ;    DBNavigator1:
TDBNavigator;    Edit1 : TEdit ;    SQLQuery1 : TSQLQuery ;   
SQLQuery2 : TSQLQuery ;    ToolBar1 : TToolBar ;    ToolButton1 :
TToolButton ;    procedure DBNavigator1Click(Sender: TObject; Button:
TDBNavButtonType);    procedure FormActivate (Sender : TObject );   
procedure SQLQuery1AfterScroll (DataSet : TDataSet );  private  
public   end; var  Form1: TForm1; implementation {$R *.lfm}uses 
Unit2; { TForm1 } procedure TForm1.DBNavigator1Click(Sender: TObject;
Button: TDBNavButtonType);begin  case Button of  nbPost:    begin    
SQLQuery1.ApplyUpdates;      DM1.SQLTransaction1.CommitRetaining;   
end;  nbDelete:    begin      SQLQuery1.ApplyUpdates;     
DM1.SQLTransaction1.CommitRetaining;    end;   end;end; procedure
TForm1.FormActivate (Sender : TObject );begin 
DM1.SQLite3Connection1.DatabaseName := 'data.db'; 
DM1.SQLite3Connection1.Params.Add('foreign_keys=1');; 
DM1.SQLite3Connection1.Connected := True;  DM1.SQLTransaction1.Active
:= True;   SQLQuery1.SQL.Text :=    'CREATE TABLE IF NOT EXISTS
customers(' +    'cust_id INTEGER PRIMARY KEY AUTOINCREMENT, ' +   
'cust_name VARCHAR(30) NOT NULL)';  SQLQuery1.ExecSQL; 
DM1.SQLTransaction1.Commit;   SQLQuery1.SQL.Text :=    'CREATE TABLE
IF NOT EXISTS sites(' +    'site_id INTEGER PRIMARY KEY AUTOINCREMENT,
' +    'site_name VARCHAR(30) NOT NULL, ' +    'site_cust INTEGER NOT
NULL, ' +    'FOREIGN KEY (site_cust) REFERENCES customers(cust_id) '
 +    'ON UPDATE CASCADE ON DELETE CASCADE)';  SQLQuery1.ExecSQL; 
DM1.SQLTransaction1.Commit;   SQLQuery1.SQL.Text := 'SELECT * FROM
customers';  SQLQuery1.Open;   SQLQuery2.SQL.Text := 'SELECT * FROM
sites';  SQLQuery2.Open;   DBGrid1.Columns[0].Visible :=
False;end; procedure TForm1.SQLQuery1AfterScroll (DataSet : TDataSet
);var  id: Integer;begin  id := SQLQuery1.Fields[0].AsInteger; 
Edit1.Text := id.ToString();   SQLQuery2.SQL.Text := 'SELECT * FROM
sites WHERE site_cust = :cid'; 
SQLQuery2.Params.ParamByName('cid').AsInteger := id; 
SQLQuery2.Open;end; end. 
What am I doing wrong?

----------------------------------------------------------------------
guest48180 · 2019-07-26 21:13

Rookie mistake 

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.SQLQuery1AfterScroll (DataSet : TDataSet );var  id:
Integer;begin  id := SQLQuery1.Fields[0].AsInteger;  Edit1.Text :=
id.ToString();   SQLQuery2.Close; // HAVE TO FIRST CLOSE THE OLD QUERY
TO RUN THE NEW ONE.  SQLQuery2.SQL.Text := 'SELECT * FROM sites WHERE
site_cust = :cid';  SQLQuery2.Params.ParamByName('cid').AsInteger :=
id;  SQLQuery2.Open;end;