Error Trapping Code Using Ado Connections

Error Trapping code using ADO connections

Using standard error trapping methods, the connection wouldn't get closed.

Depends how you implement it. I usually do something like this:

Public Sub DoSomething()
    On Error GoTo CleanFail

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.Open

    ' do stuff

CleanExit:
    If Not conn Is Nothing Then conn.Close
    Exit Sub
CleanFail:
    ' handle errors - rollback transaction, etc.
    Resume CleanExit
End Sub

I'd call it "standard error trapping", and it has yet to fail me.

---

There are other ways though. ADODB.Connection has a pretty rich API that's quite seldom used. You could wrap it up in a class module and encapsulate it in a WithEvents field, like this:

Option Explicit

Private WithEvents MyConnection As ADODB.Connection
Private Const MyConnectionString As String = ""

Private Sub Class_Initialize()
    Set MyConnection = New ADODB.Connection
    MyConnection.ConnectionString = MyConnectionString
    MyConnection.Open
End Sub

Private Sub Class_Terminate()
    If MyConnection Is Nothing Then Exit Sub
    MyConnection.Close
    Set MyConnection = Nothing
End Sub

' public members...

Private Sub MyConnection_BeginTransComplete(ByVal TransactionLevel As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    Debug.Print "BeginTransComplete"
End Sub

Private Sub MyConnection_CommitTransComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    Debug.Print "CommitTransComplete"
End Sub

Private Sub MyConnection_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    Debug.Print "ConnectComplete"
End Sub

Private Sub MyConnection_Disconnect(adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    Debug.Print "Disconnect"
End Sub

Private Sub MyConnection_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    Debug.Print "ExecuteComplete"
End Sub

Private Sub MyConnection_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    Debug.Print "InfoMessage"
End Sub

Private Sub MyConnection_RollbackTransComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    Debug.Print "RollbackTransComplete"
End Sub

Private Sub MyConnection_WillConnect(ConnectionString As String, UserID As String, Password As String, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    Debug.Print "WillConnect"
End Sub

Private Sub MyConnection_WillExecute(Source As String, CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    Debug.Print "WillExecute"
End Sub

I'd think if there's an error at any point, the pError parameter will include all the details about it:

Say that class module is named DbConnection, then you could use it like this:

Public Sub DoSomething()
    With New DbConnection
        'do stuff
    End With
End Sub

And whenever the object goes out of scope (whether End With was hit, or execution jumped out of the With block for whatever the reason), the Class_Terminate handler should run and ensure the encapsulated connection gets closed.

VBA Error handling on ADODB Connection.Open

Not sure if this is it, but in the VBE window make sure the Tools...Options...General...Error Trapping option is set to "Break on Unhandled Errors". If it were set to "Break on All Errors" this may bypass your handlers.

ADO Error exception handling?

You should first check for EADOError, which are specific ADO-related exceptions, and then EDatabaseError, which are more general database exceptions. EDBEngineError is an old BDE exception class, and is not applicable any longer if you're not using the BDE.

Screen.Cursor := crSQLWait;
Fdm.QEndOfDay.SQL.Text := 'SELECT * FROM TEndOfDay';
try
  try
    Fdm.QEndOfDay.Open;
  except
    on E: EAdoError do
    begin
      // ADO specific error handling
    end;
    on E: EDatabaseError do
    begin
      // Generic database error handling
    end;
    on E: Exception do
    begin
      // Other exceptions (non-DB related)
    end;
  end;
finally
  // Revert cursor to default always.
  Screen.Cursor := crDefault;
end;

BDE vs ADO Database Error handling

As I said in a comment, you shouldn't scatter AdoConnections and datasets across multiple forms/ Instead you should put them in a datamodule then Use the datamodule's unit in your forms' units. That way, you will be able to connect TDataSources and db-aware components on the form to the datamodule's datasets.

The other thing you can do is to install an application-wide exception handler to centralize your exception handling, if that's what you want to do. Delphi's Application object has an OnException event which you can assign to your own exception handler using code like that shown below.

A downside of an application-wide exception handler is that it can be difficult in the OnException handler to identify which of your objects is actually responsible for causing the exception.

type
  TMainForm = class(TForm)
    [...]
    procedure FormCreate(Sender: TObject);
  private
    procedure ApplicationException(Sender: TObject; E: Exception);
  public
  end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
  Application.OnException := ApplicationException;
end;

procedure TMainForm.ApplicationException(Sender: TObject; E: Exception);
var
  AErrors : Errors;
  AError : Error;
  i : Integer;
  S : String;
begin
  Caption := 'Exception';
  if E is EDatabaseError then begin
    AErrors := DataModule1.AdoQuery1.Connection.Errors;
    for i := 0 to AErrors.Count - 1 do begin
      AError := AErrors.Item[i];
      S := Format('Number: %d, NativeError: %d, source: %s, description: %s',
        [AError.Number, AError.NativeError, AError.Source, AError.Description]);
      Memo1.Lines.Add(S);
    end;
  end;
end;

Database connection error - first-chance exception

The error is most likely caused by incorrect connection string. There are several canonical samples of connection string to SQL Server Express file .mdf (see MSDN pub: https://msdn.microsoft.com/en-us/library/jj653752%28v=vs.110%29.aspx):

Using localDB syntax:

<add name="ConnectionStringName"
    providerName="System.Data.SqlClient"
    connectionString="Data Source=(LocalDB)\v11.0;AttachDbFileName=|DataDirectory|\DatabaseFileName.mdf;InitialCatalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True" />

<add name="ConnectionStringName"
    providerName="System.Data.SqlClient"
    connectionString="Server=(LocalDB)\v11.0;Initial File Name=|DataDirectory|\DatabaseFileName.mdf;Database=DatabaseName;Trusted_Connection=True;MultipleActiveResultSets=True" />

Other possible options are shown below:

<add name="ConnectionStringName"
    providerName="System.Data.SqlClient"
    connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True"/>

<add name="ConnectionStringName"
    providerName="System.Data.SqlClient"
    connectionString="Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\DatabaseFileName.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True" />

Select the one pertinent to your case and make sure you are using correct syntax.

Also, keep in mind that Database (that file-based SQL Express DatabaseFileName.mdf) could be password-protected: if so, then you have to include this info into connection string (typically such connection string is stored in .config file for security purpose). In the regular code behind you should use a verbatim string starting with @ (in lieu of double backslash "\\").

Hope this may help. Best regards,

---

Related Topics