Rdoerrors

[Gowri Sankar. J]

Rdoerrors object shows very generalized errors. how to make it to display

the specific errors. for example "it violates the primary key constraint... like".

[Osamah M. Abu-Romman]

The only way to do this is to trap the rdo error which is number 40002


Then you call a function like the following one:

NOTE: THE FOLLOWING FUNCTION WILL NOT WORK IN YOUR PROJECT. IT IS JUST TO GIVE YOU AN IDEA ON WHAT YOU NEED TO DO.


'

' PURPOSE:

' 1) This routine loops thru the rdo error object and returns

' a value indicating what type of errors occured

' and returns an appropriate number to the calling routine.

Function nCheckRDO() As Integer

' We need to start checking the rdo errors

Dim er As rdoError



' Word counter for parsing error message

Dim lnWordCounter As Integer



Dim lcRet As String



' the rdoErrors object contains a list of all errors

' which we could loop on and display all at once but

' we will not we will loop until we hit one of our errors

' that we are looking for and display it to the user

' and wait until he corrects it then if there is something

' else we will prompt him again when he resaves

For Each er In rdoErrors

' Dump the error description in the debug file so we know

' the exact description of the problem

DebugFile cMakeDots(Len(er.Description) + 32)

DebugFile "Exact Syntax of ODBC Error is: " + er.Description

DebugFile cMakeDots(Len(er.Description) + 32)



' Check the error number portion in the description

' string

If InStr(1, er.Description, "23000:", vbTextCompare) > 0 Then

' we either got a primary key violation or we entered a

' null value in a field that does not accept a null

' value.

If InStr(1, er.Description, "Violation of PRIMARY KEY", vbTextCompare) > 0 And InStr(1, er.Description, "duplicate key", vbTextCompare) > 0 Then

' it looks like the user attempted to enter a value that

' already exists in the table (a duplicate Pk)

nCheckRDO = PRIMARY_KEY_VIOLATION



' Done and get out of here

Exit Function



' check for null values in fields that do not allow nulls

ElseIf InStr(1, er.Description, "may not be null", vbTextCompare) > 0 Or InStr(1, er.Description, "does not allow nulls", vbTextCompare) > 0 Then

' Prompt the user

nCheckRDO = NULL_VIOLATION



' Done and get out of here

Exit Function



ElseIf InStr(1, er.Description, "INSERT statement conflicted with TABLE FOREIGN KEY", vbTextCompare) > 0 Then

' This usually occurs when you attempt to insert a record

' that violates a foreign key constraint i.e. one of

' the fields being inserted does not have refrence in

' the other table

nCheckRDO = INSERT_FK_VIOLATION



' Get the constraint name

lcRet = cGetConstraintName(er.Description)



' Done and get out of here

Exit Function



ElseIf InStr(1, er.Description, "TABLE CHECK constraint") > 0 Then



lcRet = cGetConstraintName(er.Description)



' Prompt the user

nCheckRDO = CONSTRAINT_VIOLATION



' Done and get out of here

Exit Function

ElseIf InStr(1, er.Description, "DELETE statement conflicted with") > 0 Then

' This error usually occurs when we try to delete a

' record that has a record in another table that refrences

' to it (Foreign Key Validation)

' We need to obtain the constraint name

lcRet = cGetConstraintName(er.Description)



nCheckRDO = FOREIGN_KEY_VIOLATION



' Done and get out of here

Exit Function



End If



ElseIf InStr(1, er.Description, "01000:", vbTextCompare) > 0 Then

' first check if this is as a result of not oppening

' a connection or it is just notifying that the command

' aborted

If InStr(1, er.Description, "ConnectionOpen", vbTextCompare) > 0 Then

nCheckRDO = SERVER_NOT_FOUND



' Done and get out of here

Exit Function



Else



' Just simply ignore it. This is just telling us

' that the command aborted but the next rdo will have the

' full description. So do nothing and the next error

' will get evaluated

End If



ElseIf InStr(1, er.Description, "22005:", vbTextCompare) > 0 Then

' This usually occurs if we entered an incompatible

' value with the destination field so we need to

' prompt the user.

nCheckRDO = INVALID_DATA_TYPE



' Done and get out of here

Exit Function



ElseIf InStr(1, er.Description, "37000:", vbTextCompare) > 0 Then



If InStr(1, er.Description, "dump", vbTextCompare) > 0 Then

' This usually occurs if the database ran out of space and usually

' the reason is that the transaction log became full so it needs

' to be dumped and it is a good idea to perform a backup right

' after we dump the transaction log.

nCheckRDO = DB_OUT_OF_SPACE



' Done and get out of here

Exit Function

' This error usually occurs if the query

' processed contains characters for a numeric field

' NOTE: Same as error S0022 and it depends on the invalid

' characters entered which one will fire

ElseIf InStr(1, er.Description, "Incorrect syntax near", vbTextCompare) > 0 Then

nCheckRDO = INVALID_COLUMN_NAME



' Done and get out of here

Exit Function

ElseIf InStr(1, er.Description, "is too long.", vbTextCompare) > 0 Then

nCheckRDO = TOO_LONG_VALUE



' Done and get out of here

Exit Function

ElseIf InStr(1, er.Description, "Implicit conversion from datatype 'text' to 'varchar' is not allowed", vbTextCompare) > 0 Then

nCheckRDO = INVALID_STRING_VALUE



' Done and get out of here

Exit Function

ElseIf InStr(1, er.Description, "Month End done successfully", vbTextCompare) > 0 Then

nCheckRDO = MONTH_END_COMPLETE



Exit Function

End If





ElseIf InStr(1, er.Description, "08001:", vbTextCompare) > 0 Then

' This usually occurs if the client could not connect to the

' sql server. This usually happens if the server went down

' and up or went down and never came up

nCheckRDO = SERVER_NOT_FOUND



' Done and get out of here

Exit Function

ElseIf InStr(1, er.Description, "IM002", vbTextCompare) > 0 Then

' This occurs when the ODBC SQL DRIVER was not set or the name specified

' does not exist. The setup is done thru windows control panel

nCheckRDO = DSN_NOT_FOUND



' Done and get out of here

Exit Function

ElseIf InStr(1, er.Description, "S0002:", vbTextCompare) > 0 Then

' This error usually occurs if the table specified does not exist

If InStr(1, er.Description, "invalid object name", vbTextCompare) > 0 Then

nCheckRDO = TABLE_NOT_FOUND



' Done and get out of here

Exit Function

End If

ElseIf InStr(1, er.Description, "S0022:", vbTextCompare) > 0 Then

' This error usually occurs if the query

' processed contains characters for a numeric field

If InStr(1, er.Description, "Invalid column name", vbTextCompare) > 0 Then

nCheckRDO = INVALID_COLUMN_NAME



' Done and get out of here

Exit Function

End If



ElseIf InStr(1, er.Description, "42000:", vbTextCompare) > 0 Then

If InStr(1, er.Description, "permission denied", vbTextCompare) > 0 Then

' This error occurs when we do not have sufficient permission

' to access the specified table

nCheckRDO = PERMISSION_DENIED



' Done and get out of here

Exit Function

End If



ElseIf InStr(1, er.Description, "08004:", vbTextCompare) > 0 Then

If InStr(1, er.Description, "The maximum number of", vbTextCompare) > 0 Then

' This error occurs when the number of users connected

' to the specified server exceeds the number of allowed

nCheckRDO = EXCEEDED_MAX_NUM_OF_CONNECTIONS



' Done and get out of here

Exit Function



End If



ElseIf InStr(1, er.Description, "22003:", vbTextCompare) > 0 Then

' This error occurs when a user enters a large value as

' as a query on a field that does not support that large

' number eg entering 52000125 to query on an integer field

nCheckRDO = ARITHMATIC_OVERFLOW



Exit Function

End If





Next



' If we reached here that means we got an unexpected error

' that we do not look for here

nCheckRDO = UNKNOWN_RDO_ERROR



' Dump this errors in the debug file

For Each er In rdoErrors

DebugFile er.Description, True

DebugFile "ODBC ERROR NUMBER IS: " + Str(er.Number)

Next



End Function



After calling the previous function according to the number it returns you can display a user friendly message instead of the technical message the rdo object displays.