????? DATABASE ERRORS ?????

[rohitt]

how to trap runtime errors thrown by database like this field cant be null..when modifying database from vb. Is there any way to give our customized messages in place of runtime database thrown errors. Its nice to be important, but it's more important to be nice.

 

[CajunCenturion]

You should be able to trap these errors just as you would with any other run-time error.

Below is a sample of the error handler that I have used in a VB Program using ADO Objects with an Access Database. (The IsEven Function is just here to show the OnError construct used in the application, and how the error handler is called - same procedure used in all functions/procedures in the project)

Global gADO_Connect As New ADODB.Connection
Const ADO_ERROR = -2147467259

'=========================================================

Public Function IsEven(rInt_Value As Integer) As Boolean

On Error GoTo HandleError

IsEven = ((rInt_Value / 2) = (rInt_Value \ 2))

Exit Function

HandleError:

ErrMessage Trim(rInt_Value), "GlobalModule.IsEven"
Resume Next

End Function

'=========================================================

Public Sub ErrMessage(rStr_Err As String, rStr_Title As String)

Dim lRst_Error As ADODB.Error
Dim lStr_Msg As String
Dim lStr_ErrDesc As String
Dim lLng_ErrorNumb As Long

lLng_ErrorNumb = Err.Number
lStr_Msg = "PROC" & vbTab & ": " & rStr_Title & vbCrLf & vbCrLf & _
"REFER" & vbTab & ": " & rStr_Err & vbCrLf & vbCrLf & _
"ERROR" & vbTab & ": "

If (Err.Number = ADO_ERROR) Then
For Each lRst_Error In gADO_Connect.Errors
lStr_ErrDesc = lRst_Error.Description
If (Left(lStr_ErrDesc, 32) = "[Microsoft][ODBC Driver Manager]&quot Then
lStr_Msg = lStr_Msg & "[Microsoft][ODBC Driver Manager]" & vbCrLf
lStr_ErrDesc = vbTab & ": " & Mid(lStr_ErrDesc, 34)
End If
lStr_Msg = lStr_Msg & lStr_ErrDesc & vbCrLf & vbCrLf & vbTab & _
" (Source" & vbTab & vbTab & ": " & lRst_Error.Source & &quot" & _
vbCrLf & vbTab & _
" (SQL State" & vbTab & ": " & lRst_Error.SQLState & &quot" & _
vbCrLf & vbTab & _
" (NativeError" & vbTab & ": " & lRst_Error.NativeError & &quot" & vbCrLf
Next
Else
lStr_Msg = lStr_Msg & Err.Number & " -- " & Err.Source & vbCrLf & Err.Description
End If

lStr_Msg = lStr_Msg & vbCrLf & vbCrLf & "ACTION" & vbTab & ": " & _
"Please Notify System Administrator"
MsgBox lStr_Msg, vbExclamation + vbOKOnly, "An Error Has Occurred"

End Sub
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[abrewis]

If IsNull(trim(rs("Field&quot)) = False then
'### Do Event
else
msgbox ("This field is NULL", vbInformation)
End If

Use the IsNull function to check data either before being entered into the database or when being retrieved.
Hope this helps

 

[strongm]

Additionally, the ADO connection object has an InfoMessage event that is invoked by the service provider when it sends a warning or informational mesage. So you could trap and handle some genuine server errors at this point.

 

[sunaj]

strongm,

Could you show an example of that?

I tried doing an invalid select statement ('SELECT * FROM tblHumbug') on a connection with the code showed at

http://www.devguru.com/Technologies/ado/quickref/connection_infomessage.html,

but the infomessage event is not fired. A normal error is raised (method execute failed blah blah).

I'm very interested in trapping the real error text (Invalid object name 'tblhumbug')

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

 

[strongm]

As I mentioned, it only handles some errors. More specifically, it really only handles connection errors (as opposed to command errors), and even then only warnings rather than errors, which still throw an exception to the calling program. So it should only be considered as an additional way of dealing with unexpected situations rather than a replacement for more traditional error handling.

Here's an example of the sort of thing it spots with Access/Jet as a backend:
[tt]
Option Explicit
Dim WithEvents objConn As ADODB.Connection

Private Sub objConn_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)

Debug.Print pError.Description

End Sub

Private Sub Command1_Click()
Set objConn = New ADODB.Connection

objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"
objConn.Open , , , adAsyncConnect
Do Until objConn.State = adStateOpen
Loop

objConn.Close
Set objConn = Nothing
End Sub

 

[CCLINT]

The event is called when a ConnectionEvent was successfull and the provider returns additionalinformation.
adStatus returns an EventStatusEnum, and if the value is adStatusErrorsOccurred, then the errors collection pError will list up the errors.

Check if adStatus = adStatusErrorsOccurred. If it is, then loop through pError to see the errors returned.

 

[sunaj]

It is a bit strange that is't not possible to return the error message using the connection object... I've been looking for that before.

With SQL server it is possible through the SQLDMO:
----------------------------------------------------
Dim sd As New SQLServer
Dim q As QueryResults, m As String
sd.Connect "servername", "user", "password"
q = sd.ExecuteWithResultsAndMessages("select * from tblhumbug", , m)
-----------------------------------------------------

but its kind of a long way to go just to get the error messages - which is usually most useful while developing anyway. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CajunCenturion]

sunaj - are you able to access the ADODB.Errors collection within the connection object?

Dim lRst_Error As ADODB.Error

If (gADO_Connect.Errors.Count > 0) Then
For Each lRst_Error In gADO_Connect.Errors
With lRst_Error
lStr_ErrDesc = .Description
lStr_ErrDesc = lStr_ErrDesc & vbcrlf & _
"Source:" & & .Source & _
"SQL State:" & .SQLState & _
"NativeError:" & .NativeError
End With
Next
End If
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[zik]

Im having a problem and need help.

This is in relation to database. I have VB6.0 and Access 2000. When I put controls on a form and then the DATA CONTROL and then I click on the ellipsis next to the DatabaseName property of the data control and select the file I want to use.
When I set the RecordSource or try running the program it says "Unrecognisable Databse Format". but when a file made in previous version of Access is used it works

Can anyone help me here pliz...

thanx
ZIK
FIJI ISLANDS

zik000@hotmail.com

 

[sunaj]

CajunCenturion, what works to a certain extend. The error contains a number for the sqlstate and nativerror, but not a description nor a source....
Its actually the description that's interresting. This must be a common annoyance - when you are programming you get the 'method execute for connection failed' and you have to to e.g. query analyser in SQL server to find out what the problem actually is, instead of getting the error directly in VB - I can't be the only one that's annoyed by that -can I?
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[rohitt]

yeah i faced the same thing...

you need to update mdac so look for latest version of mdac_typ.exe at microsoft.com for ur OS.

next thing Access 2000 works with jet 4.0 ...so you have to download that also..
jet 4.0 is needed for ado..if you still want to use dao then you have to use dao 3.6 object library...look if both r not selected in the refrences..

n atlast you have to download and apply service pack 4 for vb 6.0 atleast..service pack 5 is also available...after applying this you can see that in connect field in properties of data control you can also get access 2000.

hope it ll work .. Its nice to be important, but it's more important to be nice.

 

[sunaj]

I'm using win2000. I've got MDAC 2.6 (which I know isn't the newest - but should be good enough). I've got jet 4.0 (I don't use DAO anymore) and service pack 5 for vb.
but I use SQL server: Microsoft SQL Server 2000 - 8.00.534

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