OK I've got it again

ElevateDB 1.08

The query:

SELECT
_ID,
PM._Name AS _Project,
_LastResult,
_Stage,
_DateAdded
FROM Calls
JOIN Projects PM ON _fkProjects = PM._ID
WHERE
_fkContacts = :ContactID
AND
PM._ID <> :ProjectID
ORDER BY
_DateAdded DESC

My code:

OtherProjects.Close;
if not OtherProjects.Prepared then OtherProjects.Prepare;
OtherProjects.ParamByName('ContactID').AsInteger := Calls.FieldByName('_fkContacts').AsInteger;
OtherProjects.ParamByName('ProjectID').AsInteger := Calls.FieldByName('_fkProjects').AsInteger;
OtherProjects.ExecSQL;
showmessage(VarToStr(OtherProjects.Params.ParamValues['ContactID']) +' '+
VarToStr( OtherProjects.Params.ParamValues['ProjectID'])+' '+
inttostr(OtherProjects.recordcount));


This shows 107411 3 7585 - should be 107411 3 2

Trying the same code in EDBManager I get 7585 records when the parameters are left as NULL and 2 when I fill them in

So just to see I altered the final D in each parameter - just deleted and retyped and now it works!!!!!!!!!!!!!

Roy Lambert

Roy,

<< This shows 107411 3 7585 - should be 107411 3 2

Trying the same code in EDBManager I get 7585 records when the parameters
are left as NULL and 2 when I fill them in.  So just to see I altered the
final D in each parameter - just deleted and retyped and now it
works!!!!!!!!!!!!! >>

So, the EDB Manager works every time, but your code does not ?  Could you
send me a sample that reproduces this ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>So, the EDB Manager works every time, but your code does not ?

Yes, but remember by cut'n'paste into EDBManager I'm effectively re-typing the query which fixes it.

>Could you send me a sample that reproduces this ?

I thought about doing that but I'd have had to send a complete app and its database so chickened out.

I've often read that the CRLF pair getting altered (to LF I think but it could be CR) at the end of a line in a .pas stops the debugger picking that line up and I was wondering if this could be the same sort of thing?

What really puzzles me is the query was telling me the parameters are there and set, but just aren't being applied.

Roy Lambert

Roy,

<< What really puzzles me is the query was telling me the parameters are
there and set, but just aren't being applied. >>

Do the parameters *ever* work in your application, once they're assigned ?
IOW, do they work the first time, and then subsequently don't work ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Intriguing question. I'll have a hunt through the app tomorrow and see if there are any others and if they're working or not.

At least they're working now.

Roy Lambert

Tim


They must have worked at one time, otherwise I would have noticed, but that would have been DBISAM when I started writing the app.

I thought I'd spotted a common trend - two integer parameters but then I found one with 1 integer parameter that didn't work. Each time, altering the parameter (delete last character and retype) sorts it. I can't see how it can be an ElevateDB thing it must just be one of those D2006 weirdos.

Off I go to alter all the queries - oh joy.

Roy Lambert

Tim


I found one that has definitely been working since it was typed

SELECT Name, Description FROM Users
WHERE
Name = :UserID

If it hadn't I would never have been able to log in and find all the others weren't. Interestingly Name is not integer.

Roy Lambert