Values going into the wrong parameter of an TADOQuery

[Marco van Deursen]

I need a workaround or another solution for the following problem.

Problem:
I must build an SQL-query at runtime with a Delphi 5 application. The
simplest form of those queries which result in a problem are of this
form

query 1 - runtime built in a Delphi 5 application, then assigned to the
SQL property of an TADOQuery

select *
from album
where
recid in (select album from kenmerkenalbum where kenmerk =
:KENMERK)
AND
code = :CODE

query 2 - runtime built in a Delphi 5 application, then assigned to the
SQL property of an TADOQuery

select *
from album
where
code = :CODE
AND
recid in (select album from kenmerkenalbum where kenmerk =
:KENMERK)

This is the Delphi Sourcecode belonging to the above queries: (:CODE
needing a string and :KENMERK needing an integer)

ADOQuery1.Close;
ADOQuery1.Parameters.ParamByName('CODE').Value := edCode.Text;

ADOQuery1.Parameters.ParamByName('KENMERK').Value :=
edKenmerk.Text;
ADOQuery1.Open;

I guess this should be relativly simple, yet the TAdoQuery seems to
think otherwise.

Query 1, works fine, resulting in a correct resultset. Query 2 however
results in a data type mismatch. Reason : edCode.text is assigned to
:KenMerk and :edKenmerk.text is assigned to :Code. This source :

ADOQuery1.Close;
ADOQuery1.Parameters.ParamByName('CODE').Value :=
edKenmerk.Text;
ADOQuery1.Parameters.ParamByName('KENMERK').Value :=
edCode.Text;
ADOQuery1.Open;

results in the "would-be-correct" resultset. The problem is, I cannot
guarantee that the subselects are always the first in the where
condition. This looks like a bug in the TAdoQuery, and mostlikely in the
TAdoCustomDataSet. So is there another of assigning values to the
correct parameter?
Does anybody have any insights, solutions or workarounds on this
problem? The database is an Access database, if that's is helpful.

Thanks in advance,
Marco van Deursen

[Howard Moon]

I believe the problem is that while ParamByName is useful for *reading*
parameter values, it can fail when setting because the ADO implementation in
Delphi uses the parameter *position*, not its name. Which means you have to
know the order in the query in order to set the parameter. Instead of using
parameters, if you're building the query dynamically, why not just put the
values in the query string as you build it, and skip using parameters?
-Howard

[Roger Mudgett]

I think you need to eliminate the = part in the parameter ie just put the
param: instead of Blank = Param:
or in your example: WHERE :Kenmerk

Hope this helps,

Roger

Marco van Deursen <ma...@invers.nl> wrote in message
news:3986BECE...@invers.nl...

[Ruud Vermeij]

I also got some strange results in working with parameters. Sometimes it
works, sometimes it won't.

A query like this gives an error at runtime

DELETE FROM ATABLE
WHERE IDA IN (SELECT IDA FROM ANOTHERTABLE
WHERE IDB = :AnIDParam)

Everything is dynamicly assigned, so I choose to not use parameters.

I have some doubts about using parameters, maybe someone has a good
explanation?

Ruud Vermeij

[Marco van Deursen]

Thanks Howard,

Actually your solution was the other one left at the end, nut we chose to use
the parameter option regarding the following points:

* It will quite often happen that an updatetable query, will be used over and
over again by the user, after setting it up. And then just change the
values. In that case I can prepare the query, which should result in
enhanced performance. Or am I mistaking?

* Also there's a minor problem with strings like "Ozzy's Album". The
apostrophe will terminate the string in the SQL-statement. This means I
should double all the apostrophes in the string right? To something like
"Ozzy''s Album" ?

I'm guessing I should be looking at the no-parameter option anyway, since the
parameter option is mostlikely going to fail, whatever I try. Are there any
other characters I should be cautious about?

Kind regards,
Marco van Deursen

[Howard Moon]

> * Also there's a minor problem with strings like "Ozzy's Album". The
> apostrophe will terminate the string in the SQL-statement. This means
I
> should double all the apostrophes in the string right? To something
like
> "Ozzy''s Album" ?
>

Marco,
instead of trying to match up quotes yourself, you can pass the SQL string
to QuotedStr, and it will handle all the special cases for you. For
example, if S contains "Ozzy's Album", then QuotedStr( S ) will let you
safely pass it in a SQL call. (Of course, to assign the *constant* "Ozzy's
Album" to a string, you would have to say S := 'Ozzy''s Album', like you
said, but I assume the actual string comes from either a control or a table,
so that's not relevant, right?)
-Howard

[Marco van Deursen]

D#mn, it just figures, that something like QuotedStr existed. Just finished
writing it myself. Oh well, we all have to waste our time sometime.

And the values are indeed coming from controls and tables. So, it's perfect like
this.

Thanks for the help, Howard.