Rdoerrors object shows very generalized errors. how to make it to display
the specific errors. for example "it violates the primary key constraint... like".
Printable View
Rdoerrors object shows very generalized errors. how to make it to display
the specific errors. for example "it violates the primary key constraint... like".
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.