| View previous topic :: View next topic |
| Author |
Message |
Dave Blake
Guest
|
 Posted: Wed Jul 27, 2005 8:36 am Post subject: Parameterized Query No rows effected |
 |
|
I have a parameterized query of the following form
UPDATE MyTable
SET field1 = :Param1
WHERE field2 = :Param2 AND
NOT EXISTS (SELECT field21 FROM MyTable2
WHERE (field21=field1) AND (field22 <> :Param3) )
but it mysteriously does not update anything when executed.
All parameters are ftInteger, input direction, set to integer values before
ExecSql. Executed within a transaction after another query that updates
MyTable2 - could there be an issue of it not seeing these changes? Yet a
similar query with Param1, and Param3 replaced by fixed numeric values in
the SQL works perfectly (updates the records expected), as does a
non-parameterized query building the SQL text dymanically.
I just can't see what is wrong with it. Any ideas? ADO onto mdb file via Jet
4.
Dave
|
|
| Back to top |
|
 |
Vitali Kalinin
Guest
|
| Posted: Wed Jul 27, 2005 9:46 am Post subject: Re: Parameterized Query No rows effected |
|
|
Do you use the same ADOConnection for both Updates? If not then you should
be aware of Jet Latency problem.
|
|
| Back to top |
|
|
Dave Blake
Guest
|
| Posted: Wed Jul 27, 2005 2:29 pm Post subject: Re: Parameterized Query No rows effected |
|
|
| Quote: | Do you use the same ADOConnection for both Updates? If not then you should
be aware of Jet Latency problem.
|
Yes, same connection. I am aware of Jet latency too, I don't think it
applies to this.
A query with fewer parameters works even when have just updated field22 e.g.
UPDATE MyTable
SET field1 = 9
WHERE field2 = :Param2 AND
NOT EXISTS (SELECT field21 FROM MyTable2
WHERE (field21=field1) AND (field22 <> 11) )
but pass "9" and "11" as a parameters and nothing gets updated.
Are there limits on using parameters within a subquery or SET statement?
Dave
|
|
| Back to top |
|
|
Dave Blake
Guest
|
| Posted: Wed Jul 27, 2005 2:51 pm Post subject: Re: Parameterized Query No rows affected |
|
|
It is the parameter in the subquery that is causing the problem. Can anyone
tell me what ADO (or Jet data provider) does with parameters that would
explain this?
This sucessfully affects rows
UPDATE MyTable
SET field1 = :Param1
WHERE field2 = :Param2 AND
NOT EXISTS (SELECT field21 FROM MyTable2
WHERE (field21=field1) AND (field22 <> 11) )
parameters[0].value := 9;
parameters[1].value := 3000;
but
UPDATE MyTable
SET field1 = :Param1
WHERE field2 = :Param2 AND
NOT EXISTS (SELECT field21 FROM MyTable2
WHERE (field21=field1) AND (field22 <> :Param3) )
parameters[0].value := 9;
parameters[1].value := 3000;
parameters[2].value := 11;
does not. Why?
Dave
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
