Illegal field name
Illegal field name
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
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
Re: Illegal field name
I should have mentioned, that this happens on all SDAC versions for Delphi 7 I have access to, also the latest one.
Poul
Poul
Re: Illegal field name
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.
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.
Re: Illegal field name
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.
Select DatePart(Year, ADateTime)
from ATable
returns COLUMN1
Then I am not using an alias, but it still crashes.
Re: Illegal field name
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:
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
-
- Posts: 1
- Joined: Thu 10 May 2018 09:12
Table with a field named 'User'
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?
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?
Re: Illegal field name
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;