Solved

In classic ADO / SQLOLEDB provider, can a successful update return a non-zero error number?

Posted on 2014-11-04
9
149 Views
Last Modified: 2014-11-14
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.
0
Comment
Question by:edwardr
  • 5
  • 3
9 Comments
 
LVL 12

Assisted Solution

by:James Elliott
James Elliott earned 167 total points
ID: 40423908
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
 
LVL 12

Accepted Solution

by:
jkaios earned 333 total points
ID: 40425225
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
 

Author Comment

by:edwardr
ID: 40427715
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
 
LVL 12

Assisted Solution

by:jkaios
jkaios earned 333 total points
ID: 40427830
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 12

Expert Comment

by:jkaios
ID: 40427835
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
 

Author Comment

by:edwardr
ID: 40439725
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
 
LVL 12

Expert Comment

by:jkaios
ID: 40441726
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
 

Author Comment

by:edwardr
ID: 40442111
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
 
LVL 12

Expert Comment

by:jkaios
ID: 40442203
Always a pleasure to help n have a great weekend.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now