System.Data.OleDb.OleDbException：SQL连接查询语法错误求助

原错误代码 #

string sSql = $"SELECT OxygenTankInDetailTBL.HBedNameID, 
                       OxygenTankInDetailTBL.LocationHB 
                FROM OxygenTankInDetailTBL 
                INNER JOIN SectionsNameTBL ON (SectionsNameTBL.SectionID = OxygenTankInDetailTBL.LocationHB 
                                           AND SectionsNameTBL.LocationG = '{locationG}') 
                INNER JOIN OxygenTankTBL ON (OxygenTankTBL.HBedID = OxygenTankInDetailTBL.HBedNameID 
                                         AND OxygenTankTBL.IsActive= {true});"; 

错误信息 #

System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression '(SectionsNameTBL.SectionID = OxygenTankInDetailTBL.LocationHB AND SectionsNameTBL.LocationG = 'agaf1') INNER JOIN OxygenTankTBL ON(OxygenTankTBL.HBedID = OxygenTankInDetailTBL.HBedNameID AND OxygenTankTBL.IsActive= True'.'

错误原因 #

你使用的是OleDb驱动（通常对应Access数据库），这类数据库的SQL语法对多表连接有特殊要求：当连接三个或以上表时，必须用括号将前两个表的连接结果包裹起来，否则数据库无法正确解析连接逻辑，导致语法错误。

修复后的代码 #

string sSql = $"SELECT OxygenTankInDetailTBL.HBedNameID, 
                       OxygenTankInDetailTBL.LocationHB 
                FROM (OxygenTankInDetailTBL 
                INNER JOIN SectionsNameTBL ON (SectionsNameTBL.SectionID = OxygenTankInDetailTBL.LocationHB 
                                           AND SectionsNameTBL.LocationG = '{locationG}')) 
                INNER JOIN OxygenTankTBL ON (OxygenTankTBL.HBedID = OxygenTankInDetailTBL.HBedNameID 
                                         AND OxygenTankTBL.IsActive= {true});"; 

额外建议 #

避免直接在SQL字符串中拼接变量（如'{locationG}'），这种做法存在SQL注入风险。建议使用参数化查询，示例如下：

string sSql = "SELECT OxygenTankInDetailTBL.HBedNameID, 
                       OxygenTankInDetailTBL.LocationHB 
                FROM (OxygenTankInDetailTBL 
                INNER JOIN SectionsNameTBL ON (SectionsNameTBL.SectionID = OxygenTankInDetailTBL.LocationHB 
                                           AND SectionsNameTBL.LocationG = ?)) 
                INNER JOIN OxygenTankTBL ON (OxygenTankTBL.HBedID = OxygenTankInDetailTBL.HBedNameID 
                                         AND OxygenTankTBL.IsActive= ?);";

// 后续通过OleDbCommand添加参数
OleDbCommand cmd = new OleDbCommand(sSql, yourConnection);
cmd.Parameters.AddWithValue("@LocationG", locationG);
cmd.Parameters.AddWithValue("@IsActive", true);

内容的提问来源于stack exchange，提问作者Noa Zakai