Sat Nuna
asked on
How to differentiate error using adodb.recordset in vba
Hi experts,
How can I differentiate oracle error between connection error and other errers in vba.
I tried the code bellow. But not only when internet cable is disconnected but also when sql syntax error happened , the code show ORACLE CONNECTING ERROR.
Public Function TEST() As Boolean
Dim strSql As String
Dim oraDS As OraDynaset
On Error GoTo ERR
strSql = ""
strSql = " select COLUMN from TALE "
Set OraRec = New ADODB.Recordset
OraRec.Open strSql, OraCon, adOpenKeyset, adLockReadOnly
Exit Function
ERR:
If OraCon.State <> adStateOpen Then
MsgBox "ORACLE CONNECTING ERROR"
Else
MsgBox "ORACLE SQL SYNTAX ERROR"
End If
End Function
How can I differentiate oracle error between connection error and other errers in vba.
I tried the code bellow. But not only when internet cable is disconnected but also when sql syntax error happened , the code show ORACLE CONNECTING ERROR.
Public Function TEST() As Boolean
Dim strSql As String
Dim oraDS As OraDynaset
On Error GoTo ERR
strSql = ""
strSql = " select COLUMN from TALE "
Set OraRec = New ADODB.Recordset
OraRec.Open strSql, OraCon, adOpenKeyset, adLockReadOnly
Exit Function
ERR:
If OraCon.State <> adStateOpen Then
MsgBox "ORACLE CONNECTING ERROR"
Else
MsgBox "ORACLE SQL SYNTAX ERROR"
End If
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
coool, and you can refer to Err.Number when necessary, see if you get 03113 or similar value there?
ASKER
I like to use ERR.Description.
Thank you very much.
Thank you very much.
just a note for correction.
I think I got my comment wrong for adStateClosed condition:
>>If OraCon.State <> adStateClosed Then
>>MsgBox "ORACLE CONNECTING ERROR"
>>Else
>>MsgBox "ORACLE SQL SYNTAX ERROR"
>>End If
it should be as:
If OraCon.State = adStateClosed Then
MsgBox "ORACLE CONNECTING ERROR"
Else
MsgBox "ORACLE SQL SYNTAX ERROR"
End If
I think I got my comment wrong for adStateClosed condition:
>>If OraCon.State <> adStateClosed Then
>>MsgBox "ORACLE CONNECTING ERROR"
>>Else
>>MsgBox "ORACLE SQL SYNTAX ERROR"
>>End If
it should be as:
If OraCon.State = adStateClosed Then
MsgBox "ORACLE CONNECTING ERROR"
Else
MsgBox "ORACLE SQL SYNTAX ERROR"
End If
ASKER
So I tried to modify my code bellow, And It worked.
Thanks.
If Mid(ERR.Description, 5, 5) = "03113" Then
MsgBox "ORACLE CONNECTING ERROR"
Else
MsgBox "ORACLE SQL SYNTAX ERROR"
End If
If there is something wrong with this code , please let me know.