
return the result text from a VB ADODB.Connection.Execute command
Hi All
I am trying to run SQL scripts via VB & ADO.
Whilst I can run the scripts successfully uising the following pretty
standard VB Function, I want to be able to return the SQL result message.
I can return Errors by intercepting the Err.Description when they occur, but
not the success.
(e.g. (11 row(s) affected)).
So essentially my question is, how do you return the result text from a VB
ADODB.Connection.Execute command?
Public Function SqlRunScript(Provider As String, InitialCatalog As String, _
DataSource As String, _
LoginName As String, _
LoginPassword As String, _
strScriptFile As String) As
Boolean
On Error GoTo ErrorHandler
Dim strCmd As String
Dim strBatch As String
Dim cnnSQL As ADODB.Connection
Set cnnSQL = New ADODB.Connection
cnnSQL.Open "PROVIDER= " & Provider & "Data Source=" & DataSource & _
";Initial Catalog='" & InitialCatalog & _
"';User Id=" & LoginName & ";_
Password=" & LoginPassword & ";"
Open strScriptFile For Input As #1
Do While Not EOF(1)
Line Input #1, strCmd
If (Left(strCmd, 2) <> "GO") Then
strBatch = strBatch + " " + strCmd
Else
If strBatch <> "" Then
cnnSQL.Execute strBatch
TxtResults = TxtResults & strBatch & vbcrlf
TxtResults.Refresh
strBatch = ""
End If
End If
Loop
Close #1
cnnSQL.Close
Set cnnSQL = Nothing
SqlRunScript = True
Exit Function
ErrorHandler:
MsgBox "Error in SqlRunScript !" & vbCrLf & Err.Description
TxtResults = TxtResults & Err.Description & vbcrlf
SqlRunScript = False
Resume Next
End Function
Thanks in advance
Brian