语法错误OledbException delete语句

Question

我在这里找不出我的语法错误是什么。有人发现了吗？或者我说的都错了？

Dim myCommand As New OleDb.OleDbCommand("delete * from Team where intPlayerNo='" & txtUniformNo.Text & "'_ strFirstName='" & txtFirstName.Text & "'_ strLastName='" & txtLastName.Text & "'_ strParentName='" & txtParent.Text & "'_ strAddress='" & txtAddress.Text & "'_ strCity='" & txtCity.Text & "'_ strState='" & txtState.Text & "'_ strZipCode='" & txtZip.Text & "'_ strPhone='" & txtPhone.Text & "'_ intAge='" & txtAge.Text & "'", myConnection)

Answer 1

delete语句为Delete From Team Where...

建议您最好使用参数化查询来避免SQL Injection攻击

Answer 2

您的语法是错误的，因为您有多个WHERE条件，但没有使用正确的AND/OR将这些条件连接在一起。当然，DELETE * FROM table语法只被MS-ACCESS接受，但正确的SQL语法是DELETE FROM (不带*)，Access也接受它，因此最好使用该标准。

说您需要在命令中使用参数化查询，而不是字符串连接

Dim cmdText = "DELETE FROM Team " & _
              "WHERE intPlayerNo = @playerNo AND " & _
              "strFirstName = @firstName AND " & _ 
              ".... and so on with the other fields "
Dim myCommand As New OleDb.OleDbCommand(cmdText, myConnection)
myCommand.Parameters.Add("@playerNo", OleDbType.VarWChar).Value = txtUniformNo.Text
myCommand.Parameters.Add("@firstName", OleDbType.VarWChar).Value = txtFirstName.Text 
... continue with the other parameters required by the WHERE statement
myCommand.ExecuteNonQuery()

使用参数将保护您的代码不受Sql注入的影响，并避免在某些textbox值包含单引号时出现语法错误。

请记住，如果您的表有一个主键，那么WHERE可以简单地简化为只有PrimaryKeyFieldName = @Value