0

I put together a function (in VBScript in an ASP Classic website) to open a new MS SQL ADO connection, and found that if error handling is enabled, an error code of 424, with a description of "Object Required" occurs. I looked up this error, and everything that I find on it, is relative to Microsoft Excel. A valid and working connection object is still created, but you cannot make decisions in code (using the error response), to ascertain whether or not a proper connection object was created.

My question is, is this the expected behavior, and if yes, how would I go about making sure that the connection object has been instantiated correctly? Please note that when I tried to contact Microsoft about this behavior, the following statement appeared on the page:

Post VBA programming questions to Stack Overflow by using the vba tag, along with any other relevant tags.

The code snippet from the function is included as follows:

On Error Resume Next

' Instantiate the new DB Connection object:
Set objTemporary = Server.CreateObject("ADODB.Connection")

objTemporary.ConnectionTimeout = cServerConnectionTimeout
objTemporary.CommandTimeout = cServerCommandTimeout
objTemporary.ConnectionString = sSQLConnectionString
objTemporary.Mode = adModeReadWrite
objTemporary.Open

If Debug_OpenDBConnection = True Then
    Response.Write("<br>LINE-336 (dataaccess.asp): This is the value of the objTemporary object: ") & objTemporary & "<br>"
    Response.End
End If

' Check to see if an error occurred:
If Err.Number <> 0 Then
    If Debug_OpenDBConnection = True Then
        Response.Write("<br>LINE-345 (dataaccess.asp): This is the value of Err.Number: ") & Err.Number & "<br>"
        Response.Write("This is the value of Err.Source: ") & Err.Source & "<br>"
        Response.Write("This is the value of Err.Description: ") & Err.Description & "<br>"
        Response.End
    Else
        Redim aOpen_DB_Connection(4)
        aOpen_DB_Connection(0) = False
        aOpen_DB_Connection(1) = 2
        aOpen_DB_Connection(2) = Err.Number
        aOpen_DB_Connection(3) = Err.Source
        aOpen_DB_Connection(4) = Err.Description
    
        OpenDBConnection = aOpen_DB_Connection
        Exit Function
        Response.End
    End If

Else ' No error occured, so exit function, returning the new connection object:

    Redim aOpen_DB_Connection(1)
    aOpen_DB_Connection(0) = True
    aOpen_DB_Connection(1) = objTemporary

    OpenDBConnection = aOpen_DB_Connection
    Exit Function
    Response.End

End If

More of the function's code can be included if necessary, including the contents of the sSQLConnectionString variable. Of interest is the fact that the error reporting object will work fine (report the correct type of error) if indeed a second OPEN command is subsequently issued anytime after the first object has been opened and before it has been closed. This seems to be because the object is available for the error reporting mechanisms to function.

PaulScott
  • 117
  • 8
  • 4
    Side note: `On Error Resume Next ' Enable error trapping` - no, that's not what `On Error Resume Next` does. – BigBen Jan 08 '21 at 21:21
  • @BigBen Noted and removed. Thank you very much! – PaulScott Jan 08 '21 at 21:34
  • 2
    My comment was more than a blanket `On Error Resume Next` is normally a bad idea, it just silently hides a potential error. – BigBen Jan 08 '21 at 21:36
  • @BigBen Actually, in this case, without specifying "On Error Resume Next", then the Err Object will not be populated. I don't use it on the top of pages, and only turn it on whenever I am performing MS-SQL ADO and database operations. – PaulScott Jan 08 '21 at 21:39
  • 2
    @PaulScott It is equally bad with MS-SQL ADO and database operations as it is with something else. – GSerg Jan 08 '21 at 22:49
  • 3
    You are missing `Set` on `aOpen_DB_Connection(1) = objTemporary`. – GSerg Jan 08 '21 at 22:58
  • @GSerg Thank you for your comment. (1) This is the method as documented by Microsoft. Why is it bad?; and (2) The connection object is created and correctly returned via the array (the name of the function is OpenDBConnection). That's not the issue. The question is how to detect if an error has occurred during the object creation instantiation. That is, if there is a mistake in the connection string, or the server is not available, isn't there a way to detect that? – PaulScott Jan 08 '21 at 23:31
  • 2
    @PaulScott You are in fact using VBScript, not VBA with which you tagged your question originally. Yes, you [have to](https://stackoverflow.com/q/157747/11683) use `On Error Resume Next` in VBScipt. The `Set` is an issue because what you are saving in the array is [the default property](https://stackoverflow.com/a/17877644/11683) of the connection object, which is `ConnectionString`, not the connection object itself. – GSerg Jan 09 '21 at 07:10
  • 2
    @PaulScott You should be looking at the `ADODB.Connection` objects `Errors` collection property, there is no need to be using `On Error Resume Next` as that just ignores VBScript runtime errors by skipping the line that has errored and populating the `Err` object to then be manually checked. – user692942 Jan 09 '21 at 08:50
  • 1
    Also @GSerg is right about setting your temporary object in the array without `Set` it will error with an `Object Required` runtime error. – user692942 Jan 09 '21 at 08:55
  • @user692942 `On Error Resume Next` has to be used in VBScript because otherwise the script will just terminate at the first error. Your suggestion would be correct if ADO didn't raise an exception when its `Errors` collection has something, but it does, so before you can get to examine the `Errors`, you must suppress the exception. – GSerg Jan 09 '21 at 09:32
  • @GSerg that’s actually not true, the `Errors` collection in `ADODB` is accessible without needing to use `On Error Resume Next` see the suggested duplicate. To be perfectly honest their whole approach is overly elaborate for what they are trying to do and just makes it more difficult to pin down the actual problem oppose to what they think the problem is. – user692942 Jan 09 '21 at 11:03
  • @user692942 That is because they are raising the error with severity 10, which is [considered](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-ver15#levels-of-severity) an informational message, and are doing so in the third resultset which is not immediately visible. Change the command to `conn.Execute("raiserror ('xyz', 16, 127)")` (error level 16 in the *first* resultset) and see what happens. – GSerg Jan 09 '21 at 11:14
  • I apologize for not making it clear that this is VBScript, specifically ASP Classic. This is a lot to absorb, and very helpful. I will be running a number of tests based upon this excellent feedback, and then will update the question or provide an answer, based upon my findings. Thank you! – PaulScott Jan 11 '21 at 00:07

0 Answers0