"Syntax error in FROM clause" C# 中使用 OleDb 但在 Access 本身中使用同一查询没有错误

Question

在使用C＃/OLEDBCOMMAND.EXECUTEREADER中使用以下SQL-statement时，我会在从条件中获得语法错误。在MS Access中使用完全相同的语句直接工作正常。

SELECT 
s.idShots, s.shotdata, c.[original], s.[hash], comp.idCompetitions, comp.competitionsname, sh.idShooters, sh.firstname, sh.lastname 
FROM (([Shots] s 
INNER JOIN [ShotsCertificate] c ON c.[uuid] = s.[uuid]) 
INNER JOIN [Competitions] comp ON comp.idCompetitions = s.fidCompetitions) 
INNER JOIN [Shooters] sh ON sh.idShooters = s.fidShooters ORDER BY s.idShots ASC

在C＃中：

        OleDbCommand cmd2 = new OleDbCommand("", dbc);
        cmd2.CommandText = "SELECT s.idShots, s.shotdata, c.[original], s.[hash], comp.idCompetitions, comp.competitionsname, sh.idShooters, sh.firstname, sh.lastname FROM" +
            " (([Shots] s" +
            " INNER JOIN [ShotsCertificate] c ON c.[uuid] = s.[uuid])" +
            " INNER JOIN [Competitions] comp ON comp.idCompetitions = s.fidCompetitions)" +
            " INNER JOIN [Shooters] sh ON sh.idShooters = s.fidShooters" +
            " ORDER BY s.idShots ASC";
        log.Debug(cmd2.CommandText);
        OleDbDataReader r = cmd2.ExecuteReader();

DBC连接效果很好，它在某些以前的命令中使用，并且一切都起作用。

感谢您的建议！

Answer 1

对于记录，问题是COMP包含在访问SQL保留单词列表中，大概是用于访问DDL的COMPRESSION的缩写。将表的别名从comp更改为cmpt，允许查询在System.Data.oledb下成功运行：

sql = "SELECT s.idShots, s.shotdata, c.[original], s.[hash], cmpt.idCompetitions, cmpt.competitionsname, sh.idShooters, sh.firstname, sh.lastname FROM" +
    " (([Shots] s" +
    " INNER JOIN [ShotsCertificate] c ON c.[uuid] = s.[uuid])" +
    " INNER JOIN [Competitions] cmpt ON cmpt.idCompetitions = s.fidCompetitions)" +
    " INNER JOIN [Shooters] sh ON sh.idShooters = s.fidShooters" +
    " ORDER BY s.idShots ASC";

Answer 2

我让它起作用...没有评论：/

SELECT
 [Shots].[idShots], [Shots].[shotdata], [ShotsCertificate].[original], [Shots].[hash], [Competitions].[idCompetitions], [Competitions].[competitionsname], [Shooters].[idShooters], [Shooters].[firstname], [Shooters].[lastname] 
FROM (([Shots] 
   INNER JOIN [ShotsCertificate] ON [ShotsCertificate].[uuid] = [Shots].[uuid]) 
   INNER JOIN [Competitions] ON [Competitions].[idCompetitions] = [Shots].[fidCompetitions]) 
   INNER JOIN [Shooters] ON [Shooters].[idShooters] = [Shots].[fidShooters]
ORDER BY [Shots].[idShots] ASC