Problems in TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
workinghard · 2012-03-27 08:07

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,(SELEC
T((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 · 2012-03-27 08:37

QuoteCode: [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 · 2012-03-27 09:25

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 · 2012-03-27 10:04

Don't let us guess, post the adjusted code

----------------------------------------------------------------------
workinghard · 2012-03-27 10:13

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 · 2012-03-27 10:14

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

----------------------------------------------------------------------
workinghard · 2012-03-27 10:21

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 · 2012-03-27 10:55

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

----------------------------------------------------------------------
ludob · 2012-03-27 11:20

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 · 2012-03-27 11:39

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 · 2012-03-27 12:04

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 · 2012-03-27 14:08

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

----------------------------------------------------------------------
Arbee · 2012-03-27 14:48

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 · 2012-03-27 14:58

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 · 2012-03-27 15:10

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,(SELEC
T((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

----------------------------------------------------------------------
BigChimp · 2012-03-27 15:37

Are you sure your fpc and Lazarus versions match?

----------------------------------------------------------------------
workinghard · 2012-03-27 15:58

well, i've downloaded the latest file and installed it, but before i
removed the old one first
then, when i tried running it, that happens

----------------------------------------------------------------------
workinghard · 2012-03-27 16:01

actually this got me wondering, what does the error mean?

EDIT: it's finally getting to wor again
I reinstalled the whole program

----------------------------------------------------------------------
ludob · 2012-03-27 16:13

Quote from: workinghard on March 27, 2012, 06:01:05 pmactually this
got me wondering, what does the error mean?
It means your unit3.lfm and/or unit5.lfm has a LogEvents property for
Mysql50Connection1 which is not recognised by the lazarus/fpc version
you have now. Edit the lfm files and just delete the line containing
LogEvents.

There are some other errors in you units:
- unit3:
Code: [Select]Name: TLabeledEdit; 'Name' is already defined for a
class and can't be re-used. Change to another identifier.

-unit5:
Code: [Select]params.parambyname('Address').AsString :=
Address.text;There is no TEdit named 'Address' and it should be
parambyname('Address').AsString, without the params.
And get rid of that awful SQL statement.

----------------------------------------------------------------------
workinghard · 2012-03-27 16:26

ahh... i see

also, about my nasty SQL statement, I'll fix it, and maybe we can see
the results 

EDIT:
After some tweaking, it still gave me the same error
the code:
Code: [Select]unit Unit4;

{$mode objfpc}{$H+}

interface

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

type

  { TForm3 }

  TForm3 = class(TForm)
    Button1: TButton;
    edAddress: TEdit;
    Edwater: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    MySQLconnection: TMySQL50Connection;
    querycalc: TSQLQuery;
    transcalc: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form3: TForm3;

implementation

{$R *.lfm}

{ TForm3 }

procedure TForm3.Button1Click(Sender: TObject);
begin
    querycalc.Close;
    querycalc.sql.Clear;
    querycalc.sql.add('
INSERT INTO trmt(Address,date,pay)
VALUES((
SELECT M.Address
FROM master AS M WHERE M.Address = edAddress),CURRENT_TIMESTAMP,(
SELECT((M.Land*RM.RLandRate)+(M.House*RM.BuildingRate))
FROM Master AS M,rmnt AS RM
WHERE M.Cluster = RM.Cluster AND M.Alamat=edAddress)');
    querycalc.params.parambyname('Address').AsString :=
edAddress.Text;
    querycalc.execSQL;
  end;



end.

----------------------------------------------------------------------
ludob · 2012-03-28 07:10

QuoteAfter some tweaking, it still gave me the same errorYou're still
make the same error. Replace
Code: [Select]querycalc.params.parambyname('Address').AsString :=
edAddress.Text; withCode:
[Select]querycalc.parambyname('Address').AsString := edAddress.Text;
I note that you got rid of the 'with SQLQuery1 do' which is the right
thing to do. Makes it much easier to detect errors and the code
completion works much better without 'with'.

----------------------------------------------------------------------
workinghard · 2012-03-28 09:54

Ok, i've changed the code
But it gave me another error saying
"error: identifier idents no member "parambyname""
That's why in the other units, i've stayed away from removing the
params before parambyname

----------------------------------------------------------------------
Arbee · 2012-03-28 10:03

The "params" is needed.  It is a pointer to a TParams object.  Only
with TParams is there a "parambyname".

In your last code fragment I don't see you using :Address as parameter
in the SQL statement.  It says "edAddress".

----------------------------------------------------------------------
workinghard · 2012-03-28 10:40

Quote from: Arbee on March 28, 2012, 12:03:01 pm
In your last code fragment I don't see you using :Address as parameter
in the SQL statement.  It says "edAddress".

I don't really get the meaning of this
So i should change the editbox (edAddress) to address as well?

----------------------------------------------------------------------
Arbee · 2012-03-28 10:58

No,  I mean that in your last code fragment the SQL code looks like
this:
Code: [Select]querycalc.sql.add('
INSERT INTO trmt(Address,date,pay)
VALUES((
SELECT M.Address
FROM master AS M WHERE M.Address = edAddress),CURRENT_TIMESTAMP,(
SELECT((M.Land*RM.RLandRate)+(M.House*RM.BuildingRate))
FROM Master AS M,rmnt AS RM
WHERE M.Cluster = RM.Cluster AND M.Alamat=edAddress)');

whereas its last line should read:
Code: [Select]WHERE M.Cluster = RM.Cluster AND M.Alamat= :Address)');

----------------------------------------------------------------------
Arbee · 2012-03-28 11:05

I also have the feeling your SQL statement is missing a closing
bracket.

----------------------------------------------------------------------
Arbee · 2012-03-28 11:18

And one more ..... looking at your SQL query, I think you could do it
with one SELECT in the INSERT:
Code: [Select]INSERT INTO trmt(Address,date,pay)
VALUES(
        (
                SELECT M.Address, CURRENT_TIMESTAMP, 
((M.Land*RM.RLandRate)+(M.House*RM.BuildingRate))
                FROM Master AS M,rmnt AS RM
                WHERE RM.Cluster = M.Cluster AND M.Address = :Address
        )
)
Not sure if that occasionally will give null results if the linke
between MASTER and rmnt is not correct, but you could give it a try.

----------------------------------------------------------------------
ludob · 2012-03-28 14:04

Quote from: Arbee on March 28, 2012, 12:03:01 pmThe "params" is
needed.  It is a pointer to a TParams object.  Only with TParams is
there a "parambyname".
In fpc 2.4.4 TCustomSQLQuery had already a public function
ParamByName(Const AParamName : String) : TParam;

That means that the OP is still using his old compiler. Aren't we all
just running in circles here? The same problems always coming back. OP
is not listening or doesn't understand what we are saying.