How can ensure that update happens correctly.I am using ADODB as my connection object.If there are some errors i want to rollback all the changes occured and apply the update statement until it got correctly updated.My front end is VB6
and DB is sql server 7
I would use Transaction to ensure all your operation worked perfectly, or not (roll back).
It would be something like this:
BEGIN TRAN MyTran
SAVE TRAN FirstPoint (this create a kind of bookmark)
ROLLBACK TRAN FirstPoint
SAVE TRAN SecondPoint
ROLLBACK TRAN SecondPoint
COMMIT TRAN MyTran
Read more around this different T-SQL statements, they are really increasing the integrity of your data. You'll be in perfect control, no matter what problem happens around your application (I use it a lot for web application when you have chances of timeout!)
You can put triggers FOR UPDATE on tables you're updating. If the update completes, it could write a log in a log table so that you know it's done... But it might be boring if you have a lot of table to update.
Define a trigger on the UPDATE event on this table.
You can use 'deleted' or 'inserted' tables, wich are copies of data of your table before and after the update. Then you can make sure your update was done. Here is an exemple:
CREATE TRIGGER [check_updated] ON my_table
DECLARE @old_var varchar(50)
DECLARE @new_var varchar(50)
SET @old_var = (SELECT my_field FROM inserted WHERE --your clause)
SET @new_var = (SELECT my_field FROM inserted WHERE --your clause)
INSERT my_log_table (log) VALUES ('same value updated')
What it does:
On the event 'UPDATE' on table 'my_table', get the value before and after the update transaction on a specified field, compare them. If they are equal, write in a log table 'my_log_table' that you previously built the log 'same value updated'.
Make your log in a way it will give you the precise answer around the update operation by adding information (my example is very simple).
I use this when I need to do something if a user update a field from a value to another. I have to make sure the update was done.
I don't know the way of checking the update statement directly on the table (is there one?). That's why I proposed to use a Trigger, which will react only on the event you want, to make you sure this event happened.