[RESOLVED] Handling ADO errors in VB6

[jeffdc]

I have error handling in my routine where it displays the error in a listview. This works fine with standard VB errors. However, I ran into a situation where I was doing an update to a sql table and the one field was a datetime but the value it was trying to write was not a valid datetime. Even though I have error handling, VB still pops up an error message that says "Error 6 - Overflow". How can I stop VB from popping up this message? Is there something I need to add to my error handling that can prevent this?

If I step through the problem, the execution jumps to my error handling routine, pops up this overflow message, then after you close it, continues to execute the code in my error handler.

[dilettante]

You may need to isolate the line which throws this exception.

Perhaps test the program in the IDE with your error handling disabled?

[jeffdc]

I know what line is causing the error, its my recordset.open line.
The reason I get an overflow is it is trying to update a record with an invalid datetime value. I realize I can put code in to analyze all of the data that is being used by the update but that would be a very time consuming thing to do. I would rather just be able to trap this error somehow.

[si_the_geek]

Welcome to VBForums

Can you show us the code?

[jeffdc]

This routine is huge so I am only going to paste parts of it:

Dim dc As New ADODB.Connection
Dim dr As New ADODB.Recordset

On Error GoTo tmrUpdateSQL_Err

dc.ConnectionString = sConnectionString
dc.CursorLocation = adUseClient
dc.Open


dr.Open sQry, dc, adOpenDynamic, adLockPessimistic

tmrUpdateSQL_Err:
AddAlert "tmrUpdateSQL", "Error", Err.Number, Err.Description
If dr.State = adStateOpen Then dr.Close
Set dr = Nothing
If dc.State = adStateOpen Then dc.Close
Set dc = Nothing

sQry is a string that gets built throughout the routine. It is a sql update. I know the syntax is correct because this whole routine and the update work fine as long as I dont have any invalid values that I am updating with.

When I step through it during the time I have an invalid value, when it tries to execute the "dr.open...", it jumps down to the first line in my error handing "AddAlert...", then pops up a message box saying "Error 6 Overflow" or something like that, then after you close that, it continues execution and calls my routine called AddAlert.

[jeffdc]

If it helps, I am using SQLExpress 2005, and in my connection string I am using "Provider=SQLOLEDB".

[si_the_geek]

I suspect the issue is with the declaration of the AddAlert routine - specifically the parameter that receives the value of Err.Number . I have a feeling that it should be a Long, but check the help for Err.Number to check what data type it is.


In addition to that, there are several things about your code that are less than ideal.

First of all, you should not be using a recordset to execute an SQL statement that does not return records, because it uses extra resources and wastes time. Instead you should use the .Execute method of the Connection or a Command object (preferably with parameters), eg:

dc.Execute sQry, , adCmdText + adExecuteNoRecords

Next, setting .CursorLocation to adUseClient tends to be a bad idea (it uses extra resources, slows things down, and increases chances of errors if multiple people/programs work with the database at the same time), so it should generally be avoided if possible.

The last item I'll highlight is covered by the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum)

[jeffdc]

That was it, thanks! The parameter where Err.number was getting passed was setup as an integer so I changed it to a long and everything worked fine. Thanks for the other suggestions too (I am only a part-time, untrained programmer so I am sure I make a ton of no-no's in my code)

Not sure how to say this was resolved.

[si_the_geek]

No problem.

You can mark it as resolved by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).