How to detect when an UPDATE fails when using ADODB.Command Execute

M

mdlueck

Sr. Application Developer

Local time

Today, 15:10

Joined

Jun 23, 2011

Messages

2,634

• Sep 1, 2011

• #1

I have come up against an unexpected sharp spot in testing my application.

I have simulated another user updating the same record that "user 1" has in edit mode.

The UPDATE transaction I issue updates upon the unique table key AND the last save timestamp columns.

Obviously if the timestamp does not match, the UPDATE can not find a matching row to update.

The Execute method of an ADODB.Command object seems to silently mask this condition.

What would be the solution for getting a reliable pass/fail return code from an UPDATE? Thanks!

 

M

mdlueck

Sr. Application Developer

Local time

Today, 15:10

Joined

Jun 23, 2011

Messages

2,634

• Sep 1, 2011

• #2

Aaahhh, I have something to try first thing tomorrow...

"How To Determine Number of Records Affected by an ADO UPDATE"
http://support.microsoft.com/kb/195048

Stay tuned...

 

jdraw

Super Moderator

Staff member

Local time

Today, 15:10

Joined

Jan 23, 2006

Messages

15,461

• Sep 1, 2011

• #3

see http://msdn.microsoft.com/en-us/library/ms681559(v=VS.85).aspx

 

M

mdlueck

Sr. Application Developer

Local time

Today, 15:10

Joined

Jun 23, 2011

Messages

2,634

• Sep 2, 2011

• #4

Resolved!!! 'twas as simple as:

Code:

  'Execute the SQL
  Set adoRS = adoCMD.Execute(lRecordsAffected)

  'Make sure 1 record was affected
  If lRecordsAffected <> 1 Then
    Call errorhandler_MsgBox("Class: clsObjProjectsTbl, Function: Update(), Failed: UPDATE of BE DB did not affect a total of 1 record, actually RecordsAffected=" & lRecordsAffected)
    Update = False
    GoTo Exit_Update
  End If

And attached is a sample of the initial error message. Perhaps I will add some suggestion text that "someone else might have changed the same record".

 

Attachments

• 
  ApplicationErrorDialog3_UPDATEofBEfailed.png

  10.8 KB · Views: 594

M

mdlueck

Sr. Application Developer

Local time

Today, 15:10

Joined

Jun 23, 2011

Messages

2,634

• Sep 2, 2011

• #5

PS... and it appears IMPARATIVE that you accept the adoRS object that Execute will return in order to obtain a valid RecordsAffected return code.

Code:

Set adoRS = .Execute(lRecordsAffected)

Otherwise it returns RecordsAffected=0 consistently.

I had no use for the adoRS object so decided to tidy up and remove that "extra" object. Suddenly my code could no longer UPDATE records. I backtracked to when it was working, and the only thing different was the unused adoRS object... sure enough, putting that back fixed UPDATE. (shrug)

 

jdraw

Super Moderator

Staff member

Local time

Today, 15:10

Joined

Jan 23, 2006

Messages

15,461

• Sep 2, 2011

• #6

It seems to me that

If lRecordsAffected <> 1

Click to expand...

you could get a 0 returned, and it would <> 1????

If lRecordsAffected =0 would be the choice to invoke the error handler

 

M

mdlueck

Sr. Application Developer

Local time

Today, 15:10

Joined

Jun 23, 2011

Messages

2,634

• Sep 2, 2011

• #7

jdraw said:

If lRecordsAffected =0 would be the choice to invoke the error handler

Click to expand...

In this case, I know that I expect it to =1 as the code is INSERTing / UPDATEing 1 record.

It was odd that it dropped back to 0 when I did not capture the resulting adoRS object. (shrug)

Yes, other transactions might change multiple records, or or or...