Can you Test and trap for ADO SQL Open connection fail?

rsaritzky

Hi,

There are numerous posts here on how to create an ADO SQL connection. The general syntax is to define a variable of type "Automation" and subtype "Microsoft ActiveX Data Objects...", then

ADOConn.Create

then

ADOConn.Open(ConnectionString); //ConnectionString is the text of the connection string.

When the OPEN works successfully, of course there's no problem. However, when the connection fails, a "modal" error message is displayed. Here's a sample error:

This message is for C/AL programmers:

The call to member Open failed. Microsoft OLE DB Provider for SQL Server returned the following message:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.


My question is:

Is there a way to TRAP for a failed connection, so that my C/AL code can handle the error? The ADOConn.Open does not return a value directly.

DotNet-related articles on this subject refer to "try/catch" techniques (not available in C/AL) and additional return codes (adstate) AFTER the Open command. The problem is this "modal" error message that just causes the code to fail.

Has anyone figured out a way to test for a valid SQL connection in C/AL Code (doesn't have to be ADO)?

Thanks

ara3n

You need to run your code in a codeunit.

Call the Codeunit like this.


If YourCodeunit.run then

Success

else
Message('failed');

rsaritzky

Hi Rashed,

This ALMOST works. It turns out that if you are updating the database, you cannot use an "IF CODEUNIT.RUN..." in the middle of a write transaction. But it DOES avoid the "modal" error message. I'll keep you posted.

ara3n

Yes, it has to be a new transaction. you need to change the code.