Page 1 of 1
Query based on 2 variables
Posted: Wed 26 Apr 2006 13:42
by mierlp
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
Posted: Thu 27 Apr 2006 09:08
by Antaeus
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;
Posted: Thu 27 Apr 2006 19:43
by mierlp
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
Posted: Thu 27 Apr 2006 22:01
by GEswin
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,
Posted: Fri 28 Apr 2006 08:24
by Antaeus
GEswin is right, using parameters is more convenient than dynamic generated queries, moreover, using parameters for oft-recurring queries you can achieve better performance.
Posted: Sat 29 Apr 2006 19:10
by mierlp
thanks
you're suggestions are working great