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

[mdlueck]

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!

 

[mdlueck]

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]

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

 

[mdlueck]

Resolved!!! 'twas as simple as:

  '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".

 

[mdlueck]

PS... and it appears IMPARATIVE that you accept the adoRS object that Execute will return in order to obtain a valid RecordsAffected return 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]

It seems to me that

If lRecordsAffected <> 1

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

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

 

[mdlueck]

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

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...