vba INSERT INTO ... SELECT 语句（Access SQL）上的语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21205985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Syntax error on INSERT INTO ... SELECT statement (Access SQL)
提问by OcelotcR
I am trying to insert a field 'SID' Value into tbl1, But to get the SIDIt must reference the forename and surname to another table (tbl2) to get the SID, I then have used the following SQL Statement and subsequent code.
我试图在tbl1 中插入一个字段“ SID”值，但要获取SID它必须将前名和姓氏引用到另一个表 ( tbl2) 以获取SID，然后我使用了以下 SQL 语句和后续代码。
Dim sqlquery As String = "INSERT INTO tblPayments (StudentID,Payment,PaymentDate) VALUES (SELECT StudentID FROM tblStudents WHERE Forename = @Forename AND Surname = @Surname , Payment = @SPaid, PaymentDate = @todaysdate)"
Dim sqlcommand As New OleDbCommand
With sqlcommand
.CommandText = sqlquery
.Parameters.AddWithValue("@SPaid", Paidtxt.Text)
.Parameters.AddWithValue("@todaysdate", Today.Date)
.Parameters.AddWithValue("@Forename", Forenametxt.Text)
.Parameters.AddWithValue("@Surname", Surnametxt.Text)
.Connection = conn
.ExecuteNonQuery()
End With
MsgBox("query executed, closing connection")
conn.Close()
Yet, the SQLQuery is giving the error :
然而，SQLQuery 给出了错误：
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in query expression 'SELECT StudentID FROM tblStudents WHERE Forename = @Forename AND Surname = @Surname'.
System.Data.dll 中发生类型为“System.Data.OleDb.OleDbException”的未处理异常附加信息：查询表达式“SELECT StudentID FROM tblStudents WHERE Forename = @Forename AND Surname = @Surname”中的语法错误。
But I can not see what is wrong with the part of the statement that is specified as wrong, can someone tell me where its wrong please?
但是我看不出被指定为错误的那部分语句有什么问题，有人能告诉我哪里错了吗？
回答by Gordon Linoff
I think the error is very evident. The syntax for insert . . . selectdoes not use the valueskeyword:
我认为错误非常明显。的语法insert . . . select不使用values关键字：
INSERT INTO tblPayments(StudentID, Payment, PaymentDate)
SELECT StudentID, @SPaid, @todaysdate
FROM tblStudents
WHERE Forename = @Forename AND Surname = @Surname;
