C#连接MS Access 2003多表INNER JOIN出现语法错误求助

你遇到的这个错误，是MS Access 2003使用的Jet SQL引擎特有的语法限制导致的——Access Jet SQL要求多个连续的INNER JOIN必须用括号嵌套，不像标准SQL（比如SQL Server）可以直接连续写连接语句。你的原SQL没有做嵌套处理，引擎无法正确解析连接的逻辑层级，所以抛出了语法错误。

修正后的SQL语句 #

把多表JOIN部分用括号逐层嵌套，让Access能识别正确的连接顺序：

SELECT k.[Desc], k.Family, k.Num, p1.Name AS KeyAdmin, 
       p2.Name AS KeyCustodian1, p3.Name AS KeyCustodian3,
       p4.Name AS SecurityOfficer, p5.Name AS ServiceIT 
FROM (((((KC AS k 
INNER JOIN Person AS p1 ON k.Ka = p1.Id) 
INNER JOIN Person AS p2 ON k.Kc1 = p2.Id) 
INNER JOIN Person AS p3 ON k.Kc3 = p3.Id) 
INNER JOIN Person AS p4 ON k.So = p4.Id) 
INNER JOIN Person AS p5 ON k.It = p5.Id) 
WHERE k.Num = @Num;

完整的修正后C#代码 #

替换你原代码中的SQL字符串即可，另外注意using块会自动管理连接生命周期，不需要手动调用conn.Close()：

try 
{ 
    using (OleDbConnection conn = new OleDbConnection(connecString)) 
    { 
        conn.Open(); 
        OleDbCommand cmd = new OleDbCommand(@"SELECT k.[Desc], k.Family, k.Num, p1.Name AS KeyAdmin, 
                                                   p2.Name AS KeyCustodian1, p3.Name AS KeyCustodian3,
                                                   p4.Name AS SecurityOfficer, p5.Name AS ServiceIT 
                                            FROM (((((KC AS k 
                                            INNER JOIN Person AS p1 ON k.Ka = p1.Id) 
                                            INNER JOIN Person AS p2 ON k.Kc1 = p2.Id) 
                                            INNER JOIN Person AS p3 ON k.Kc3 = p3.Id) 
                                            INNER JOIN Person AS p4 ON k.So = p4.Id) 
                                            INNER JOIN Person AS p5 ON k.It = p5.Id) 
                                            WHERE k.Num = @Num;", conn); 
        OleDbDataAdapter adapt = new OleDbDataAdapter(cmd); 
        cmd.Parameters.AddWithValue("@num", form.comboKC.SelectedValue); 
        DataTable dt = new DataTable(); 
        adapt.Fill(dt); 
        form.dataGridView1.DataSource = dt; 
    }
}
catch (Exception ex)
{
    // 建议添加异常捕获，方便调试时查看具体错误信息
    MessageBox.Show("运行错误：" + ex.Message);
}

调试小技巧 #

以后遇到Access SQL语法问题，可以先把SQL语句复制到Access的查询设计器里直接运行，Access会给出更直观的语法错误提示，能帮你快速定位问题，比在代码里调试更高效。

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