Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-11-04
9
Medium Priority
?
166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 12

Assisted Solution

by:James Elliott
James Elliott earned 501 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 999 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 12

Assisted Solution

by:jkaios
jkaios earned 999 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
 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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