Subject Re: [IBO] problems with SQL query
Author petesouthwest
Hi Helen

As I use the qryFBall do several things I would like to have set the sql at run time, also both fields are strings. I thus changed the code to look like:

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

with datamodule2.qryFBAll do
begin
if not Prepared then Prepare;
if Active then Close;
Params['empNo'].AsString := form1.DBEdit3.Text;
Params['projectno'].AsString :=form1.cmbModule.text;
sql.Clear;
sql.add(queryString);
open;
end;

but I get 'incompatible types: integer and string' on both the params lines. The fields are both set to char(10) in the database

Thanks for your help, I really do appreciate it!

Pete

----- Original Message -----
From: Helen Borrie
To: IBObjects@yahoogroups.com
Sent: Thursday, July 29, 2004 8:54 AM
Subject: Re: [IBO] problems with SQL query


At 08:25 AM 29/07/2004 +0100, you wrote:
>Hi
>
>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?
>
>Thanks
>Pete
>code:

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='+'"'
>+form1.cmbModule.text+'"';


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 :=
StrToInt(form1.cmbModule.text);
> // sql.Clear;
> // sql.add(queryString);
> open;
> end;

Helen



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !


Yahoo! Groups Sponsor
ADVERTISEMENT





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/IBObjects/

b.. To unsubscribe from this group, send an email to:
IBObjects-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.




[Non-text portions of this message have been removed]