A Delphi Dabbler here. Teaching myself. Using Delphi 2007 with SDAC version 220.127.116.11
purchased back in 2008 I believe.
Trying to write a small app that will allow me to search a SQL database of 30,000 records.
Search has to locate data in 8 different fields looking for a match on user's input which could
be a partial name, partial postcode, address etc.
I have the code written to produce the query which works in SQL Management Studio but not when passed
as a parameter in Delphi code.
This is the Procedure that creates the Query.
Code: Select all
procedure TMainForm.btnSearchClick(Sender: TObject); begin MSQuery1.SQL.Clear; StrToFind := edtSearchBox.Text; SQLQuery1 := 'SELECT CaseCode, Name, Add5, convert(varchar(12),'+ ' deb_date,103) as deb_date,'+ ' convert(varchar(12),Start,103) as Start,'+ ' convert(varchar(12), appdate,103) as appdate,'+ ' (select top 1 nominee from ips_casestaff as s'+ ' where s.casecode = ips_case.casecode and'+ ' role = 10 and nomineetype = 1) as ''IP'','+ ' (select top 1 nominee from ips_casestaff as s'+ ' where s.casecode = ips_case.CaseCode and'+ ' role = 30 and nomineetype = 1) as ''Manager'' from ips_case'+ ' where ( casecode like %'+StrToFind+'% or name like %'+StrToFind+ '% or add1 like %'+StrToFind+'% or add2 like %'+StrToFind+ '% or add3 like %'+StrToFind+'% or add4 like %'+StrToFind+ '% or add5 like %'+StrToFind+'% ) order by casecode'; MSQuery1.SQL.Add(SQLQuery1); ShowMessage(MSQuery1.SQL.Text); //Displays OK in ShowMessage window. MSQuery1.Execute; end;
Leaving out the left hand % wildcard the error is the same except it relates to the 'or' statement
and not the value of the StrToFind variable.
If I leave out both % wildcard characters it does this.......