Problem with TMSQuery.RefreshQuick()

Lithium™ · Post by **Lithium™** » Wed 23 Jun 2010 08:16

I've tried so hard to to find such topic below, but a lot of time need to browse over 50 pages (where is the search by topics?). So new topic created. Sorry if a repetition has place.

A SQL Query like

Code: Select all

select
	TableA.KeyA,		-- PrimaryKey, Identity
	TableA.Value as ValueA,
	TableA.Version,		-- timestamp field
	TableB.Value as ValueB
from
	TableA
	inner join TableB on
		TableB.KeyAB = TableA.KeyAB

placed into TMSQuery.

On calling TMSQuery.RefreshQuick(TRUE) the exception with message 'Ambiguous column name Version' raises.
It occurs because TableB also have a field with name 'Version' (and Table B.Version has such purpose either as TableA.Version, though it not so important here). The SQLProfiler show it clarity.

But in query above is meant use the TableA .Version for RefreshQuick() because it explicitly specified, isn't it? I see to solve this problem by including the TableName into RowVersion field name while RefreshQuick executing.

Are you know about this problem and plan (or not) to solve it? And what (if not plan) can I make in such cases?

Oh yeah, in the latest build 4.80.0.58 the problem still exists.

Dimon · Post by **Dimon** » Wed 23 Jun 2010 10:18

To solve the problem set the MSQuery.Options.FullRefresh property to True.

Lithium™ · Post by **Lithium™** » Wed 23 Jun 2010 11:01

Thank you very much, the problem is solved.

Lithium™ · Post by **Lithium™** » Wed 23 Jun 2010 11:12

I faced new problem under this subject.
I need to set MSQuery.ReadOnly property to TRUE, because data just displayed and refreshed in one Form but changes proceeds in other Form. If I make so, new exceptions raises 'Unique keyFields required'. What do I wrong?

Dimon · Post by **Dimon** » Wed 23 Jun 2010 15:29

The point is that when you use the read-only mode for opened query, SQL Server doesn't return information about table at which fields is belong.
To solve the problem set the TMSQuery.KeyFields property.

Lithium™ · Post by **Lithium™** » Thu 24 Jun 2010 06:34

Thank you very much, the problem is solved again.

Lithium™ · Post by **Lithium™** » Thu 01 Jul 2010 13:49

Unfortunately it is hasty conclusion, the problem is not solved yet. The exception 'Unique keyFields required' does not appear more, but the TMSQuery does not sent any queries to server and do nothing though changes exists. Only ReadOnly property fixed to FALSE the QuickRefresh works correctly.
Please, help me once again to solve this problem.

Dimon · Post by **Dimon** » Thu 01 Jul 2010 14:42

Unfortunately, RefreshQuick does not work when MSQuery.ReadOnly = True because SQL Server doesn't return information about table to which fields belong.
To solve the problem you should not set MSQuery.ReadOnly to True.

Lithium™ · Post by **Lithium™** » Fri 02 Jul 2010 06:09

Thank you for quick reply, I shall take into account this particularity.