Error handling 2

[jopaumier]

My bane (among other VB things) - error handling.

I have code that parses lines of text, one line at a time, and inserts the results into a database. I don't know if I am doiung the best and most efficient way, but it works. Generally,

Dim objLine As TextStream
Dim strNewLine As String
Set objLine = fspenTextFile(textfile, ForReading, , TristateFalse)

Do While Not objLine.AtEndOfStream
process text fields
Loop

In the 'process text fields', I process each field individually. There are 15-25 fields, depending on the text file. Should I encounter an error anywhere during the processing of the line, I want inform the user (I plan to add it to a log file), stop processing the current record and go to the next record.

I have On Error Goto ErrHndl at the beginning of the sub. The handler is outside the loop, and it lets the user know an error was encountered and returns to inside the loop near the "Loop" statement to do some cleanup before processing the next record.

I think this is bad, bad programming on my part, since it seems like I am jumping out of a loop then back in (and I have yet to test the consequences). On Error Resume Next does not seem feasible without adding code after processing each field. How does one handle an error inside a loop, yet continue processing after handling the error?

Jim

 

[CassandraR]

Hi Jim:

If you want to just abandon the current loop, not the entire loop, then I would suggest:

    Dim objLine As TextStream
    Dim strNewLine As String
    Set objLine = fso.OpenTextFile(textfile, ForReading, , TristateFalse)

    On Error Goto MyErrorHandler

    Do While Not objLine.AtEndOfStream
      process text fields
DoNextLoop:                     ' Destination for abandoning the current loop.
    Loop

. . .
    Exit Sub

MyErrorHandler:
    If Err.Number = "recoverable error" Then
        ' Correct the error condition and return
        ' Add error correction here.
        Resume Next
    ElseIf Err.Number = "non-recoverable_error" Then
        ' Abandon the current loop, but continue
        Resume DoNextLoop
    Else
        ' We can't handle the current error and must exit.
    EndIf
End Sub

HTH,
Cassie

 

[nicsin]

You could use on error resume next and branch using "if err". err will only be false if no erro has occured.

ex.

on error resume next
Do While Not objLine.AtEndOfStream
err.clear 'clear the errors collection
process text fields
if err then
'error handling
else
'proceed normally. may be ommitted
end if
Loop

hope it helps
nicsin

 

[CassandraR]

Hi Jim:

Concerning the question of jumping out of the loop and then back in, don't view it as being "bad, bad programming". After all, do you totally avoid calling Subs and Functions from within all loops? Calling your error handler, which is outside of the loop, is a way to tidy up the code by keeping the error handling code out of the way of the main coding.

Another way to add the error handler into the loop is as follows:

    On Error Goto MyErrorHandler

    Do While <condition>
        ' Process text fields.  May be multiple statements.
MyErrorHandler:
        If Err.Number <> 0 Then
            ' Process the error.  Remember to do Err.Clear
        End If
    Loop

    On Error Goto 0

HTH,
Cassie
;-)

 

[jopaumier]

Cassie and nicsin,

Thank you for the replies.

Cassie, I have implemented exactly what you have suggested (but in only one sub at the moment). Again, it just seems like I am jumping out of and into loops. It may be ok to do it this way, but is sure does give me the creeps!

nicsin, your suggestion is what I'd like to avoid, if possible. One thing I did not say is that I have 8 tables to process, each having 15-25 fields. A lot of extra code.

Jim

 

[jopaumier]

Cassie,

Your second message came in as I was finishing a response to the first. Maybe I just need to think of the error handler in different terms - it really is in the flow, just parked out of the way for emergencies.

As I pondered my first response I thought about what you've suggested in your second response - my only question is how do I avoid processing the code in MyErrorHandler if all goes well in processing the text? I need to look into it. In all kinds of testing without an error handler (please don't tell anyone I said that!), I've never encountered a problem processing the text files. Think I just jinxed myself?

Thanks,
Jim

 

[nicsin]

Excuse me but I thought you didn't like to exit loops while error handling occurs. My solution proposed the error handling within the loop... Anyway, good luck!

nicsin

 

[CCLINT]

nicsin, the error handling proposed by Cassie2002 also catches and handles the error inside the loop.
Yours does also, but, you need to do:

if err then
'error handling
End If
after each field which gets processed

 

[jopaumier]

nicsin,

You are right. Sorry about being abrupt earlier. Although I have asked a lot of questions here, I have yet to figure out what happens when I am composing my response to one or two, and more responses come in (who if anyone gets notified of my response?). I kinda panic and just 'submit' without rereading, rethinking, and recomposing. No excuse on my part, though. I appreciate your efforts. The responses tell me I have not overlooked something obvious and that is just as important, and reassuring, as finding something completely new.

And I just got notified of another response.

Jim

 

[nicsin]

cassie, cclint,

jim wrote:

I have On Error Goto ErrHndl at the beginning of the sub. The handler is outside the loop, and it lets the user know an error was encountered and returns to inside the loop near the "Loop" statement to do some cleanup before processing the next record.

I think this is bad, bad programming on my part, since it seems like I am jumping out of a loop then back in (and I have yet to test the consequences).

I may be mistaken but isn't this cassie's first solution? His/her second solution does seem better!


jim,

text file handling could be trivial but you are hiting a database so you definately need an error handler. I have written a similar program not so long ago and used the solution I provided. I checked for error for every record (not field) which seemed more reasonable. I also did write the problems in a log file.

All the best!

nicsin

 

[jopaumier]

nicsin,

I think I've been quite lucky when it comes to this part of the code. Part of the motivation for the question is bad thinking on my part - I'll go back and add the error handlers later. By the time I got to doing this, which was a small part my first VB project, I had enough experience to know better and know how to do it. Now there is no time and no money. Meaning I do it on my own time.

I just need to think it through as to the best way to manage this. And I think I misled you. If an error occurs anywhere during the processing of a record, I want to let the user know which record, but not process any more data from that record and continue on to the next record.

Thanks again,
Jim

 

[nicsin]

Jim,

may be I wasn't clear as well. just to be sure here is the code I have written for my app.



Do While Not fsoStream.AtEndOfStream
If GetAsyncKeyState(vbKeyEscape) <> 0 Then
userCancelled = True
Exit Do
Else
cntr = cntr + 1
'build the insert query
strSql = &quot;insert into tblLogData values(&quot;

record = fsoStream.ReadLine

'I do some processing here concerning the fields and

strSql = strSql & record & &quot&quot;

err.Clear

On Error Resume Next
con.Execute strSql
If err Then
errorLog = True
con.RollbackTrans
rolledBack = True
'if an error occurs, write the date to the errorLog file
write2file CStr(theDate) & vbTab & vbTab & s & &quot;.csv&quot; & vbTab & CStr(cntr), _
Path & &quot;ErrorLog.txt&quot;
Exit Do
Else
tempRecsImported = tempRecsImported + 1
End If
End If
Loop



I hope you will find the solution to your problem as well!

regards,
nicsin

 

[CassandraR]

Hi Jim:

Quote: my only question is how do I avoid processing the code in MyErrorHandler if all goes well in processing the text

The answer is maybe too obvious to be seen. If no error occurs on a particular pass through the loop, then Err.Number will be zero. Thus, the

If Err.Number <> 0 Then

condition is not met and the error handling code is skipped.

Nicsin:

I had misread Jim's question the first time through. Upon re-reading Jim's question, I realized that my first posting was not the answer. I was in error. Then I corrected my response and re-posted, this time addressing the issue of the error handler being within the loop.

Miss Cassandra
(&quot;Cassie&quot;, &quot;Cassie2002&quot

 

[nicsin]

cassie,

so we aggree.

all the best,
nicsin

 

[CassandraR]

Nicsin:

We do indeed agree.

Best wishes to you,
Cassie

 

[jopaumier]

Cassie & nicsin,

Thanks to you both and here's stars for both.

nicsin, thank you for the code snippet.

Cassie, you are right - it was way too obvious!

Jim

 

[CassandraR]

Thanks for the star, Jim!

Cassie

 

[nicsin]

thnx jim!

nicsin