Hi

I have the following code:

1. SQL Query:
select * from model where make_description like '%'+{make_description=''}+'%'

2. EWB Code:
field "make_description" is in table "model"
field "make_description" is in table "make"

After selecting make_description it is assigned to the make string
and then passed to the code below for filtering.

So the  Database.LoadRows should only show models that belong to the make.

 fNewCar_M.WL_Model.Close;
 fNewCar_M.WL_Model.Params.Clear;
 fNewCar_M.WL_Model.Params.Add('make_description =''%' + make + '%''');
 Database.LoadRows(fNewCar_M.WL_Model);

The Database.LoadRows is returning all rows (I think meaning that there is a problem with the params declarations)

So I can't work out what I am doing wrong here..

Many thanks


Kamran

Hi Kamran

Define the SQL query as

select * from model where make_description like {'%' + make_description=' + '%'} OR alternatively
select * from model where make_description like {make_description=' + '%'}


The QuotedStr is a useful function which could be used in the following line.

 fNewCar_M.WL_Model.Params.Add('make_description =''%' + make + '%''');

This would become

fNewCar_M.WL_Model.Params.Add('make_description = ' + QuotedStr('%' + make + '%'));

which makes it easier to read.


Richard

I got that wrong.  How about this for the SQL?

select * from model where make_description like {make_description = '%'}

Richard

Hi Richard

That works for me !

The learning never stops.  

Much appreciated.

Take care

Kamran

Richard Harding wrote:

I got that wrong.  How about this for the SQL?

select * from model where make_description like {make_description = '%'}

Richard

Kamran,

>    fNewCar_M.WL_Model.Params.Add('make_description =''%' + make + '%''');

Adding the param like this is correct. Since the placeholders are are
already used in the param, they are wrong (doubled) in the query:

So you should change your query definition it from

select * from model where make_description like
'%'+{make_description=''}+'%'

to

select * from model where make_description like {make_description=''}

That should work.

Uli

kamran wrote:

> field "make_description" is in table "model"
> field "make_description" is in table "make"

Not sure if I understand this fully, but it always struck me as a
newbie to SQL that people seemed to spend a lot of time fixing issues
with the same column names (fields) in different tables. Plainly there
are often links between them, but wouldn't it be better if you could
guarantee no problems, or if there are to be problems, that you know
immediately?

It seemed obvious to me that each table should be two words with unique
initials. So "ModelData" and "MakeStore" perhaps. Then each column gets
a prefix of the table initials. So mdMakeDescription and
msMakeDescription. It clarifies JOINs, so JOIN MakeStore on (mdModelID
= msModelID). Copy and paste and join to the BarneyRubble table, and if
you don't fix it to be brModelID, it will instantly fail and tell you
you made a mistake.

Just a thought - I'm not saying this is the only true way, just one
that I found useful to stop issues where you have the same column name
and it complicates things.

--

Matthew Jones

Hi

Thanks to Richard, Uli and Matthew.

It all works now as intended.

Cheers.

Kamran