Problem with query on XE7

[kko]

Why this code work:

Code: Select all

procedure TForm1.Edit1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
begin
case RadioGroup1.ItemIndex of
0: UniQuery1.SQL.Text:='Select Name, address, Phone, email, status, uslogin from clients where Name like :text order by name';
1: UniQuery1.SQL.Text:='Select Name, address, Phone, email, status, uslogin from clients where Phone like :text order by name';
2: UniQuery1.SQL.Text:='Select Name, address, Phone, email, status, uslogin from clients where email like :text order by name';
end;
UniQuery1.ParamByName('text').Value:='%'+Edit1.text+'%';
UniQuery1.Execute;
end;

But this code doesn't work:

Code: Select all

procedure TForm1.Edit1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
begin
UniQuery1.SQL.Text:='Select Name, address, Phone, email, status, uslogin from clients where :st like :text order by name';
case RadioGroup1.ItemIndex of
0: UniQuery1.ParamByName('st').Value:='Name';
1: UniQuery1.ParamByName('st').Value:='Phone';
2: UniQuery1.ParamByName('st').Value:='email';
end;
UniQuery1.ParamByName('text').Value:='%'+Edit1.text+'%';
UniQuery1.Execute;
end;

What am i doing wrong? why it won't work?

[FCS]

Hello,

Maybe you should call UniQuery1.Prepare before execute it.
I'm afraid that you can't send field name as a parameter.
Read about macros in the documentation of UniDac.

Regards
Michal

[kko]

Thank you for your suggestion!
But it won't work with Prepare too

Code: Select all

procedure TForm1.Edit1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState); //поиск клиента
begin
UniQuery1.SQL.Text:='Select Name, address, Phone, email, status, uslogin from clients where :st like :text order by name';
case RadioGroup1.ItemIndex of
0: UniQuery1.ParamByName('st').Value:='Name';
1: UniQuery1.ParamByName('st').Value:='Phone';
2: UniQuery1.ParamByName('st').Value:='email';
end;
UniQuery1.ParamByName('text').Value:='%'+Edit1.text+'%';
UniQuery1.Prepare;
if UniQuery1.Prepared then UniQuery1.Execute;
end;

[azyk]

FCS is right. To change the field name in the WHERE clause of an SQL query, you can use UniDAC macros. For this, specify a string value with the name of the field in the TMacro.Value macro property. Your code with the mentioned modifications will look like the following:

Code: Select all

procedure TForm1.Edit1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
begin
UniQuery1.SQL.Text:='Select Name, address, Phone, email, status, uslogin from clients where &st like :text order by name';
case RadioGroup1.ItemIndex of
0: UniQuery1.MacroByName('st').Value:='Name';
1: UniQuery1.MacroByName('st').Value:='Phone';
2: UniQuery1.MacroByName('st').Value:='email';
end;
UniQuery1.ParamByName('text').Value:='%'+Edit1.text+'%';
UniQuery1.Execute;
end;

See more details about macros in our online documentation: https://www.devart.com/unidac/docs/#dev ... macros.htm

[kko]

azyk
Thank you! Now it works.

[azyk]

If any other questions come up, please contact us.