Problem with error handling

[Jokada]

I want my program to send me am email when something goes wrong. the program and the handler work fine when i put them in separate files.
But when i join them the code below doesn't seem to work.

On Error Goto MyErrorHandler
My program
.....
.....

MyErrorHandler:
here the code for sending the email
.....

Any ideas how i should solve this problem??
kind regards
jokada

 

[tsuji]

Hello Jokada,

Just a pragmatic solution, something like this:

On Error Resume Next
My program
.....
.....
'can be somewhere within the My program
'consider a strategic positioning
if err.number<>0 then
MyErrorHandler
end if
'can have some other codes from My program continuing
.....
.....

Sub MyErrorHandler
here the code for sending the email
.....
End Sub

regards - tsuji

 

[Jokada]

Hmm the error handler works now but when it triggers an error it doesn't stop sending emails
here is the entire code

On Error Resume Next

Begintime = Now()
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "Select printerName, drivername, servername from " _
& " 'LDAP://DC=tke,DC=intra' where objectClass='printQueue'"
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 30
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute
If Err.Number<>0 Then
MyErrorHandler
End If

arrPrinter = objRecordSet.GetRows()

objConnection.Close()

iprinterCount = 0

Set MyConnection = CreateObject("ADODB.Connection")

'SQL Connection String
MyConnection.Open "Driver={SQL Server};server=(local);database=IPBD_TEST;uid=SA;pwd=;"
'MyConnection.Open "Driver={SQL Server};server=bla;database=IPBD_TEST;uid=SA;pwd=;" 'this (servername) triggers the error
If Err.Number<>0 Then
MyErrorHandler
End If

Set CreateAllCommand = CreateObject("ADODB.Command")
Set CreateAllCommand.ActiveConnection = MyConnection

Set TruncateAllCommand = CreateObject("ADODB.Command")
Set TruncateAllCommand.ActiveConnection = MyConnection

'creation das SQL befehl
CreateAllCommand.CommandType = 1
CreateAllCommand.CommandText = "INSERT INTO [printerTemp] ([Printername], [servername], [description] VALUES(?,?,?)"

'empty table before refilling it
TruncateAllCommand.CommandType = 1
TruncateAllCommand.CommandText = "TRUNCATE TABLE [PrinterTemp]"
TruncateAllCommand.Execute


For intI = 0 to UBound(arrPrinter,2)
strServerName = Trim(arrPrinter(0, IntI))
strDrivername = Trim(arrPrinter(1, IntI))
strPrinterName = Trim(arrPrinter(2, IntI))
strSQLCreate = "INSERT INTO [printerTemp] ([Printername], [servername], [description]) "
strSQLCreate = strSQLCreate & "VALUES ('" & strPrinterName & "', '" & strServerName & "', '" & strDrivername & "')"
CreateAllCommand.CommandText = strSQLCreate
CreateAllCommand.Execute
If Err.Number<>0 Then
MyErrorHandler
End If
iprinterCount = iprinterCount + 1

Next
MyConnection.Close()

'MsgBox Begintime & vbcrlf & Endtime
'MsgBox iprinterCount


Sub MyErrorHandler()

emailserver = "bla"
meldung = "an error has occured in on the sql serversql script was not succesfull"
meldung = meldung & vbcrlf & "test"
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "some email"
objEmail.To = "some emil"

objEmail.Subject = "ES-APPL-LD: Synchronisation printers was not successfull"
objEmail.Textbody = meldung
objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2
objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= emailserver
objEmail.Configuration.Fields.Item _
("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25
objEmail.Configuration.Fields.Update
objEmail.Send
End Sub

 

[tsuji]

Jokada,

It is because it is in a loop. Put a err.clear there. The number of email would equal the number of troubled intI.

If Err.Number<>0 Then
MyErrorHandler
err.clear
End If

- tsuji

 

[Jokada]

Nope that doesn't work
I've even put an err.clear in the procedure
but now I've solved it with an if structure and a boolean so that the procedure is only executed once

 

[tsuji]

Jokada,

If you only need one email for multiple failure in the for loop, that's the way to go. But, I think what err.clear will result is what I said "the number of email would equal the number of troubled intI". Are you saying it is not correct? (not challenging, just want to know if my reasoning fails in this code.)

- tsuji

 

[Jokada]

well i let the program run for 5 minutes and i had 1300 emails. I just cant believe there were that much errors.
I think that the procedure is looping for some reason. but i dont understand why.
this code i used as you suggested
If Err.Number<>0 Then
MyErrorHandler
err.clear
End If

all that this code does (i think) is when an error is found at this point the error handles is called ONCE and the program should end. for some reason the sub procedure doesn't end but starts all over again.
So it never comes to err.clear

even when you change the code to
If Err.Number<>0 Then
err.clear
MyErrorHandler ' or call MyErrorHandles
End If
the procedure doest quit.

Greets

 

[tsuji]

Jokada,

Just for intellectual curiosity, I would echo out ubound(arrPrinter,2) see what is really set to after other possible cascaded error in connection, command objects etc.

- tsuji