VBA交易 [英] VBA Transactions

问题描述

我正在尝试从Excel VBA向SQL中插入一些数据。 SQL命令是在VBA脚本过程中建立的，并包括一些SQL变量的使用。

I'm trying to insert some data into SQL from Excel VBA. The SQL commands are built up over the course of the VBA script, and include the use of some SQL variables.

我试图了解事务在VBA中的工作方式，以及它们是否可以满足我的需要，下面提供了可以测试该代码的代码，但它不起作用。它总是给我一个关于必须定义标量变量@name的错误，因此我认为这里的数据/事务范围存在问题。我怎样才能使这个简单的代码起作用？

I'm trying to understand how transactions work in VBA, and whether they will work with what I need to do, I have the code below that will test this, but it does not work. It always gives me an error about "Must define scalar variable @name" so I assume there is an issue here with the scope of the data/transaction. How can I get this simple code to work?

Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=ImportTest;" & _
"Data Source=localhost\sqlexpress"  

Set cn = New ADODB.Connection

With cn
   .CursorLocation = adUseClient
   .Open stADO
   .CommandTimeout = 0     
End With

cn.BeginTrans
cn.Execute "set implicit_transactions off"
cn.Execute ("declare @name varchar(100)")    
cn.Execute ("set @name='name'")    
cn.Execute ("Insert into test (id,name) values (55,@name)")
cn.CommitTrans    

cn.Close
Set cn = Nothing

推荐答案

您需要分批执行所有这些操作

you need to execute all these in 1 batch

cn.Execute "set implicit_transactions off"
cn.Execute ("declare @name varchar(100)")
cn.Execute ("set @name='name'")
cn.Execute ("Insert into test (id,name) values (55,@name)")

建立一个字符串，然后使用1 cn.Execute

built a string and then use 1 cn.Execute

更好的是，使用参数化查询来防止 SQL注入

Better yet, use parameterized queries to guard against SQL injection

这篇关于VBA交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！