return database message on successful sql execution using VBScript

Question

I need to capture sql message upon successful execution of sql statement using vbscript. Here is my code.

Public Function ExecuteAPI_String(sql_Statement)
Dim num 
On Error Resume Next
Set objRecordSet = objConnection.Execute sql_Statement,num,adExecuteNoRecords
If Err.Number <> 0 Then
    ExecuteSQLStatement_String = Err.description
    objRecordSet.Close
    Err.Clear
Else
    ExecuteAPI_String = num & "records were affected"
    objRecordSet.Close
End If

Would have been helpful if you posted the resulting message you receive from ExecuteSQLStatement_String or ExecuteAPI_String. — user692942

user692942 user692942 · Accepted Answer · 2017-10-06T07:03:32

The problem here is the use of On Error Resume Next because it will cause any statement that errors to be skipped and set the Err object. If you comment out the On Error Resume Next you will see the actual error which will be nothing to do with the execution of your SQL query as this line;

Set objRecordSet = objConnection.Execute sql_Statement,num,adExecuteNoRecords

isn't a valid statement as the method needs to be surrounded by brackets like this;

Set objRecordSet = objConnection.Execute(sql_Statement, num, adExecuteNoRecords)

You will also need to make sure that all the values you're passing to the Execute() method are valid (such as adExecuteNoRecords being correctly defined).

Once you're sure the line is executing correctly, then you can uncomment the On Error Resume Next.

return database message on successful sql execution using VBScript

1 Answers