Query based on 2 variables

mierlp · Post by **mierlp** » Wed 26 Apr 2006 13:42

hi,

I'm new to MySQL/MyDAC and have a problem on how to
build a user query based on selections he makes using
2 dblookupcomboboxes. The listsource from each dblookupcombo
points to a ohter database.

The user selects in every dblookupcombobox a records to create
his selections. When he hits the ''Run'' button, the query has
to be executed.

Greetz Peter

Antaeus · Post by **Antaeus** » Thu 27 Apr 2006 09:08

To use tables from different databases you should put database name before table name with point as delimiter. For example:
select tb1.*, tb2.* from db1.tb1, db2.tb2;

mierlp · Post by **mierlp** » Thu 27 Apr 2006 19:43

Hi,

Oke..thanks, but where i'm looking for is the correct syntax and
how to build the query using the selected value from the
dblookupcombobox. Let's assume i have a dblookupcombobox
which points to a tables called function.

dblookupcombobox.listsource:= qryFunction
dblookupcombobox.listfield:=functionName
dblookupcombobox.keyfield:=functionID
(main table contains a field call FunID (integer)

Now i'm looking for the way to create a query using the selected value from the dblookupcombobox...do i need to use the keyvalue
for building the query, like :

myQuery.clear;
myQuery.SQL.Add('select * from test where funID= ' +chr(39) + dblookupcombobox.keyvalue

i can't figure out the correct syntax for doing this....

greetz Peter

GEswin · Post by **GEswin** » Thu 27 Apr 2006 22:01

Code: Select all

Query.SQL.Clear;
Query.SQL.Add('select * from test where funID = :funID');
Query.ParamByName('funID').AsInteger := qryFunction.FieldByName('functionID').AsInteger;
Query.Open;

After first time you open query, you've enought to call:

Code: Select all

Query.ParamByName('funID').AsInteger := qryFunction.FieldByName('functionID').AsInteger;
Query.Open;

Groetjes,

Antaeus · Post by **Antaeus** » Fri 28 Apr 2006 08:24

GEswin is right, using parameters is more convenient than dynamic generated queries, moreover, using parameters for oft-recurring queries you can achieve better performance.

mierlp · Post by **mierlp** » Sat 29 Apr 2006 19:10

thanks

you're suggestions are working great