vba 运行多个 SQL 语句的 ADO 命令：无法返回错误消息：使用 Connection.Errors 集合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10519912/
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
ADO Command running multiple SQL statements: can't get error message back: USE THE Connection.Errors collection
提问by ajd
I have some triggers on various tables that use raiserror to tell the user that he tried to do something bad. I would like to run several inserts and updates atomically, rolling back if one of the triggers says so. I was evil in a former life so I'm using Access VBA.
我在各种表上有一些触发器，它们使用 raiserror 告诉用户他试图做坏事。我想以原子方式运行多个插入和更新，如果其中一个触发器这样说，则回滚。我前世很邪恶，所以我使用的是 Access VBA。
If the first statement is at fault, I get a run time error in my VBA. However if the second or subsequent statement calls raiserror then I can't see any error message.
如果第一条语句有问题，我的 VBA 就会出现运行时错误。但是，如果第二个或后续语句调用 raiserror 则我看不到任何错误消息。
SOLUTION: examine the connection.Errors collection.
解决方案：检查 connection.Errors 集合。
This is an Access 2007 ADP front-end and a SQL 2005 database.
这是一个 Access 2007 ADP 前端和一个 SQL 2005 数据库。
The VBA below is the bare minimum to show the effect. I get the same effect if I wrap all my atomic statements in a stored proc.
下面的 VBA 是显示效果的最低要求。如果我将所有原子语句包装在存储过程中，我会得到相同的效果。
I thought i was being clever, putting my validation as close to the database as possible. Obviously not!
我以为我很聪明，将我的验证尽可能靠近数据库。显然不是！
If you care to try out the code below, swap the order of s1 and s2 in CommandText and admire the different results.
如果您想尝试下面的代码，请交换 CommandText 中 s1 和 s2 的顺序并欣赏不同的结果。
Public Sub TestSqlErrors2()
'CREATE TABLE [dbo].[tblStuff]([id] [int] IDENTITY primary key NOT NULL,[stuff] [varchar](255) NULL)
Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim r As Long
Dim s1 As String
Dim s2 As String
s1 = " insert into tblStuff(stuff) values ('aaa') "
s2 = " if 1=1 begin raiserror ('me, i''m just a lawnmower',16,1) rollback transaction end "
Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
'cnn.BeginTrans 'makes no difference
cmd.CommandText = "begin transaction " & s2 & s1 & " commit transaction "
cmd.Execute r
'cnn.CommitTrans
'cnn.Errors.count is either 2 or 0
End Sub
I've tried various combinations. In the stored proc below I'm explicitly (re-) raising an error. However, that error doesn't get passed back to VBA.
我尝试了各种组合。在下面的存储过程中，我明确（重新）引发错误。但是，该错误不会传递回 VBA。
--CREATE TABLE [dbo].[tblStuff]([id] [int] IDENTITY primary key NOT NULL,[stuff] [varchar](255) NULL)
alter proc AddMoreStuff2
as
begin try
begin transaction
--swap the two statements below around to see the difference
--if the first statement is raiserror then a run-time error appears in VBA
--if not, no error appears in VBA, although no records are added
insert into tblStuff(stuff) values ('aaa')
if 1=1 begin raiserror ('me, i''m just a lawnmower',16,1) rollback transaction end
commit transaction
end try
begin catch
declare @errormessage varchar(1024)
set @errormessage = error_message()
raiserror ( @errormessage, 16, 1 )
end catch
The VBA code to call this new proc is...
调用这个新过程的 VBA 代码是...
Public Sub TestSqlErrors2()
Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim r As Long
Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "exec AddMoreStuff2"
cmd.Execute r
End Sub
采纳答案by Tim Mercer
Please see http://support.microsoft.com/kb/254304
请参阅http://support.microsoft.com/kb/254304
PRB: ADO Errors Collection Does Not Contain User-Defined Error Messages
Executing a SQL Server stored procedure (SP) by using ActiveX Data Objects (ADO) does not populate the ADO Errors collection of the Connection object with user-defined errors that are raised in the SP. This behavior only occurs when using the OLE DB Provider for SQL Server (SQLOLEDB) to establish an ADO connection to the SQL Server database.
PRB：ADO 错误集合不包含用户定义的错误消息
使用 ActiveX 数据对象 (ADO) 执行 SQL Server 存储过程 (SP) 不会使用 SP 中引发的用户定义错误填充 Connection 对象的 ADO 错误集合。仅当使用 OLE DB Provider for SQL Server (SQLOLEDB) 建立到 SQL Server 数据库的 ADO 连接时，才会发生此行为。
You have to explicitly set nocount on at the beginning of your sql script.
您必须在 sql 脚本的开头显式设置 nocount on。
回答by ajd
DAO does not raise a VBA error but it does populate the connection .Errors collection. So I can check this after each Execute call to get the error description. e.g.:
DAO 不会引发 VBA 错误，但会填充连接 .Errors 集合。所以我可以在每次 Execute 调用后检查这个以获取错误描述。例如：
Dim errorMessage As String
Dim e As Object
For Each e In cnn.Errors
errorMessage = errorMessage & e.Description
Next
If Len(errorMessage) > 0 Then
Err.Raise 64000 - 1, Left(cmd.CommandText, 20), errorMessage
End If
