Error when passing parameters to TSQLQuery object

[georges]

Hello

I am getting an error when passing parameters to a TSQLQuery object.

The following code works without parameters:

//--------- begin code
begin
   Form2.SQLQuery2.SQL.Clear;
   Form2.SQLQuery2.SQL.Add('update USR_CUST_FIN_CHRG');
   Form2.SQLQuery2.SQL.Add('set AMT_PAST_DUE = 110');
   Form2.SQLQuery2.SQL.Add('where CUST_NO = ' + '''1435''');

   Form2.SQLQuery2.ExecSQL;
   Form2.SQLTransaction2.Commit;
end
//--------- end code



I change the code to work with parameters such as below:

//--------- begin code
var
   v_cust_no: string[15];
   v_amt_past_due: real;

begin
   v_cust_no:= '1435';
   v_amt_past_due:= 110;

   Form2.SQLQuery2.SQL.Clear;
   Form2.SQLQuery2.SQL.Add('update USR_CUST_FIN_CHRG');
   Form2.SQLQuery2.SQL.Add('set AMT_PAST_DUE = :amt_past_due');
   Form2.SQLQuery2.SQL.Add('where CUST_NO = :cust_no');

   Form2.SQLQuery2.ParseSQL := true;
   Form2.SQLQuery2.Params.ParamByName('amt_past_due').AsFloat := v_amt_past_due;
   Form2.SQLQuery2.Params.ParamByName('cust_no').AsString := v_cust_no;

   Form2.SQLQuery2.ExecSQL;
   Form2.SQLTransaction2.Commit;
end
//--------- end code


When I run the code, I get the following error:

Project project1 raised an exception class 'EDatabaseError' with message:
Parameter "amt_past_due" not found

At address 54ADAC

What am I doing wrong?

Regards,

[LacaK]

What value has Form2.SQLQuery2.ParamCheck ?
(is it True ?)

[georges]

Hello LacaK

Thanks for your input.   The value of Form2.SQLQuery2.ParamCheck is true.

Regards,

[rvk]

Can you change your code like this and tell us the result?
(adding line 2-4)

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

1.   Form2.SQLQuery2.ParseSQL := true;
2.   Showmessage(inttostr(Form2.SQLQuery2.Params.Count));
3.   if Form2.SQLQuery2.Params.Count > 0 then
4.     Showmessage(Form2.SQLQuery2.Params[0].Name);

[georges]

Hi Rvk

The result for line 2 "Showmessage(inttostr(Form2.SQLQuery2.Params.Count));" is 0. 

I also added the code "ShowMessage(Form2.SQLQuery2.SQL.Text);" and the result was

update USR_CUST_FIN_CHRG
set AMT_PAST_DUE = :amt_past_due
where CUST_NO = :cust_no


I can't see why the parameter count is zero.

Regards,

[rvk]

Yeah, the only reason I can think of is what LacaK already suggested.

Could you try adding ParamCheck := true before setting the SQL just to be sure.

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

1.   Form2.SQLQuery2.ParamCheck := true;
2.   Form2.SQLQuery2.SQL.Clear;
3.   Form2.SQLQuery2.SQL.Add('update USR_CUST_FIN_CHRG');
4.   Form2.SQLQuery2.SQL.Add('set AMT_PAST_DUE = :amt_past_due');
5.   Form2.SQLQuery2.SQL.Add('where CUST_NO = :cust_no');
6.   Form2.SQLQuery2.ParseSQL := true;
7.   Showmessage(inttostr(Form2.SQLQuery2.Params.Count));
8.    if Form2.SQLQuery2.Params.Count > 0 then
9.      Showmessage(Form2.SQLQuery2.Params[0].Name);

Otherwise you would need to make a reproducible test-project.

[georges]

Hello Rvk

I made a new project from scratch and got it to work.   

Thanks for your assistance.   Also, thanks to LacaK.

Regards,