Error number in VB and SQL Server

[glenv]

Is the err.number generated by VB coming from an SQL server database unique?

Bec. i frequently encounter the same error numbers with different descriptions.

Is there any way i can capture a unique error number?

Thanks

 

[sunaj]

Yes.

Use the ADO error collection. The 'Native error' corresponds to the SQL error (Try 'select * from master.dbo.sysmessages')

-----------------------------------------------------
Dim conn As Connection
Dim rst As Recordset
On Error GoTo ErrHndl:
    Set conn = New Connection
    conn.Open "Provider=SQLOLEDB;Password=YourPassW;Persist Security Info=False;User ID=YourID;Initial Catalog=YourDB;Data Source=YourSQLServer"
    Set rst = New Recordset
    rst.Open "SELECT * FROM tblCrap", conn, adOpenStatic, adLockOptimistic
    rst.Close
    Set rst = Nothing
    conn.Close
    Set conn = Nothing
    Exit Sub
ErrHndl:
    Dim MyErr As ADODB.Error
    For Each MyErr In conn.Errors
        MsgBox "Error Number: " & MyErr.Number & vbCrLf & _
            "Description: " & MyErr.Description & vbCrLf & _
            "Source: " & MyErr.Source & vbCrLf & _
            "SQL State: " & MyErr.SQLState & vbCrLf & _
            "Native Error: " & MyErr.NativeError
    Next MyErr
-----------------------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'