Sql Update语句出错 [英] error with Sql Update statement

问题描述

你好，

我的vba代码有错，但我不知道在哪里..请帮帮我

Hello,
I have an error in my vba code but I don''t know where.. help me please

Set MyTable2 = MyDB.OpenRecordset("SELECT * FROM [tblRoutingMain] " & _
                                "WHERE ((([tblRoutingMain].[QUOTE NUMBER])='" & [QUOTE NUMBER] & "') AND (([tblRoutingMain].COMPPN)='" & COMPPN & "')) " & _
                                "ORDER BY tblRoutingMain.ROUTING ASC;")
    
  If Not MyTable2.EOF Then MyTable2.MoveFirst
  
 
      strUpdateQuery = "UPDATE tblRoutingMain SET  tblRoutingMain.[SEQ] = '" & MyTable2.Fields("SEQ") & "', " & _
                       "WHERE ((([tblRoutingMain].[QUOTE NUMBER])='" & [QUOTE NUMBER] & "CONT1" & "') AND (([tblRoutingMain].COMPPN)='" & COMPPN & "') AND (tblHardwareCost.[ROUTING]='" & MyTable2.Fields("ROUTING") & "'))" & _
                       "ORDER BY tblRoutingMain.ROUTING ASC;"
      CurrentDb.Execute strUpdateQuery, dbFailOnError

推荐答案

你真的应该考虑使用SqlParameters。它更容易编写，更易于阅读/调试，更安全。添加变量作为sql-query的一部分会使它容易受到sql注入。



但要调试这个，我要做的第一件事就是看到执行前 strUpdateQuery 变量的结果。如果你看到你想要执行什么，你会看到什么是错的。



你为什么要在更新查询中订购？

You really should consider using SqlParameters. It''s easier to write, easier to read/debug and more secure. Adding variables as a part of a sql-query will make it vulnerable for sql-injections.

But to debug this, the first thing I would do is to see the result of strUpdateQuery variable before it is executed. You will see what''s wrong if you see what you are trying to execute.

And why are you doing ordering in an update query?

"ORDER BY tblRoutingMain.ROUTING ASC"

我获得了这段代码：

<pre lang="sql">Set MyTable2 = MyDB.OpenRecordset("SELECT * FROM [tblRoutingMain] " & _
                               "WHERE ((([tblRoutingMain].[QUOTE NUMBER])=''" & [QUOTE NUMBER] & "'') AND (([tblRoutingMain].COMPPN)=''" & COMPPN & "'')) " & _
                               "ORDER BY tblRoutingMain.ROUTING ASC;")

 If Not MyTable2.EOF Then MyTable2.MoveFirst


     strUpdateQuery = "UPDATE tblRoutingMain SET  tblRoutingMain.[SEQ] = ''" & MyTable2.Fields("SEQ") & "'' " & _
                      "WHERE ((([tblRoutingMain].[QUOTE NUMBER])=''" & [QUOTE NUMBER] & "CONT1" & "'') AND (([tblRoutingMain].COMPPN)=''" & COMPPN & "'') AND (tblHardwareCost.[ROUTING]=''" & MyTable2.Fields("ROUTING") & "''))"

     CurrentDb.Execute strUpdateQuery, dbFailOnError

我还有其他错误..参数很少。预期1

I have an other error.. to few parameters. expected 1

这篇关于Sql Update语句出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！