Workaround Reserved Words

Question

I have a table called Candidates with fields C_ID, C_Surname, C_Names and C_Gender. The word NAMES is reserved in Microsoft Jet 4.0 and therefore the SQL statement SELECT C_Names AS Names does not work. Is there any workaround to use the word Names as an identifier?

NOTE: My method of connection is ADO.

Update

Seeing as none of the provided answers have worked, here’s my full SQL statement:

SELECT
  C.C_ID AS ID,
  StrConv(C.C_Surname, 3) AS Surname,
  StrConv(C.C_Names, 3) AS Name,
  Sum(V.V_ClassA * M.M_Multiplier) AS ClassA,
  Sum(V.V_ClassB * M.M_Multiplier) AS ClassB,
  Sum(V.V_ClassC * M.M_Multiplier) AS ClassC
FROM
  ((Candidates AS C
    INNER JOIN Votes AS V ON C.C_ID = V.C_ID)
    INNER JOIN Parties AS P ON V.P_ID = P.P_ID)
    INNER JOIN Multiplier AS M ON P.P_Type = M.P_Type
GROUP BY
  C.C_ID,
  C.C_Surname,
  C.C_Names
ORDER BY
  Sum(V.V_ClassA * M.M_Multiplier) DESC,
  Sum(V.V_ClassB * M.M_Multiplier) DESC,
  Sum(V.V_ClassC * M.M_Multiplier) DESC;

Implementation Code

begin
  with dmBKElections.qryTallyVotes, SQL do
    begin
      Clear;
      Add('SELECT');
      Add('  C.C_ID AS ID,');
      Add('  StrConv(C.C_Surname, 3) AS Surname,');
      Add('  StrConv(C.C_Names, 3) AS Name,');
      Add('  Sum(V.V_ClassA * M.M_Multiplier) AS ClassA,');
      Add('  Sum(V.V_ClassB * M.M_Multiplier) AS ClassB,');
      Add('  Sum(V.V_ClassC * M.M_Multiplier) AS ClassC');
      Add('FROM');
      Add('  ((Candidates AS C');
      Add('    INNER JOIN Votes AS V ON C.C_ID = V.C_ID)');
      Add('    INNER JOIN Parties AS P ON V.P_ID = P.P_ID)');
      Add('    INNER JOIN Multiplier AS M ON P.P_Type = M.P_Type');
      Add('GROUP BY');
      Add('  C.C_ID,');
      Add('  StrConv(C.C_Surname, 3),');
      Add('  StrConv(C.C_Names, 3)');
      Add('ORDER BY');
      Add('  Sum(V.V_ClassA * M.M_Multiplier) DESC,');
      Add('  Sum(V.V_ClassB * M.M_Multiplier) DESC,');
      Add('  Sum(V.V_ClassC * M.M_Multiplier) DESC;');
      Open;
    end;
end;

When I change

Add('  StrConv(C.C_Names, 3) AS Name,');

to

Add('  StrConv(C.C_Names, 3) AS [Names],');

I get the error:

Project BKElections_P.exe raised exception class $C0000005 with message 'access violation at 0x0062ee2a: read of address 0x000000e2'.

surround the reserved words with square brackets, like `as [names]`? Syntax depends on back-end. — Beth, Oct 05 '15 at 15:50
confirm by changing 'name/names' to a non-reserved word (name1) and see if that resolves your problem. — Beth, Oct 05 '15 at 16:54
The statement works perfectly when setting the identifier to `Name`. The access violation is solely caused by changing it to `Names` encased by quotation marks, apostrophe or grave accent. — Reginald Greyling, Oct 05 '15 at 17:00
Post the code where you are actually executing this query. Your information makes no sense; changing the alias of a column cannot cause an AV. — Ken White, Oct 05 '15 at 20:31
Step 1 when troubleshooting: Get rid of the `with`, so there's no confusion regarding which `Add` might be in scope. Step 2 (if step 1 doesn't solve the problem): Use the debugger to figure out which object hasn't been created before you're attempting to use it. Step 3: Run your SQL statement in Access itself with proper values and see if the issue still occurs. — Ken White, Oct 06 '15 at 12:37

score 0 · Answer 1 · answered Oct 05 '15 at 18:57

0

Your have two choise if you are using SQL Server or MySQL on all other there must be one:

SELECT Fieldname as "Name" ...

This ist ANSI

OR

SELECT Fieldname as [Name]

This ist SQL Server and MS Access

On MySQL you can use:

SELECT Fieldname as ´Name´

But is it possible that you get the error in .NET and not in SQL? Normaly you don't get this error if you rename a column to "name", only if you want to access a column with a reserved name in some cases.

So in ADO.NET you can try:

rst![Name]
rst.Fields("Name")

Or the best think: Give us a sample of your code. And please try the SQL in SSMS.

answered Oct 05 '15 at 18:57

Frank

1,901
20
27

I provided my full SQL statement in my question and I am using TADOQuery to implement it. I checked and can confirm that the word “Names” (not Name) is not a reserved word in Delphi, seeing as I can create a string variable identified as Names without any problem. Access also accepts Names in a query, so the problem lies somewhere with ADO.NET – Reginald Greyling Oct 05 '15 at 19:21
I just did some research and updated my question. Check first paragraph second sentence. – Reginald Greyling Oct 05 '15 at 19:28

score 0 · Answer 2 · answered Oct 05 '15 at 19:32

0

You should wrap any reserved words in square brackets as follows:

[Name] instead of Name

answered Oct 05 '15 at 19:32

Laurence Frost

2,759
3
28
45

Looking at your error message, I think that the query is not the problem. I think that putting square brackets in the string is the problem. I am not familiar with the language you have used, but I would try and remove the part of the code where it actually executes against the database and see if you still have the problem. I think the string has a problem with square brackets. Perhaps square brackets have a specific meaning in that language which means you cannot add them. You may need to escape them somehow. – Laurence Frost Oct 06 '15 at 14:52

Workaround Reserved Words

Update

Implementation Code

2 Answers2