Re: ADO error handling

In article <plruuuo0suic76e2r8a8h1omkq956ej2ma_at_4ax.com>, invalid_at_invalid.spam says...
> Can anyone tell me why error handlers not catcthing oracle unique
> contraint errors?
>
> Dim dbConnection As New ADODB.Connection
> Dim dbCommand As New ADODB.Command
> Dim dbRecordset As New ADODB.Recordset
> Dim strSQL As String ' SQL string
> Dim Err As ADODB.Error
> Dim strError As String
>
> On Error GoTo ErrorHandler
>
> strSQL = ""
> strSQL = strSQL & ""
> strSQL = "INSERT INTO PASS"
> strSQL = strSQL & " (pass_id,password,username,type)"
> strSQL = strSQL & " VALUES (autonum_password.nextval,Upper('" &
> UCase(Trim(Text2.Text)) & "'),"
> strSQL = strSQL & "upper('" & UCase(Trim(Text1.Text)) & "'),upper('" &
> cboType.Text & "'))"
>
> dbConnection.Mode = adModeReadWrite
> dbCommand.CommandText = strSQL
> dbRecordset.LockType = adLockOptimistic
> dbConnection.Open C_CONNECT_STRING
>
> dbConnection.BeginTrans
>
> dbCommand.ActiveConnection = dbConnection
> dbCommand.CommandType = adCmdText
>
> dbCommand.Execute
>
> dbConnection.CommitTrans
>
>
> ErrorHandler:
>
> For Each Err In dbConnection.Errors
> MsgBox "Error #" & Err.Number & vbCr & _
> " " & Err.Description & vbCr & _
> " (Source: " & Err.Source & ")" & vbCr & _
> " (SQL State: " & Err.SQLState & "), vbCritical, "Error"
> Debug.Print strError
> Next
>
> End Sub
>

A unique contraint error is provider-specific, not an ADO error. That means it won't cause a VB runtime error, and your ErrorHandler label will never be reached. You should either:

1. Check your dbConnection.Errors collection right after you Execute
2. Use the RecordsAffected parameter with Execute to make sure your insert succeeded.
   • Tom