ADO Command running multiple SQL statements: can't get error message back: USE THE Connection.Errors collection

Question

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.

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.

SOLUTION: examine the connection.Errors collection.

This is an Access 2007 ADP front-end and a SQL 2005 database.

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.

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.

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.

--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...

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

Answer 1

Please see 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.

You have to explicitly set nocount on at the beginning of your sql script.

Answer 2

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.:

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