473,735 Members | 1,756 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Error code - advanced

157 New Member
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:
Expand|Select|Wrap|Line Numbers
  1. 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)
  2. On Error GoTo 0
  3.  
  4. Dim RstLoksum As New ADODB.Recordset
  5. Dim RstHistorikk As New ADODB.Recordset
  6. Dim Aktiv_bruker As String
  7. Dim Slett As String
  8. 'errorCount brukes for å følge med antall "prøv igjen" ved feil
  9. Dim errorCount As Integer
  10. Dim errHandl As New ADODB.Recordset
  11. 'errDel brukes for å vite hvilken del av koden som lagde feilen
  12. Dim errDel As Integer
  13. 'errEgen brukes for å holde en egendefinert feilbeskrivelse
  14. Dim errEgen As String
  15. 'errEgen=" " er muligens unødvendig, ble lagt til for å unngå en "invalid use of null" feil
  16. errEgen = " "
  17. ntlogin = Environ("Username")
  18. Aktiv_bruker = ntlogin
  19.  
  20. If Antall_tabletter < 0 Then
  21.     MsgBox "Antall tabletter må være en positiv verdi" & vbCrLf & vbCrLf & "Lokasjonsflyttingen avbrytes", vbOKOnly, "LogiDose"
  22.     Exit Function
  23. End If
  24.  
  25. With RstLoksum
  26.  
  27. 'Denne koden gjentas flere ganger
  28. 'Hvis det blir error er dette første forsøk i denne delen av koden
  29. errorCount = 0
  30. 'ved error vil koden prøve på nytt fra dette punktet
  31. LokasjonsflyttError1Fortsett:
  32. 'hvis tabellen er åpen..
  33. If .State = adStateOpen Then
  34.     'IKKE lagre endringer
  35.     .CancelUpdate
  36.     'lukk tabellen
  37.     .Close
  38. End If
  39. On Error GoTo LokasjonsflyttError1
  40.  
  41. 'Her starter den vanlige koden
  42. If Not Fra = "Intet" And Not Fra = "Grossist" Then
  43.     .Open "SELECT * from [Lok Loksummer] where varenr=" & Varenr & " AND Lokasjon ='" & Fra & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  44.  
  45.         If .EOF And .BOF Then
  46.             .AddNew
  47.             !Varenr = Varenr
  48.             !Lokasjon = Fra
  49.         End If
  50.  
  51.         If IsNull(![Antall tabletter]) Then
  52.             ![Antall tabletter] = 0
  53.         End If
  54.  
  55.         ![Antall tabletter] = ![Antall tabletter] - Antall_tabletter
  56.  
  57.         If ![Antall tabletter] = 0 Then
  58.             .Delete
  59.             Slett = "ja"
  60.         End If
  61.  
  62.         If Not Slett = "ja" Then
  63.     .Update
  64. On Error GoTo 0
  65.         End If
  66.  
  67.     .Close
  68. End If
  69. Slett = ""
  70.  
  71. 'Hører til error handling, beskrevet tidligere i koden
  72. errorCount = 0
  73. LokasjonsflyttError2Fortsett:
  74. If .State = adStateOpen Then
  75.     .CancelUpdate
  76.     .Close
  77. End If
  78. On Error GoTo LokasjonsflyttError2
  79. 'slutt på det som hører til error handling
  80.  
  81. If Not Til = "Intet" Then
  82.     .Open "SELECT * from [Lok loksummer] where varenr=" & Varenr & " AND lokasjon='" & Til & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  83.         If .EOF And .BOF Then
  84.             .AddNew
  85.             !Varenr = Varenr
  86.             !Lokasjon = Til
  87.         End If
  88.  
  89.         If IsNull(![Antall tabletter]) Then
  90.             ![Antall tabletter] = 0
  91.         End If
  92.  
  93.         ![Antall tabletter] = ![Antall tabletter] + Antall_tabletter
  94.  
  95.         If ![Antall tabletter] = 0 Then
  96.             .Delete
  97.             Slett = "ja"
  98.         End If
  99.  
  100.         If Not Slett = "ja" Then
  101.  
  102.  
  103.         .Update
  104. On Error GoTo 0
  105.         End If
  106.  
  107.     .Close
  108. End If
  109. End With
  110.  
  111. With RstHistorikk
  112.  
  113. 'Hører til error handling, beskrevet tidligere i koden
  114. errorCount = 0
  115. LokasjonsflyttError3Fortsett:
  116. If .State = adStateOpen Then
  117.     .CancelUpdate
  118.     .Close
  119. End If
  120. On Error GoTo LokasjonsflyttError3
  121. 'slutt på det som hører til error handling
  122.  
  123. .Open "SELECT * from [LOK Loksummer - flytthistorikk]", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  124.     .AddNew
  125.     ![Varenr] = Varenr
  126.     !Conveyor = Conveyor
  127.     !Pasient = Pasient
  128.     !Kundegruppe = Kundegruppe
  129.     ![Antall tabletter] = Antall_tabletter
  130.     !Hendelse = Hendelse
  131.     !BoksID = BoksID
  132.     !Batch = Batch
  133.     ![Fra lokasjon] = Fra
  134.     ![Til lokasjon] = Til
  135.     ![flyttet av] = Aktiv_bruker
  136.     ![Flyttet tid] = Now
  137.     ![registrert tid] = Now
  138.     ![Akt] = -1
  139.  
  140.     .Update
  141. On Error GoTo 0
  142. .Close
  143.  
  144. End With
  145. Exit Function
  146.  
  147. 'HER STARTER ERROR HANDLING
  148.  
  149. 'Ved feil i error handling gå direkte til "call feillogg()"
  150. On Error GoTo FeilIErrorHandl
  151.  
  152. 'Lagre hvilken del av koden som genererte feil og gå vidre til LokasjonsflyttErrorLagre
  153. LokasjonsflyttError1:
  154. errDel = 1
  155. MsgBox "Error1"
  156. GoTo LokasjonsflyttErrorLagre
  157. MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError1", vbOKOnly, "LogiDose"
  158. Exit Function
  159.  
  160. LokasjonsflyttError2:
  161. errDel = 2
  162. MsgBox "Error2"
  163. GoTo LokasjonsflyttErrorLagre
  164. MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError2", vbOKOnly, "LogiDose"
  165. Exit Function
  166.  
  167. LokasjonsflyttError3:
  168. errDel = 3
  169. MsgBox "Error3"
  170. GoTo LokasjonsflyttErrorLagre
  171. MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError3", vbOKOnly, "LogiDose"
  172. Exit Function
  173.  
  174. 'Her begyner "Hoved delen" Lagre hva som ble forsøkt gjort og hva som gikk galt
  175. LokasjonsflyttErrorLagre:
  176. 'dette holder oversikt over hvor mange forsøk som er brukt
  177. errorCount = errorCount + 1
  178.  
  179. 'åpne tabellen [FEIL Loksumflytt]
  180. With errHandl
  181.     .Open "SELECT * FROM [FEIL Loksumflytt] ORDER BY Feil_ID DESC", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  182.         'Hvis dette er første forsøk: finn en feil id
  183.         If errorCount = 1 Then
  184.             'Hvis det ikke finnes noen oppføringer i [FEIL Loksumflytt] er feil_id=1
  185.             If .BOF And .EOF Then
  186.                 feil_id = 1
  187.             Else
  188.             'tabellen er sortert etter feil_id ny feil_id=gammel feil_id + 1
  189.                 feil_id = !feil_id + 1
  190.             End If
  191.         End If
  192.  
  193.         'Hvis det IKKE er første forsøk starter koden her etter ".open"
  194.         .AddNew
  195.         !feil_id = feil_id
  196.         !forsøk_nummer = errorCount
  197.         !Fra_funksjon = "Loksumflytt"
  198.         !tid = Now
  199.         !bruker = Brukernavn
  200.         !errNum = Err.Number
  201.         !errDes = Err.Description
  202.         !errDel = errDel
  203.         !Varenr = Varenr
  204.         !Antall_tabletter = Antall_tabletter
  205.         !Fra = Fra
  206.         !Til = Til
  207.         'Det under dette punktet er "optional" i funksjonen
  208.         If Not IsNull(Hendelse) Then
  209.             !Hendelse = Hendelse
  210.         End If
  211.         If Not IsNull(Behandlingstype) Then
  212.             !Behandlingstype = Behandlingstype
  213.         End If
  214.         If Not IsNull(BoksID) Then
  215.             !BoksID = BoksID
  216.         End If
  217.         If Not IsNull(Batch) Then
  218.             !Batch = Batch
  219.         End If
  220.         If Not IsNull(Conveyor) Then
  221.             !Conveyor = Conveyor
  222.         End If
  223.         If Not IsNull(Pasient) Then
  224.             !Pasient = Pasient
  225.         End If
  226.         If Not IsNull(Kundegruppe) Then
  227.             !Kundegruppe = Kundegruppe
  228.         End If
  229.         If Not IsNull(errEgen) Then
  230.             !errEgen = errEgen
  231.         End If
  232.         .Update
  233.     .Close
  234. End With
  235.  
  236. 'Hvis det har gått mer enn 9 forsøk
  237. If errorCount > 9 Then
  238. FeilIErrorHandl:
  239.     'FeilLogg lagrer i egen tabell(feillogg) og skriver ut automatisk (til MD_4 per 9/3-2009)
  240.     Call FeilLogg(Varenr, "Lokasjonsflytt", "Antall_tabletter:" & Antall_tabletter & " Fra:" & Fra & _
  241.     " Til:" & Til & " err.number:" & Err.Number & " err.description:" & Err.Description & " tid:" & _
  242.     Now & " Bruker:" & Brukernavn & " ErrEgen:" & errEgen)
  243.  
  244.     'Går til slutten av error handling og fortsetter med neste vare
  245.     'Dette har ikke blitt testet pga problemer med å provosere fram feil
  246.     GoTo ErrorHandlSlutten
  247.     Exit Function
  248. End If
  249.  
  250. 'sSleep(100) = vent 1 sekund
  251. sSleep (100)
  252.  
  253. 'ser hvilken del av koden som feilet
  254. 'Err.clear = tar programmet ut av "feil modus"
  255. 'Prøv igjen (goto "begynelsen av den delen der feilen oppsto")
  256. If errDel = 1 Then
  257.     Err.Clear
  258.     GoTo LokasjonsflyttError1Fortsett
  259. ElseIf errDel = 2 Then
  260.     Err.Clear
  261.     GoTo LokasjonsflyttError2Fortsett
  262. ElseIf errDel = 3 Then
  263.     Err.Clear
  264.     GoTo LokasjonsflyttError3Fortsett
  265. Else
  266.     'Hvis errDel på noen rar måte har blitt noe annet enn det som står her
  267.     'Håndter på samme måte som hvis den bruker mer enn 9 forsøk
  268.     errEgen = "Ukjent errDel: " & errDel
  269.     GoTo FeilIErrorHandl
  270. End If
  271.  
  272. 'Gir opp og fortsetter
  273. 'Skal bare komme hit når FeilLogg har blitt skrevet ut
  274. ErrorHandlSlutten:
  275. Err.Clear
  276. End Function
May 6 '09 #1
9 3296
NeoPa
32,569 Recognized Expert Moderator MVP
It would be helpful if you would tell us :
  1. Where the code errors in the first place.
  2. Which error routine is being invoked.
May 6 '09 #2
ADezii
8,834 Recognized Expert Expert
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).
May 6 '09 #3
MrDeej
157 New Member
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 LokasjonsflyttE rror1 "
LokasjonsflyttE rror1 sets the "ErrDel = 1" and "Goto LokasjonsflyttE rrorLagre "

LokasjonsflyttE rrorLagre saves the error message and uses the "ErrDel" to go back to line 43 trough this expression:
Expand|Select|Wrap|Line Numbers
  1. If errDel = 1 Then 
  2.     Err.Clear 
  3.     GoTo LokasjonsflyttError1Fortsett
  4. 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.
May 7 '09 #4
FishVal
2,653 Recognized Expert Specialist
@MrDeej
Try to replace it with

Expand|Select|Wrap|Line Numbers
  1. If errDel=1 Then Resume LokasjonsflyttError1Fortsett
  2.  
May 7 '09 #5
MrDeej
157 New Member
Ok.. I will implement this and report back here when an error occour
May 7 '09 #6
ADezii
8,834 Recognized Expert Expert
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 LokasjonsflyttE rror1Fortsett:. Should another, or the same Error, occur after Line #78, you now branch to LokasjonsflyttE rror2: where errDel now equals 2 and you again return to LokasjonsflyttE rrorLagre but errDel now equals 2.
May 7 '09 #7
NeoPa
32,569 Recognized Expert Moderator MVP
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.
May 7 '09 #8
MrDeej
157 New Member
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?
May 8 '09 #9
NeoPa
32,569 Recognized Expert Moderator MVP
  1. From Access hit Alt-F11 to go to the IDE window.
  2. From there hit Ctrl-G to open the Debug Pane.
  3. From there type in Error and hit F1 (Context sensitive help).
  4. When prompted select On Error Statement.
  5. Read this all through. Some of the notes lower down are quite important for understanding.
  6. Click on See Also, and select Resume Statement.
  7. Read through this smaller section.
  8. 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 :
  1. Why your code didn't work.
  2. 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.
May 8 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
4502
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. www.woodsolutions.com/download/ErrorLoadComp.jpg
1
1488
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 Warning ------------------------------------------------------------------------------
4
5371
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 CLSID {3CFCB90E-B0B0-11D2-8D8B-00C04F79EE8F} is either not valid or not registered. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error...
1
7741
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 of resource: '', and resource name: ''. SQLSTATE=57011 . When looking in db2diag.log, we found the following that seemed to correspond to each exception: 2005-05-03-08.58.57.470000 Instance:DB2 Node:000
10
3358
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 anybody know how I could do this?
25
17212
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 being uploaded. I'm also using the HtmlInputFile control to do the uploading. My problem is that when the user's file size exceeds 512k, the page immediately redirects to the "The page cannot be displayed" error page which is very confusing. ...
0
1176
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 Error Reporting (at the bottom of the screen), which launches the Error Reporting screen. Another way to get to that screen: Open My Computer, right-click on Properties, click on Advanced tab and Error Reporting. Any assistance would be welcome.
5
2004
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 some pages I have applied a small client-script to trap the enter-key beeing pressed and re-route this action. With the new version this little script still works, it traps the enter-key. BUT it causes postback-errors on many other .NET control,...
4
1402
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 required parameters. What i want to do Now iam gonna try to explain how i want this to work. I want this code to select the projekt from the table projekt if all the posts in the table kostnader that have the same ID as the projekt and status = 1.
0
8965
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9466
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9330
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8202
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6748
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6050
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3278
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2741
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.