尝试通过 SQL 从另一个表中获取 ID 插入访问数据库 [英] Trying to insert into access database via SQL getting ID from another table

问题描述

我在 Access 数据库中有 2 个表:

I have 2 tables in an Access database:

• tblMachine
• 字段:
• 机器 ID

• 机器描述

• tblMachine
• fields:
• MachineID

• MachineDescription

tblProblem

我正在尝试向 tblProblem 添加一条新记录，使用 MachineDescription 从 tblMachine 中查找 MachineID

I am trying to add a new record into tblProblem, using the MachineDescription to find the MachineID from tblMachine

但是，我的 SQL 语句在 sub-select 语句上抛出错误

However, my SQL statement throws an error on the sub-select statement

这是我的声明:

string sql = "INSERT INTO tblProblem" +
                " ([MachineID], [ProblemDescription], [ProblemOrder])" +
                " VALUES (" + 
                "(SELECT ([MachineID] FROM tblMachine WHERE MachineDescription = @MachineDescription))," +
                " @ProblemDescription, @ProblemOrder);";

突出显示的问题是这部分:

The problem being highlighted is this part:

"(SELECT ([MachineID] FROM tblMachine WHERE MachineDescription = @MachineDescription)),"

我做错了吗?它告诉我有一个语法错误...

Have I done something wrong? It is telling me there is a syntax error...

推荐答案

额外的一组括号，可能还有不合格的字段名称:

Extra set of parentheses, and perhaps unqualified field names:

String sql = "INSERT INTO tblProblem " + 
    "([MachineID], [ProblemDescription], [ProblemOrder])" +
    "SELECT tblMachine.[MachineID], @ProblemDescription, @ProblemOrder " +
    "FROM tblMachine " +
    "WHERE tblMachine.MachineDescription = @MachineDescription";

这篇关于尝试通过 SQL 从另一个表中获取 ID 插入访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！