Member_2_1767690
asked on
In classic ADO / SQLOLEDB provider, can a successful update return a non-zero error number?
When our legacy application executes an UPDATE statement, our code behaves as if an exception has been raised (and assumes the update has not worked), yet the effects of the update have been found later to be committed to the database.
Due to the infrequency of the issue, we are unable to obtain any exact error numbers or descriptions - we only know the issue is occurring due to the resultant effects on the database tables.
Is it even possible to get non-zero errors from ADO / SQL OLEDB provider when the UPDATE actually succeeds??
Please Note: This is a legacy VB6 app, Classic ADO with SQL OLEDB provider - nothing to do with dot NET.
Due to the infrequency of the issue, we are unable to obtain any exact error numbers or descriptions - we only know the issue is occurring due to the resultant effects on the database tables.
Is it even possible to get non-zero errors from ADO / SQL OLEDB provider when the UPDATE actually succeeds??
Please Note: This is a legacy VB6 app, Classic ADO with SQL OLEDB provider - nothing to do with dot NET.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If something went wrong on line 6, the compiler will automatically trigger line 14, which displays the error in a message box. But if line 6 succeeds, then any code following it, such as lines 8 - 12 will run.
ASKER
Thanks, jkaios.
Yes, the Records Affected parameter is what I meant - I was trying to split the terms I was using between classic VB and dot NET which James was using.
No matter - I will close the question - I'm not after how to capture the error - we already have error traps like the ones you describe, but are restricted in the tracing and messaging options available to us to read or log any error that is raised in these traps - its a production system (no message boxes allowed, no trace files allowed). My question does not require me to know what the error is - just whether an update statement can succeed IF ADO does raise an error number <> 0 (i.e. anything, it doesn't matter what number - any error at all).
In your example - let's say the error trap fires and the message box is raised. Under any circumstance then, is it possible for say, the UPDATE statement to actually write the value somevalue to the field somefield in the database - i.e. perhaps a non-critical error or warning raised as an error? Our code currently assumes if it enters the error trap, the statement has failed and no value has been written to the database - I want to know if that assumption can be broken.
Yes, the Records Affected parameter is what I meant - I was trying to split the terms I was using between classic VB and dot NET which James was using.
No matter - I will close the question - I'm not after how to capture the error - we already have error traps like the ones you describe, but are restricted in the tracing and messaging options available to us to read or log any error that is raised in these traps - its a production system (no message boxes allowed, no trace files allowed). My question does not require me to know what the error is - just whether an update statement can succeed IF ADO does raise an error number <> 0 (i.e. anything, it doesn't matter what number - any error at all).
In your example - let's say the error trap fires and the message box is raised. Under any circumstance then, is it possible for say, the UPDATE statement to actually write the value somevalue to the field somefield in the database - i.e. perhaps a non-critical error or warning raised as an error? Our code currently assumes if it enters the error trap, the statement has failed and no value has been written to the database - I want to know if that assumption can be broken.
Point taken, thanks for the clarification. BTW, that's a very good point!
One approach is to use "transaction processing", which ADO fully supports so that if ANYTHING (any kind of error) ever happens during an UPDATE or INSERT operation, simply "roll back" any changes that might have happened to your DB. And the proper place to roll back any changes is in your error trap routine.
A second approach is to query the specific record that was supposed to be updated and check if it has the new result or not.
Hope this helps?
Example on lines 7, 11, and 20:
One approach is to use "transaction processing", which ADO fully supports so that if ANYTHING (any kind of error) ever happens during an UPDATE or INSERT operation, simply "roll back" any changes that might have happened to your DB. And the proper place to roll back any changes is in your error trap routine.
A second approach is to query the specific record that was supposed to be updated and check if it has the new result or not.
Hope this helps?
Example on lines 7, 11, and 20:
Sub updateName()
On Error Goto Error_Trap:
Dim NumOfRecsAff as Long
ADO.BeginTrans 'intialize our Transaction Processing
ADO.Execute "UPDATE sometable set somefield = 'some value'", NumOfRecsAff
ADO.CommitTrans 'finalize any update, flush any pending writes to the DB
If (NumOfRecsAff <> 0) Then
msgbox "number of recs affected: " & NumOfRecsAff
End If
Exit Sub
Error_Trap:
MsgBox Err.Description, vbCritical, "ADO Error# " & Err.Number
ADO.RollbackTrans 'cancel anything that might have happened to our DB
End SubASKER
Thanks, jkaios.
Yes, thanks for the point on transactions - we have begin /commit /rollback at a higher level, so that we can atomically commit a number of records or not, rather than individual records at a time - the problem for us lies in what happens in between (there are intermediate repeat loops designed to get around transient lock/timeout problems).
I am contemplating forcing a transaction around each statement, but that gets into nested transactions, and this problem:
http://support2.microsoft.
Mostly, I believe we get the error message as a result of attempting nested transactions. I cannot recall exactly, but I believe that in some scenarios the SQL OLEDB provider "fakes" nested transactions, by creating another database connection behind the scenes, which causes further problems with connection timeout properties - I will have to dig back through my notes on that issue.
Anyway - thanks so much for your help - it's good to have someone take the time to respond to questions!
Cheers.
Ed.
Yes, thanks for the point on transactions - we have begin /commit /rollback at a higher level, so that we can atomically commit a number of records or not, rather than individual records at a time - the problem for us lies in what happens in between (there are intermediate repeat loops designed to get around transient lock/timeout problems).
I am contemplating forcing a transaction around each statement, but that gets into nested transactions, and this problem:
http://support2.microsoft.
Mostly, I believe we get the error message as a result of attempting nested transactions. I cannot recall exactly, but I believe that in some scenarios the SQL OLEDB provider "fakes" nested transactions, by creating another database connection behind the scenes, which causes further problems with connection timeout properties - I will have to dig back through my notes on that issue.
Anyway - thanks so much for your help - it's good to have someone take the time to respond to questions!
Cheers.
Ed.
Always a pleasure to help n have a great weekend.
ASKER
Thank you both for your input.
Yes, we are aware of the SET NOCOUNT ON issue - in this case, we believe the statement is failing with a non-zero error, before we are able to check the RecordsAffected property. The code assumes the statement has failed if a non-zero error is raised and therefore does not bother to check RecordsAffected - only if the statement succeeds with no error do we check RecordsAffected.
I guess my question could be re-worded as: "Would it be possible for RecordsAffected (once populated) to be > 0 if Err.Number was <> 0"?
Also, in VB6 we cannot use the (dot Net only) SQLDataReader or SQLDataAdapter - we're just using the ADO command object itself.
Also, yes, we already do use OnError - we use it to capture the error number, description and procedural context to a trace file, however due to client request, we cannot enable the tracing permanently to capture it and inspect it accordingly (as the trace files end up being huge). We only know the error trap is firing, because of what the caller method is doing next in the database.
Thanks anyway, guys - much appreciated ;-)