Hello guys!
We have an SQL server which sometimes makes timeouts and connection errors. And we have an function witch writes and updates data in 2 tables on this server. When the SQL server error appears it, in 99%, of the cases, works if we just press the play button in VBA debug. Therefor we have maked an error handling which just tryes again.
However, as this error handling is difficult to test because of maybe 1 or 2 errors a day, we have tested it to not work as we would like it.
Sometimes it dosent try again, sometimes it only try 1 time and then stop code.
Can you see any obious errors in our error handling?
It's a rather long code: - Function Loksumflytt(Varenr As Long, Antall_tabletter As Long, Fra As String, Til As String, Optional Hendelse As String, Optional Behandlingstype As String, Optional BoksID As String, Optional Batch As String, Optional Conveyor As String, Optional Pasient As String, Optional Kundegruppe As String)
-
On Error GoTo 0
-
-
Dim RstLoksum As New ADODB.Recordset
-
Dim RstHistorikk As New ADODB.Recordset
-
Dim Aktiv_bruker As String
-
Dim Slett As String
-
'errorCount brukes for å følge med antall "prøv igjen" ved feil
-
Dim errorCount As Integer
-
Dim errHandl As New ADODB.Recordset
-
'errDel brukes for å vite hvilken del av koden som lagde feilen
-
Dim errDel As Integer
-
'errEgen brukes for å holde en egendefinert feilbeskrivelse
-
Dim errEgen As String
-
'errEgen=" " er muligens unødvendig, ble lagt til for å unngå en "invalid use of null" feil
-
errEgen = " "
-
ntlogin = Environ("Username")
-
Aktiv_bruker = ntlogin
-
-
If Antall_tabletter < 0 Then
-
MsgBox "Antall tabletter må være en positiv verdi" & vbCrLf & vbCrLf & "Lokasjonsflyttingen avbrytes", vbOKOnly, "LogiDose"
-
Exit Function
-
End If
-
-
With RstLoksum
-
-
'Denne koden gjentas flere ganger
-
'Hvis det blir error er dette første forsøk i denne delen av koden
-
errorCount = 0
-
'ved error vil koden prøve på nytt fra dette punktet
-
LokasjonsflyttError1Fortsett:
-
'hvis tabellen er åpen..
-
If .State = adStateOpen Then
-
'IKKE lagre endringer
-
.CancelUpdate
-
'lukk tabellen
-
.Close
-
End If
-
On Error GoTo LokasjonsflyttError1
-
-
'Her starter den vanlige koden
-
If Not Fra = "Intet" And Not Fra = "Grossist" Then
-
.Open "SELECT * from [Lok Loksummer] where varenr=" & Varenr & " AND Lokasjon ='" & Fra & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
-
-
If .EOF And .BOF Then
-
.AddNew
-
!Varenr = Varenr
-
!Lokasjon = Fra
-
End If
-
-
If IsNull(![Antall tabletter]) Then
-
![Antall tabletter] = 0
-
End If
-
-
![Antall tabletter] = ![Antall tabletter] - Antall_tabletter
-
-
If ![Antall tabletter] = 0 Then
-
.Delete
-
Slett = "ja"
-
End If
-
-
If Not Slett = "ja" Then
-
.Update
-
On Error GoTo 0
-
End If
-
-
.Close
-
End If
-
Slett = ""
-
-
'Hører til error handling, beskrevet tidligere i koden
-
errorCount = 0
-
LokasjonsflyttError2Fortsett:
-
If .State = adStateOpen Then
-
.CancelUpdate
-
.Close
-
End If
-
On Error GoTo LokasjonsflyttError2
-
'slutt på det som hører til error handling
-
-
If Not Til = "Intet" Then
-
.Open "SELECT * from [Lok loksummer] where varenr=" & Varenr & " AND lokasjon='" & Til & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
-
If .EOF And .BOF Then
-
.AddNew
-
!Varenr = Varenr
-
!Lokasjon = Til
-
End If
-
-
If IsNull(![Antall tabletter]) Then
-
![Antall tabletter] = 0
-
End If
-
-
![Antall tabletter] = ![Antall tabletter] + Antall_tabletter
-
-
If ![Antall tabletter] = 0 Then
-
.Delete
-
Slett = "ja"
-
End If
-
-
If Not Slett = "ja" Then
-
-
-
.Update
-
On Error GoTo 0
-
End If
-
-
.Close
-
End If
-
End With
-
-
With RstHistorikk
-
-
'Hører til error handling, beskrevet tidligere i koden
-
errorCount = 0
-
LokasjonsflyttError3Fortsett:
-
If .State = adStateOpen Then
-
.CancelUpdate
-
.Close
-
End If
-
On Error GoTo LokasjonsflyttError3
-
'slutt på det som hører til error handling
-
-
.Open "SELECT * from [LOK Loksummer - flytthistorikk]", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
-
.AddNew
-
![Varenr] = Varenr
-
!Conveyor = Conveyor
-
!Pasient = Pasient
-
!Kundegruppe = Kundegruppe
-
![Antall tabletter] = Antall_tabletter
-
!Hendelse = Hendelse
-
!BoksID = BoksID
-
!Batch = Batch
-
![Fra lokasjon] = Fra
-
![Til lokasjon] = Til
-
![flyttet av] = Aktiv_bruker
-
![Flyttet tid] = Now
-
![registrert tid] = Now
-
![Akt] = -1
-
-
.Update
-
On Error GoTo 0
-
.Close
-
-
End With
-
Exit Function
-
-
'HER STARTER ERROR HANDLING
-
-
'Ved feil i error handling gå direkte til "call feillogg()"
-
On Error GoTo FeilIErrorHandl
-
-
'Lagre hvilken del av koden som genererte feil og gå vidre til LokasjonsflyttErrorLagre
-
LokasjonsflyttError1:
-
errDel = 1
-
MsgBox "Error1"
-
GoTo LokasjonsflyttErrorLagre
-
MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError1", vbOKOnly, "LogiDose"
-
Exit Function
-
-
LokasjonsflyttError2:
-
errDel = 2
-
MsgBox "Error2"
-
GoTo LokasjonsflyttErrorLagre
-
MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError2", vbOKOnly, "LogiDose"
-
Exit Function
-
-
LokasjonsflyttError3:
-
errDel = 3
-
MsgBox "Error3"
-
GoTo LokasjonsflyttErrorLagre
-
MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError3", vbOKOnly, "LogiDose"
-
Exit Function
-
-
'Her begyner "Hoved delen" Lagre hva som ble forsøkt gjort og hva som gikk galt
-
LokasjonsflyttErrorLagre:
-
'dette holder oversikt over hvor mange forsøk som er brukt
-
errorCount = errorCount + 1
-
-
'åpne tabellen [FEIL Loksumflytt]
-
With errHandl
-
.Open "SELECT * FROM [FEIL Loksumflytt] ORDER BY Feil_ID DESC", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
-
'Hvis dette er første forsøk: finn en feil id
-
If errorCount = 1 Then
-
'Hvis det ikke finnes noen oppføringer i [FEIL Loksumflytt] er feil_id=1
-
If .BOF And .EOF Then
-
feil_id = 1
-
Else
-
'tabellen er sortert etter feil_id ny feil_id=gammel feil_id + 1
-
feil_id = !feil_id + 1
-
End If
-
End If
-
-
'Hvis det IKKE er første forsøk starter koden her etter ".open"
-
.AddNew
-
!feil_id = feil_id
-
!forsøk_nummer = errorCount
-
!Fra_funksjon = "Loksumflytt"
-
!tid = Now
-
!bruker = Brukernavn
-
!errNum = Err.Number
-
!errDes = Err.Description
-
!errDel = errDel
-
!Varenr = Varenr
-
!Antall_tabletter = Antall_tabletter
-
!Fra = Fra
-
!Til = Til
-
'Det under dette punktet er "optional" i funksjonen
-
If Not IsNull(Hendelse) Then
-
!Hendelse = Hendelse
-
End If
-
If Not IsNull(Behandlingstype) Then
-
!Behandlingstype = Behandlingstype
-
End If
-
If Not IsNull(BoksID) Then
-
!BoksID = BoksID
-
End If
-
If Not IsNull(Batch) Then
-
!Batch = Batch
-
End If
-
If Not IsNull(Conveyor) Then
-
!Conveyor = Conveyor
-
End If
-
If Not IsNull(Pasient) Then
-
!Pasient = Pasient
-
End If
-
If Not IsNull(Kundegruppe) Then
-
!Kundegruppe = Kundegruppe
-
End If
-
If Not IsNull(errEgen) Then
-
!errEgen = errEgen
-
End If
-
.Update
-
.Close
-
End With
-
-
'Hvis det har gått mer enn 9 forsøk
-
If errorCount > 9 Then
-
FeilIErrorHandl:
-
'FeilLogg lagrer i egen tabell(feillogg) og skriver ut automatisk (til MD_4 per 9/3-2009)
-
Call FeilLogg(Varenr, "Lokasjonsflytt", "Antall_tabletter:" & Antall_tabletter & " Fra:" & Fra & _
-
" Til:" & Til & " err.number:" & Err.Number & " err.description:" & Err.Description & " tid:" & _
-
Now & " Bruker:" & Brukernavn & " ErrEgen:" & errEgen)
-
-
'Går til slutten av error handling og fortsetter med neste vare
-
'Dette har ikke blitt testet pga problemer med å provosere fram feil
-
GoTo ErrorHandlSlutten
-
Exit Function
-
End If
-
-
'sSleep(100) = vent 1 sekund
-
sSleep (100)
-
-
'ser hvilken del av koden som feilet
-
'Err.clear = tar programmet ut av "feil modus"
-
'Prøv igjen (goto "begynelsen av den delen der feilen oppsto")
-
If errDel = 1 Then
-
Err.Clear
-
GoTo LokasjonsflyttError1Fortsett
-
ElseIf errDel = 2 Then
-
Err.Clear
-
GoTo LokasjonsflyttError2Fortsett
-
ElseIf errDel = 3 Then
-
Err.Clear
-
GoTo LokasjonsflyttError3Fortsett
-
Else
-
'Hvis errDel på noen rar måte har blitt noe annet enn det som står her
-
'Håndter på samme måte som hvis den bruker mer enn 9 forsøk
-
errEgen = "Ukjent errDel: " & errDel
-
GoTo FeilIErrorHandl
-
End If
-
-
'Gir opp og fortsetter
-
'Skal bare komme hit når FeilLogg har blitt skrevet ut
-
ErrorHandlSlutten:
-
Err.Clear
-
End Function
9 3267 NeoPa 32,556
Expert Mod 16PB
It would be helpful if you would tell us : - Where the code errors in the first place.
- Which error routine is being invoked.
As indicated by NeoPa, you are going to have to pinpoint the context in which the Error is happening. The large number of Unconditional GoTo Statements (Line Numbers: 156, 163, 170, etc.) makes this type of coding very difficult to Debug. This is one reason why the use of Unconditional GoTos is frowned upon (outside of On Error GoTo).
Ok..
Errors occour, sometimes, on following lines
43.
63.
82.
103.
123.
140.
I really have very little experience in error coding cause i usually make the code avoid error instead of handling them, but this is the SQL server which makes my nightsleep hard :P
When an error happends, i want the code to basically try again. E.G. is when error occours in line 82. i want it to make a log entry and try again at line 82. As you can see we have different pointers to error handling and which uses the Goto command.
When error on 43 or 63 is uses the "On Error GoTo LokasjonsflyttError1 "
LokasjonsflyttError1 sets the "ErrDel = 1" and "Goto LokasjonsflyttErrorLagre "
LokasjonsflyttErrorLagre saves the error message and uses the "ErrDel" to go back to line 43 trough this expression: - If errDel = 1 Then
-
Err.Clear
-
GoTo LokasjonsflyttError1Fortsett
-
end if
Here is one problem, when it makes one of this loops it only makes 1 and if error again the code stops. And i dont know why. I would like it to try up to 9 times before it quits the code.
@MrDeej
Try to replace it with -
If errDel=1 Then Resume LokasjonsflyttError1Fortsett
-
Ok.. I will implement this and report back here when an error occour
MrDeej, I do not mean to sound harsh, but as long as you employ this current logic as far as Unconditional Branching to handle Errors, this will just be the beginning of the end as far as problems and tracing them go. That being said, you clear the Error and branch to LokasjonsflyttError1Fortsett:. Should another, or the same Error, occur after Line #78, you now branch to LokasjonsflyttError2: where errDel now equals 2 and you again return to LokasjonsflyttErrorLagre but errDel now equals 2.
NeoPa 32,556
Expert Mod 16PB
If, in an error routine, you use Go To to branch back to the code you want to retry (as opposed to using Resume ...), then the compiler will never know that you're trying again, and can only assume you are still handling the first error.
When your code errors within an error handler it will not try to re-enter the error handler (It already knows that code fails), but it will go to the next level up and allow that code to handle the new error. Essentially unwrapping itself like an onion.
I strongly recommend you read up on this subject in the help system. It's a good idea generally to check out a subject in help or the manual before trying to use it. Things work so much more easily that way.
I have looked in some help files for Error handling. But i cant find an error handling which just tryes again.
Basic to this subject.
If i have no error code, the debug dialog pops up and let me debug the code on error. Here i just have to press play and it now works. This is because the errors are not in the code, but in the connection to the SQL server.
Therefore we have made this code, which i could not find any info on, to make the code just try again.
Because that is what i basically want: "On Error Try Again" 9 times before stopping
ADezii: I didnt understand your post fully, what do you mean by branching?
NeoPa 32,556
Expert Mod 16PB - From Access hit Alt-F11 to go to the IDE window.
- From there hit Ctrl-G to open the Debug Pane.
- From there type in Error and hit F1 (Context sensitive help).
- When prompted select On Error Statement.
- Read this all through. Some of the notes lower down are quite important for understanding.
- Click on See Also, and select Resume Statement.
- Read through this smaller section.
- Select any other items you see that you want to understand in more detail.
You should now have a thorough understanding of the basic issues and will grasp : - Why your code didn't work.
- What you need to do to ensure it always does in future.
This is well worth putting in the small amout of effort to understand.
Good luck with your project.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: B Golash |
last post by:
Hi
I`m trying to install a third-party control. During
the install I receive this error...see the url below.
I have no idea as to the meaning of the error.
...
|
by: JoseLuis |
last post by:
I HAVE THIS Warning : What can I do to fix it ????
XML comment block must immediately precede the language element to witch its
applies
and next is the code qhen I doble click the...
|
by: Ahmed |
last post by:
i have tried running the csharpsite in my Microsoft Visual C#.net and after i
hit the run button i keep getting the same error which is :
Server Error in '/csharpsite' Application
COM object with...
|
by: timVerizon |
last post by:
Hoping someone can help here..
Our application (C#.Net) was receiving IBM.Data.DB2.DB2Exceptions
ERROR SQL0904N Unsuccessful execution
caused by an unavailable resource. Reason code: '', type...
|
by: jeff regoord |
last post by:
A user inputs a float value. The scanf() function gets the value.
However, I need to create an error handler with an if else statement
saying invalid input if the input is not a number. Does...
|
by: moondaddy |
last post by:
I have an application where users need to upload images and in my web.config
file I have a setting like this:
<httpRuntime maxRequestLength="512" />
Which restricts image larger than 500k from...
|
by: kt |
last post by:
We want to automatically turn off the error reporting options using Windows
API functions for the following functions area
Click on Start, Settings, Control Panel, System, the Advanced tab and...
|
by: Bjorn Sagbakken |
last post by:
Hello
I have just migrated from VS 2003 to VS 2005, and .NET framework 1.1 to 2.0
I am at the end of debugging and fixing stuff. Now there is one error I just
cannot find a solution to:
On...
|
by: alcstudio |
last post by:
Have this SQL-string:
SQL = "select id as pid from projekt union select count(*) as totalt from kostnader where id = pid and status = 1"
And i get this error:
* No value given for one or more...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |