Recent

Author Topic: [SOLVED] AfterScroll Select Query Failing  (Read 212 times)

guest48180

  • Guest
[SOLVED] AfterScroll Select Query Failing
« on: July 26, 2019, 05:48:32 pm »
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

  • Guest
Re: AfterScroll Select Query Failing
« Reply #1 on: July 26, 2019, 11:13:49 pm »
Rookie mistake  :D

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;