Logic Transaction Error

[winz]

hi,

Just wonder, how the transaction help us on if transaction is successful, all of these things will be done or exceuted as the case may be, or if transaction failes then one of then will be executed.
from my example, the transaction can't help me to done my task.

con.BeginTrans
con.Execute "Update Names Set Name = 'Jane' Where ID = 60"
con.Execute "UpdatePro 'Changed Name8', 58"
If con.Errors.Count > 0 Then
con.RollbackTrans
Else
con.CommitTrans
End If
In the Names table there has no ID with value 60. But it won't generate error and continue the con.Execute "UpdatePro 'Changed Name8', 58" is exceuted.

pls advice!

[skhanal]

After your update statement, you should grab the number of rows processed, if it is 0 don't execute the next one.

[winz]

1.) ya, I know the solution, I think I must select the row first, if NOT RS.EOF Then only we update the row, correct????
2.) But, I just wonder why it won't generate error, an I found that most of the developers they update their records by straight use the UPDATE statement without checking that if the row is existed.

Pls advice!

[nicc777]

Ferifying each INSERT or UPDATE can produce a bottleneck in your DB performance, but generally you can grab error messages programatically. This is relative easy in Perl, and I supose the same applies for other programming environments. Each statement executed MUST have some kind of return value. Learn to understand the return value.

My 2c

[andi_g69]

Winz:

re 2) There is no error returned because there is no error just because the update statement does not effect any record!

It is the same as if you ask me how often I have been in Philadelphia and I answer "never". Which is a valid answer ;-)

[winz]

means that as long as it is not effected any record then no matter what SQL statement is -> SELECT/UPDATE/DELETE
then it will consider NONE error, correct??

[andi_g69]

SQL will only returns "physical" errors. E.g. Update failed because of a table lock, Insert fails because it violates constraints, etc

Logical errors as no row effected have to be handled by your application.

[winz]

Logical errors as no row effected have to be handled by your application.

thank so much, means that check if o row return??? Correct???

[andi_g69]

Jep, that's correct

[winz]

I really wonder about the Transaction again:

Condition A:

con.BeginTrans
con.Execute "Update [Names] Set Name = 'kong' Where ID = 20", RecordsEffected
con.Execute "Update [invalidTable] Set Name = 'wong' Where ID = 28", RecordsEffected


Condition B

con.BeginTrans
con.Execute "Update [invalidTable] Set Name = 'wong' Where ID = 28", RecordsEffected
con.Execute "Update [Names] Set Name = 'kong' Where ID = 20", RecordsEffected

If con.Errors.Count > 0 Then
con.RollbackTrans
Response.Write "Transaction Rollback" & err.Description

Else
con.CommitTrans
Response.Write "Transaction Commit & Record Effected is :" & RecordsEffected
End If


1.) If in condition A, the con.Errors.Count can't catch the Error, and commit the transaction, but In condition B it be able to catch the error and rollback the transaction

Pls help...WHY

In these cases, How should we make sure that either all of the tables involved in the transaction should get updated or none of the tables should get updated. We should not allow any chance to update the database partially.