How to pass parameters to an ADOQuery object?

Question

I am using ADOQuery in Delphi 7 and Oracle. I am getting error while passing parameters to ADOQuery. I have used following line. Please help me to identify error.

ADOQuery.Sql.text:= 'select * from temp_table '+
        'where column1 in (select column from table2 where id=:id) and id=:id';
ADOQuery.Parameters.ParamByValue('id').value= 'abc';
ADOQuery.open;

when I open the query i will get following error:

Parameter object is improperly defined. Inconsistent or incomplete information is provided.

If i remove last condition of query then it works fine. ''select * from temp_table where column1 in (select column from table2 where id=:id)' — Nalu, Oct 13 '11 at 12:58

score 4 · Accepted Answer · answered Oct 13 '11 at 13:28

We have the same problem, we ended "masking" the class TParameters like this:

Declaration:

TMyParameter = class(TParameter)
private
  function GetAsValue: variant;
  Procedure SetAsValue(const Value: variant);
public
  property Value: variant read GetAsValue write SetAsValue;
end;

Implementation:

procedure TMyParameter.SetAsValue(const Value: variant);
var
  iPar: Integer;

begin
  for iPar:= 0 to Collection.Count - 1 do
    if (Name = TParameter(Collection.Items[iPar]).Name) then
      TParameter(Collection.Items[iPar]).Value:= Value;
end;

function TMyParameter.GetAsValue: variant;
begin
  Result:= inherited Value;
end;

And how to use:

TMyParameter(ADOQuery.Parameters.ParamByName('id')).AsValue:= 'abc';

I hope it helps.

score 1 · Answer 2 · answered Oct 13 '11 at 13:35

1

for i:=0 to ADOQuery.Parameters.Count-1 do
begin
  if ADOQuery.Parameters.Items[i].Name = 'id' then
    ADOQuery.Parameters.Items[i].Value := 'abc';
end;

answered Oct 13 '11 at 13:35

SimaWB

9,246
2
41
46

score 0 · Answer 3 · answered Nov 19 '21 at 00:55

In the SQL code declare a variable of the necessary type, assign to that variable the parameter; you will be able to use that variable as many times as necessary:

ADOQuery.Sql.text:= 'declare @param varchar(50); set @param = :id; '+
'select * from temp_table '+
'where column1 in (select column from table2 where id=@param) and id=@param';

ADOQuery.Parameters.ParamByValue('id').value= 'abc';
ADOQuery.open;

Regards

score 0 · Answer 4 · answered Oct 13 '11 at 13:12

0

You need to distinguish between the two id;s:

ADOQuery.Sql.text:= 'select * from temp_table a where column1 in (select column from table2 b where b.id=:id) and a.id=:id'; 
ADOQuery.Parameters.ParamByValue('id').value= 'abc'; 
ADOQuery.open;

answered Oct 13 '11 at 13:12

Pieter van Wyk

2,316
9
48
65

is not possible to use same id each? actually in my original query i have to pass 7 times this 'id' field. – Nalu Oct 13 '11 at 13:18
@naren: yes it is possible but the paramby... methods will only find the first. That is why the other two answers loop over all parameters and update all where the names match. – Marjan Venema Oct 13 '11 at 17:16

How to pass parameters to an ADOQuery object?

4 Answers4

Linked

Related