Return database messages on successful SQL execution when using ADO

Question

I'm working on a legacy VB6 app here at work, and it has been a long time since I've looked at VB6 or ADO. One thing the app does is to executes SQL Tasks and then to output the success/failure into an XML file. If there is an error it inserts the text the task node.

What I have been asked to do is try and do the same with the other mundane messages that result from succesfully executed tasks, like (323 row(s) affected).

There is no command object being used, it's just an ADODB.Connection object. Here is the gist of the code:

Dim sqlStatement As String
Set sqlStatement = /* sql for task */

Dim sqlConn As ADODB.Connection
Set sqlConn = /* connection magic */

sqlConn.Execute sqlStatement, , adExecuteNoRecords

What is the best way for me to capture the non-error messages so I can output them? Or is it even possible?

Answer 1

I think the rows affected is a function of the Query Analyzer/Enterprise manager, and not something returned through the API.

If I recall correctly, using classic ADO, we had to do a MoveLast then a MoveFirst to force all of the records to come over the wire, then do a count of the Recordset.

I also remember something about which cursor type being used affecting the count of records coming back.

Other than that, are you trying to grab the print statement... It seems like you are using no stored procedures, so beyond count, what are you expecting to get?

Answer 2

Besides having a generic error handler in your routines the ADO connection object has an Errors collection. After performing some action check the errors for a count > 0 and if it is you need to iterate the collection and log all the errors. There is a Clear method if you want to continue after logging.

After making a quick test project I found that declaring my variable using WithEvents VB adds the InfoMessage event. I ran a DBCC CHECKDB command and the InfoMessage event fired once. The pConnection variable had 284 errors in it with all the other messages.

note: the Connection.Errors collection is 0 based.

Answer 3

The number of rows affected is returned through an optional second argument of the Connnection object's Execute method

Dim num As Long    
sqlConn.Execute sqlStatement, num, adExecuteNoRecords     
MsgBox num & " records were affected"