Illegal field name

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
DerPaul
Posts: 17
Joined: Thu 25 Jan 2007 02:35

Illegal field name

Post by DerPaul » Tue 14 Jan 2014 18:17

Hi
When I try to do a Master/detail relationship using renaming in the detailpart, like User as Bruger, the detailpart fails with an Illegal field name.

An example:
I have two tables, the master-table, Users, which contains all the registrered users, and a History table, containing everything which has been done by the different users.
I want to show for each user what the particular user has done, that is, show all the items the user has done something with.
So I make Users mastertable, History the detail table. The detail MsQuery gets a MasterSource=Users, and a MasterField=User and a DetailField=Bruger
Remember that Bruger's true name is really User, but I want to be able to use other languages, and in Danish (my language) User is called Bruger.
So far, so good. But when I click activate, I get an error: Bruger invalid field.

This seems to fail in all circumstances where the DetailField is not a proper fieldname. It is like Master/Detail relations cannot handle as name in the detail part.
If for instance you want to use years, you will do something like Year(TheDate) as Aar. This fails with Illegal field name.

Any suggestions?

Best regards

Poul

DerPaul
Posts: 17
Joined: Thu 25 Jan 2007 02:35

Re: Illegal field name

Post by DerPaul » Wed 15 Jan 2014 19:47

I should have mentioned, that this happens on all SDAC versions for Delphi 7 I have access to, also the latest one.

Poul

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Illegal field name

Post by AlexP » Thu 16 Jan 2014 15:04

Hello,

This is correct behavior, since the DetailField value is substituted to the WHERE section of the detailed query, and since you can't use aliases in WHERE, you can either paste an existing field name to the DetailField property, or not specify the DetailField and MasterField properties, and set the WHERE clause manually.

DerPaul
Posts: 17
Joined: Thu 25 Jan 2007 02:35

Re: Illegal field name

Post by DerPaul » Mon 20 Jan 2014 18:04

Well, what about if I have to extract the year-part of a datetime field?
Select DatePart(Year, ADateTime)
from ATable

returns COLUMN1
Then I am not using an alias, but it still crashes.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Illegal field name

Post by AlexP » Tue 21 Jan 2014 10:21

Hello,

As I wrote earlier, aliases cannot be used in the WHERE clause. In your case, you should manually write a condition to the query:

Code: Select all

Select DatePart(Year, ADateTime) 
from ATable
where DatePart(Year, ADateTime) = :param_name

stefano.biggi
Posts: 1
Joined: Thu 10 May 2018 09:12

Table with a field named 'User'

Post by stefano.biggi » Thu 13 Dec 2018 11:42

I have a problem using TMSQuery with Delphi 10.2 and SDAC for MS SQL SERVER (last version)

The table has a field named 'User'.

If i try to append a new record with TMSQuery it returns an error "sintax error near 'User'".
If i try with ADOQuery it works well, using the same code.
How can i resolve the problem?

Stellar
Devart Team
Posts: 340
Joined: Tue 03 Oct 2017 11:00

Re: Illegal field name

Post by Stellar » Fri 14 Dec 2018 15:55

The field name of the "User" table is a reserved expression in MS SQL Server, therefore, quotation must be used. For this, set the QuoteNames option for TMSQuery to True, for example:

Code: Select all

MSQuery1.Options.QuoteNames := True;

Post Reply