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.
edwardrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
May or may not be relevant:

Previously, in Classic ASP and ADO, NOCOUNT was most commonly a factor if you were checking RecordsAffected on Command.Execute(). SqlDataReader does have a RecordsAffected property, but it's rarely used since it's not populated until the datareader has iterated all the rows and the datareader is closed. There are some possible implications if you're using DataAdapters to submit changes to the database, since it uses the rows affected result to determine if the update succeeded. Probably the easiest way to check for that case is to search the codebase for SqlDataAdapter and see if the Update() method is being called.

http://weblogs.asp.net/jongalloway/How-NOCOUNT-affects-ADO.NET
0
jkaiosIT DirectorCommented:
You can always use the On Error statement to check for subsequent errors that might occur from any ADO commands.  Please note that it's also a good practice to use the adExecuteNoRecords option for any SQL query that does NOT return any result to improves performance by minimizing internal processing of ADO.
Sub updateName()

   On Error Resume Next

   adoCN.Execute "UPDATE myTable SET FirstName = 'Jon Doe'", , adExecuteNoRecords

   If Err Then
      MsgBox Err.Description, vbCritical, "Error# " & Err.Number
   End If

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
edwardrAuthor Commented:
Hi, James and jkaios.

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 ;-)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jkaiosIT DirectorCommented:
we believe the statement is failing with a non-zero error, before we are able to check the RecordsAffected property

There is NO RecordsAffected property in both the Connection object and the Recordset object of ADO.  This is a "parameter" of the Execute method of the Connection object.  What you need to do a create varaible of Long data type and then pass this variable to second parameter of the Execute method.

If you're using the On Error statement and there is an error occurs from the Execute method of ADO, then you should be able to capture the error as in the following example:

Sub updateName()

   On Error Goto Error_Trap:

   Dim NumOfRecsAff as Long
   ADO.Execute "UPDATE sometable set somefield = 'some value'", NumOfRecsAff

   If (NumOfRecsAff <> 0) Then
      msgbox "number of recs affected: " & NumOfRecsAff
   End If

   Exit Sub
Error_Trap:
   MsgBox Err.Description, vbCritical, "ADO Error# " & Err.Number

End Sub

Open in new window

0
jkaiosIT DirectorCommented:
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.
0
edwardrAuthor Commented:
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.
0
jkaiosIT DirectorCommented:
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:

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 Sub

Open in new window

0
edwardrAuthor Commented:
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.com/kb/177138/en-us

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.
0
jkaiosIT DirectorCommented:
Always a pleasure to help n have a great weekend.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.