在 Visual Basic for MSAccess 中链接 Sql 命令

Question

使用Visual Basic和SQL将数据插入MS Access数据库.....我有以下代码，如果表中尚不存在 @username 的值，则仅添加该值。

cmd.CommandText = "INSERT INTO tbl (CustomerID) SELECT @username FROM(SELECT COUNT(*) FROM tbl) As v WHERE Not EXISTS(Select 1 FROM tbl as t WHERE t.CustomerID = @username)"

但是，我希望插入另一个不需要相同验证检查的值。此值存储在@password

cmd.CommandText = "INSERT INTO tbl (CustomerID, Password) SELECT @username FROM(SELECT COUNT(*) FROM tbl) As v WHERE Not EXISTS(Select 1 FROM tbl as t WHERE t.CustomerID = @username VALUES (@username, @password)"

我已经尝试了上述方法，但它不起作用。

总之，我只想将@username添加到数据库中并在同一行中@password，但只有@username通过检查。

编辑： 我尝试过cmd.CommandText = "INSERT INTO tbl (CustomerID, Password) SELECT @username, @password FROM (SELECT COUNT(*) FROM tbl) As v WHERE Not EXISTS(Select 1 FROM tbl as t WHERE t.CustomerID = @username)"但收到语法错误。

Answer 1

SQL 命令中出现错误：

..1 FROM tbl as t WHERE t.CustomerID = @username VALUES (@username, @password)"
`------------------------------------------------^

如果这条线适合您。

cmd.CommandText = "INSERT INTO tbl (CustomerID) 
SELECT @username 
FROM
(
SELECT 
COUNT(*) 
FROM tbl
) As v 
WHERE Not EXISTS(Select 1 FROM tbl as t WHERE t.CustomerID = @username)"

那么这一行也应该有效：

cmd.CommandText = "INSERT INTO tbl (CustomerID, password) 
SELECT 
@username,
@password
FROM
(
SELECT 
COUNT(*) 
FROM tbl
) As v 
WHERE Not EXISTS(Select 1 FROM tbl as t WHERE t.CustomerID = @username)"

Answer 2

这可能不是您正在寻找的解决方案，但根据需要，我会考虑如下所示。我认为通过查找@username并在单独的 SQL 行中添加@password值来将其添加到表中。抱歉，如果我误解了什么，但是如果您可以绕过逻辑检查，为什么不直接添加sql的第二行以将值添加到@username = tbl.username的表中。

像这样： 更新 tbl 设置 [密码] = @password 其中 [用户名] = @username