• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 51
  • Last Modified:

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
0
Sat Nuna
Asked:
Sat Nuna
  • 3
  • 2
1 Solution
 
Ryan ChongCommented:
you got to refer to Err.Description in particular to get the exact error.

But not only when internet cable is disconnected but also when sql syntax error happened , the code show ORACLE CONNECTING ERROR.
can you put a break point in your codes and see what's the State of connection when sql syntax error was happened?

or you can try refer to adStateClosed state instead?

If OraCon.State <> adStateClosed Then
        MsgBox "ORACLE CONNECTING ERROR"
    Else
        MsgBox "ORACLE SQL SYNTAX ERROR"
    End If

Open in new window

0
 
Sat NunaAuthor Commented:
Than you letting me know using Err.Description. Err.Description was ORA-03113 when internet cable is disconnected.
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.
0
 
Ryan ChongCommented:
coool, and you can refer to Err.Number when necessary, see if you get 03113 or similar value there?
0
 
Sat NunaAuthor Commented:
I like to use ERR.Description.
Thank you very much.
0
 
Ryan ChongCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now