Problems in TSQLQuery

[workinghard]

Recently i've been working on calculating the land maintenance rate for the area where I live, and these are the line so far.
These are the codes

Code: [Select]

procedure TForm5.Button1Click(Sender: TObject);

  begin
  with SQLQuery1 do
  begin
    close;
    sql.clear;
    sql.add('INSERT INTO trans(Ad,date,total)VALUES((SELECT M.address FROM master AS M WHERE M.address = edAddress),CURRENT_TIMESTAMP,(SELECT((M.land*RM.landrate)+(M.home*RM.buildingrate))FROM Master AS M,maintenance AS RM WHERE M.Cluster = RM.Cluster AND M.Address=edAddress)');
    params.parambyname('Alamat').AsString := Alamat.text;
    open;
  end;
     
But, I get this error
SQLQuery1: Parameter "Address" not found

What does that mean?
The query code ( i know it's more than 255 lines, but i've shortened it already on the program )
Is the code wrong? or is there something else?

I had an edit box for address, and a tdbgridbox, and a calculate button in the form, and that's all (Although i will add it later if i can figure this out)

[ludob]

Code: [Select]

    sql.add('INSERT INTO trans(Ad,date,total)VALUES((SELECT M.address FROM master AS M WHERE M.address = edAddress),CURRENT_TIMESTAMP,(SELECT((M.land*RM.landrate)+(M.home*RM.buildingrate))FROM Master AS M,maintenance AS RM WHERE M.Cluster = RM.Cluster AND M.Address=edAddress)');
    params.parambyname('Alamat').AsString := Alamat.text;


That looks like a copy and paste and cross fingers in the hope it works.
1) you specify a parameter 'Alamat' but there is no :Alamat in the query
2) I assume edAddress is the address you get in the editbox. Then use it as a parameter in you query by entering it as :Address  and

Code: [Select]

params.parambyname('Address').AsString := edAddress.text3) The query itself is a disaster.  Rewrite it as

Code: [Select]

INSERT INTO trans (Ad,date,total)
SELECT M.address, CURRENT_TIMESTAMP, (M.land*RM.landrate)+(M.home*RM.buildingrate)
FROM Master AS M,maintenance AS RM
WHERE M.Cluster = RM.Cluster AND M.Address=:Address
So much easier to read, isn't it? For the syntax look here http://dev.mysql.com/doc/refman/5.1/en/insert-select.html.

[workinghard]

I'm really sorry, I've forgot to translate the word :"Alamat" to english
in the params, actually it should be address as well
still not working, even after i've fiddled with it

[BigChimp]

Don't let us guess, post the adjusted code

[workinghard]

Code: [Select]

close;

sql.clear;

sql.add ('INSERT INTO trans (Ad,date,total)
SELECT M.address, CURRENT_TIMESTAMP, (M.land*RM.landrate)+(M.home*RM.buildingrate)
FROM Master AS M,maintenance AS RM
WHERE M.Cluster = RM.Cluster AND M.Address=:Address');

params.parambyname('Address').AsString := edAddress.text;
open;

Here's the code...

[Arbee]

You should not use "open", but "execSQL";

[workinghard]

i've tried it, still raised the same error message:  "SQLQuery1: Parameter "Address" not found"

also, on the master table, the M.address value, M.land, M.building, M.Cluster and on the maintenance table, the RM.landrate, RM.buildingrate existed and not null

or maybe should i rewrite the code again and see what will happen?

[workinghard]

i've tried, but then it won't compile, saying that identifier idents no member "params"

[ludob]

The complete code should read:

Code: [Select]

  begin
  with SQLQuery1 do
  begin
    close;
    sql.clear;
    sql.add ('INSERT INTO trans (Ad,date,total) SELECT M.address, CURRENT_TIMESTAMP, (M.land*RM.landrate)+ M.home*RM.buildingrate) FROM Master AS M,maintenance AS RM WHERE M.Cluster = RM.Cluster AND M.Address=:Address');
    parambyname('Address').AsString := edAddress.text;
    ExecSQL;
  end;


[workinghard]

I've also tried doing it ludob, but still, it can't work
In any ways, it couldn't work...
could it be that lazarus has errors on this part? (I'm using 0.9.30 with fpc 2.4.2)
should i recompile?
 

[ludob]

A lot of changes have been made to sqldb since fpc 2.4.2. I suggest you upgrade to lazarus 0.9.30.4 that comes with fpc 2.6.0. You can find it here http://sourceforge.net/projects/lazarus/files/
 

[workinghard]

Even after updating, it still won't solve the problems....
what could possibly be wrong in the code?

[Arbee]

Have you tried without the "with"  (just clutching at straws, but you never know)?

Code: [Select]

SQLQuery1.close;
SQLQuery1.sql.clear;
SQLQuery1.sql.add ('INSERT INTO trans (Ad,date,total) SELECT M.address, CURRENT_TIMESTAMP, (M.land*RM.landrate)+ M.home*RM.buildingrate) FROM Master AS M,maintenance AS RM WHERE M.Cluster = RM.Cluster AND M.Address=:Address');
SQLQuery1.params.parambyname('Address').AsString := edAddress.text;
SQLQuery1.ExecSQL;


[BigChimp]

And if you can post the entire porgram or the entire unit, that might help, too...

What error message are you seeing on compilation? Still blabla idents no params or similar?

[workinghard]

yes, and even worse
i've updated the lazarus to the most recent version, and I started to get this error )in the attachment

Code for the main form

Code: [Select]

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, unit3, unit4, unit6;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private
    { private declarations }
 end;
var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  Form3 := TForm3.Create(nil);
  Form3.ShowModal;
  Form3.Release;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  application.terminate
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  Form4 := TForm4.Create(nil);
  Form4.ShowModal;
  Form4.Release;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
  Form6 := TForm6.create(nil);
  Form6.showmodal;
  form6.release;
end;

end.

Code for the second form

Code: [Select]

unit Unit3;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, mysql50conn, sqldb, db, dbf, FileUtil, Forms, Controls,
  Graphics, Dialogs, DbCtrls, DBGrids, ExtCtrls, StdCtrls;

type

  { TForm3 }

  TForm3 = class(TForm)
    Insert: TButton;
    cancel: TButton;
    ComboCluster: TComboBox;
    Datasource1: TDatasource;
    address: TLabeledEdit;
    Cluster: TLabel;
    lastrecordedwatercounter: TLabeledEdit;
    buildingsize: TLabeledEdit;
    landsize: TLabeledEdit;
    Name: TLabeledEdit;
    MySQL50Connection1: TMySQL50Connection;
    SQLQuery1: TSQLQuery;
    SQLQuery2: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure cancelClick(Sender: TObject);
    procedure InsertClick(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form3: TForm3;

implementation

{$R *.lfm}

{ TForm3 }

procedure TForm3.InsertClick(Sender: TObject);
begin
  SQLQuery1.insert;
  sqlquery1.fieldbyname('Name').Text:= Name.Text;
  sqlquery1.fieldbyname('address').Text:= Address.Text;
  SQLQuery1.Fieldbyname('cluster').text:= combocluster.text;
  sqlquery1.fieldbyname('Landsize').Text:= Landsize.Text;
  sqlquery1.fieldbyname('buildingsize').Text:= buildingsize.Text;
  sqlquery1.fieldbyname('lstrcrdwatercounter').Text:= lastrecordedwatercounter.Text;
  sqlquery1.post;
  sqlquery1.applyupdates;
  sqlquery1.refresh;

  showmessage('new user has been made');
  form3.close;
end;

procedure TForm3.cancelClick(Sender: TObject);
begin
  Form3.close;
end;


end.

Code for the 3rd form:

Code: [Select]

unit Unit5;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, mysql50conn, sqldb, DB, FileUtil, Forms, Controls,
  Graphics, Dialogs, StdCtrls, DBGrids;

type

  { TForm5 }

  TForm5 = class(TForm)
    edAlamat: TEdit;
    Button1: TButton;
    PemakaianAirTerakhir: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    MySQL50Connection1: TMySQL50Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form5: TForm5;

implementation

{$R *.lfm}

{ TForm5 }

procedure TForm5.Button1Click(Sender: TObject);

  begin
  with SQLQuery1 do
  begin
    close;
    sql.clear;
    sql.add('INSERT INTO trans(Ad,date,total)VALUES((SELECT M.address FROM master AS M WHERE M.address = edAddress),CURRENT_TIMESTAMP,(SELECT((M.land*RM.landrate)+(M.home*RM.buildingrate))FROM Master AS M,maintenance AS RM WHERE M.Cluster = RM.Cluster AND M.Address=edAddress)');
    params.parambyname('Address').AsString := Address.text;
    open;
  end;

end;
end.


those are the codes, so far
but the new error makes everything fail...
i can't compile anything, and i'm stuck on the edit only, cannot run tests anymore