Using DBLookupComboBox as parameter for SQL SELECT statement.

[PANTLORD]

Hi there,

Hopefully this is an easy one. I am trying to pass the contents of a DBLookupComboBox as a criteria in an SQL select statement but it keeps giving me the error;

'QMain: Field 'Operator' is of an unknown type'

the code I am using is;

procedure TMain.EPwordExit(Sender: TObject);
var
MySQLString : string;
Operator : string;
begin

Operator := DBLCBOpName.Text;

MySQLString := 'SELECT OperatorInfo.Pword FROM OperatorInfo WHERE OperatorInfo.Name = :Operator';

QMain.Close; //QMain is a TQuery Component.
QMain.SQL.Clear;
QMain.SQL.Add(MySQLString);
QMain.ExecSQL;
QMain.open;
QMain.first;

EAcclev.Text := QMain.Fields.Fields[0].AsString;
end;

Thanks in advance
PL

 

[PANTLORD]

Hi there,

After messing about with this I tried using the line;

MySQLString := ('SELECT OperatorInfo.Pword FROM OperatorInfo WHERE OperatorInfo.Name = '+ DBLCBOpName.Text);

which will now compile but it seems to think that my BDLookupComboBox should be a field as if from a table and not a control off the form any ideas why?


Thanks
PL

 

[PANTLORD]

Back again,

Well I managed to get this thing working the code that seemed to do it was as follows;

MySQLString := ('SELECT Pword FROM OperatorInfo WHERE Name = '''+DBLCBOpName.Text+'''');

this seems pretty contorted i.e. 6*' and 2*+ for one form control but hey it works hope this is of use to someone.

All the best
PL

 

[jbpelletier]

Have u tried this one?
I havent work with querry using delphi in a years so im not sure at 100%. Correct me if im wrong..

MySQLString := 'SELECT OperatorInfo.Pword FROM OperatorInfo WHERE OperatorInfo.Name = ":Operator"';

Jb

 

[PANTLORD]

Hi there,

I tried that but it didn't seem to work think I did it the right way i.e. created a variable called operator and assigned it to my dropdownlist text, then passed this to the SQL statement.

Thanks anyway
PL

 

[TealWren]

I think this would work:

MySQLString := 'SELECT OperatorInfo.Pword FROM OperatorInfo WHERE OperatorInfo.Name = '+''''+Operator+'''';


I think putting a colon in front of it like :Operator means it's a parameter. Then to set a parameter in a query you need to say something like this.

QMain.ParamByName('Operator').AsString := Operator;
TealWren

 

[TimSNL]

The code from your first post should work if you poke a value into the qurey parameter like this. I find this is better than fussing with multiple commas '''' >:O>

QMain.Close; //QMain is a TQuery Component.
QMain.SQL.Clear;
QMain.SQL.Add(MySQLString);
QMain.Params[0].Value := Operator;
QMain.ExecSQL;
QMain.open;
QMain.first;

 

[PANTLORD]

Thanks for that,

I thought the excess of ' was a bit too much like overkill!!
PL

 

[svanels]

My sugestion, make a form with an editbox, a combobox, a button and a Tquery


In this case we need :
TQuery --> MyQuery
Editbox --> edtSelect
ComboBox --> cbSelect
BitButton --> bbtnSelect
dbGrid, navigator Statusbar etc.


You can use a parametrized query whith the following

Select CODE, NAME, LOCATION, TYPE from
MYTABLE MYTABLE
where CODE like :SearchString

In the params property of the query, define the datatype of SearchString as ftString


The ComboBox takes care of the search columns CODE, NAME etc.
The EditBox supplies the value using the variable SString.
The bitbutton fires the query


Global variables:

SString : string;
RecordsRetrieved: integer;

Populate the ComboBox with:
[/b]
CODE
NAME
LOCATION
NORMAL
SPECIAL[/b]



procedure TForm1.FormCreate(Sender: TObject);
begin
inherited;
SString :='%%';
cbSelect.ItemIndex := 0;
end;


procedure TForm1.edtSelectChange(Sender: TObject);
begin
inherited;
SString := '%' + edtSelect.Text + '%';
end;



procedure TForm1.bbtnSelectClick(Sender: TObject);
begin
inherited;
with Myquery do
begin
close;
SQL.Clear;
SQL.Add('SELECT CODE, NAME, LOCATION, TYPE');
SQL.Add('FROM MYTABLE MYTABLE');

//Check which column to search
case cbSelect.ItemIndex of
0: begin
SQL.Add('WHERE CODE LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
1: begin
SQL.Add('WHERE NAME LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
2: begin
SQL.Add('WHERE LOCATION LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
3: begin
SString := 'NORMAL';
edtSelect.Text:= 'NORMAL';
SQL.Add('WHERE TYPE = "NORMAL"');
end;
4: begin
SString := 'SPECIAL';
edtSelect.Text:= 'SPECIAL';
SQL.Add('WHERE TYPE = "SPECIAL"');
end;
End; {case}

SQL.Add('ORDER BY CODE, TYPE');
open;

end; { with }

RecordsRetrieved := MyQuery.RecordCount;
sbStatusBar.Panels[2].text:= IntToStr(RecordsRetrieved) + ' Records';
//update statusbar [/b

end;


We assume that the TYPE column can have only 2 possible values, so we do not need a

parameter at 3 & 4

Hope this helps you out



S. van Els
SAvanEls@cq-link.sr