Subject Re: [IBO] problems with SQL query
Author Helen Borrie
At 08:25 AM 29/07/2004 +0100, you wrote:
>I have just converted my app from paradox to firebird using the IBO
>components. I was using the bit of code below to find one record from 2
>tables that are joined on empNo, the user can then select the employee
>number in the edit box and the project from a combo box (cmbModule). In
>paradox it worked fine and came back with the corresponding records from
>both tables. However, in my firebird/ibo version it appears to come back
>with all the records in the employee table, matched with one record in the
>project1 table.
>Can anyone suggest what is happening?

You don't have any join criteria in this statement.

>queryString:='SELECT * FROM employee INNER JOIN project1 ON project1.empNo
>=:empNo WHERE empNo="' + form1.DBEdit3.EditText +'" AND projectno='+'"'

Set the SQL property at design time:

// queryString:=
SELECT e.* FROM employee e
JOIN project1 p1
ON p1.empNo = e.emp_no
WHERE p1.empNo= :empNo
and p1.projectno= :projectno

Then, on your buttonclick or whatever:

>with datamodule2.qryFBAll do
> begin
if not Prepared then Prepare;
if Active then Close;
Params['empNo'].AsInteger := StrToInt(form1.DBEdit3.EditText);
Params['projectno'].AsInteger :=
> // sql.Clear;
> // sql.add(queryString);
> open;
> end;