Help with Query Parameters

[sytech]

Hi Folks

I need a query to return data from jobtable I have tried code below from FAQ area but then found out that ParamByName doesnt work with ADOquery How do I change this to Parameters or is there an easier way I have tried filering with pos but its case sensitive so I dont get all the data I cant change the underlying MS Access tables because their distributed.

Any Ideas Would be greatly appreciated.


procedure TJobStatus.FormCreate(Sender: TObject);

begin
inherited;
SString:='%%';
cbselect.ItemIndex :=0;
end;

procedure TJobStatus.edtselectChange(Sender: TObject);
begin
inherited;
sstring :='%' + edtselect.text + '%';

end;

procedure TJobStatus.bbtnselectClick(Sender: TObject);
begin
inherited;
with jobstatus do
begin
close;
datamodule2.JobStatus.SQL.clear;
datamodule2.JobStatus.sql.add('SELECT Jobno,Customer,ref, notes');
datamodule2.JobStatus.sql.add('from Jobstable');

case cbselect.ItemIndex of
0: begin
datamodule2.JobStatus.sql.add ('where jobno like :searchstring');
Parambyname('searchstring').asstring:=sstring;
end;
1: begin
datamodule2.JobStatus.sql.add ('where Customer like :searchstring');
Parambyname('searchstring').asstring:=sstring;
end;
2: begin
datamodule2.JobStatus.sql.add ('where Ref like :searchstring');
Parambyname('searchstring').asstring:=sstring;
end;
3: begin
datamodule2.JobStatus.sql.add ('where notes like :searchstring');
Parambyname('searchstring').asstring:=sstring;
end;
end;

Graham

 

[DaZZleD]

you could simply use

case cbselect.ItemIndex of
0: begin
datamodule2.JobStatus.sql.add ('where jobno like ' + sstring);
end;
1: begin
datamodule2.JobStatus.sql.add ('where Customer like ' + sstring);
end;
2: begin
datamodule2.JobStatus.sql.add ('where Ref like ' + sstring);
end;
3: begin
datamodule2.JobStatus.sql.add ('where notes like ' + sstring);
end;
end;

 

[sytech]

Thanks D
Your suggestion almost there two problems I have now are the % wildcard doesnt do anything it just attaches to the end of the text also it is case sensitive if I Remove the % wildcard from the code and enter the full text data it returns data otherwise gives error message Syntax error in Jobno Like XXX%
I have tried other Wildcards Like * etc and enclosing in brackets non appear to work.

Any Ideas are Appreciated.

Graham

 

[LucieLastic]

hi

You need QuotedStr()

eg
:
3: begin
datamodule2.JobStatus.sql.add ('where notes like ' + quotedStr(sstring));
end;

lou

 

[LucieLastic]

You can also get round the case sensitivity problem. I'm not sure what the function is in Access, but try one of these: Upper, UpperCase, ToUpper

example:

3: begin
datamodule2.JobStatus.sql.add ('where Upper(notes) like ' + QuotedStr(UpperCase(sstring)));
end;

lou

 

[svanels]

The % wildcard is used in the SQL sintax for searching fragments of the string entered.
'%on%' will return the records containing 'tyson', 'telephone' etc.

Use the quotedstring or: "

Steven van Els
SAvanEls@cq-link.sr

 

[sytech]

Thanks All

Got it Working at last for Info in Access it is UCase also need the % wildcard to work correctly

Thanks again

Graham