Passing Parameters to SQL

[ColinTod]

Hi,
This is my first post.
I have only just started using SQL in Delphi and am obviously doing something wrong.
I have created a membership database and wish to create some RAVE reports for it.
I have a TIBQuery with the following SQL statement in it:

--------------------
select * from PERSON, SESSION, TRAINING_HISTORY
WHERE (ACTIVATED = 'Y') and
(SESSION.SESSION_ID = TRAINING_HISTORY.SESSION_ID) and
(PERSON.PERSON_ID = TRAINING_HISTORY.PERSON_ID) and
(SESSION.SESSIONDATE = '9/01/2006')
order by LAST_NAME
-------------------
and the following code in my "CreateReport" procedure
---------------------
If ReportNo = 1 then
begin
RvClass.Open;
RvClass.Execute;
end;
----------------------

This works perfectly, producing a report of attendances for the given day.
I then start to play with parameters as seen below

-------------------
select * from PERSON, SESSION, TRAINING_HISTORY
WHERE (ACTIVATED = 'Y') and
(SESSION.SESSION_ID = TRAINING_HISTORY.SESSION_ID) and
(PERSON.PERSON_ID = TRAINING_HISTORY.PERSON_ID) and
(SESSION.SESSIONDATE = :SDate)
order by LAST_NAME
---------------------
and the following code in my "CreateReport" procedure
---------------------
If ReportNo = 1 then
begin
qrClass.ParamByName('SDate').Value := '9/01/2006';
RvClass.Open;
RvClass.Execute;
end;
----------------------

This produces no errors, but gives a blank report.

In the long run I want to replace '9/01/2006' with a veriable but need to get this working first.

Can anyone help.

Much apreciated

Colin

 

[towerbase]

I've never used TIBquery but shouldn't

    [b]qr[/b]Class.ParamByName('SDate').Value := '9/01/2006';

be

    [b]Rv[/b]Class.ParamByName('SDate').Value := '9/01/2006';

Andrew
Hampshire, UK

 

[ColinTod]

qrClass is the name of the TIBQuery.
RvClass is the Rave component.

Colin

 

[ThunderForest]

Just a passing thought....

What type is :sDate? String? DateTime? If DateTime, it may be querying for, e.g., '09/01/2006 00:00:00'.

Getting answers before I'm asked.
Providing answers if I can.

 

[ColinTod]

sDate is ftUNKNOWN. The help say's:
"DataType is set automatically when a value is assigned to the parameter. Do not set DataType for bound fields, as that may cause the assigned value to be misinterpreted."

I think the problem is somewhere different.

I rechecked the origional (everything inside qrClass.SQL ) as below:
-------------------
select * from PERSON, SESSION, TRAINING_HISTORY
WHERE (ACTIVATED = 'Y') and (SESSION.SESSION_ID = TRAINING_HISTORY.SESSION_ID) and (PERSON.PERSON_ID = TRAINING_HISTORY.PERSON_ID) and (SESSION.SESSIONDATE = '9/1/2006')
order by LAST_NAME
-------------------
If ReportNo = 1 then
begin
RvClass.Open;
RvClass.Execute;
end;
--------------------
and it worked fine.

so since everyone seems to be concentrating on the Date aspect I thought I would try passing a value to the "Activated" field:
--------------------
select * from PERSON, SESSION, TRAINING_HISTORY
WHERE (ACTIVATED = :Active) and (SESSION.SESSION_ID = TRAINING_HISTORY.SESSION_ID) and (PERSON.PERSON_ID = TRAINING_HISTORY.PERSON_ID) and (SESSION.SESSIONDATE = '9/1/2006')
order by LAST_NAME
--------------------
If ReportNo = 1 then
begin
with qrClass do
begin
Close;
ParamByName('Active').Value := 'Y';
Open;
end;
RvClass.Open;
RvClass.Execute;
end;
---------------------

Again I get a blank report.
I seems to me that I am doing something basicly wrong with the process of passing a parameter.
Ideas?

Colin

 

[ThunderForest]

Forgive me, have to ask.... "Active" is defined in the Param property, right? Looks like you did for sDate when you mentioned ftUnknown.

Getting answers before I'm asked.
Providing answers if I can.

 

[towerbase]

I never use .Value when using ParamByName to pass parameters into a query. I always use something like

  ParamByName('Active').AsString := 'Y';
  ParamByName('SDate').AsDateTime := SessionDate;

It might be worth trying these modifications to see if the query returns any records.

Andrew
Hampshire, UK