Results 1 to 12 of 12

Thread: Update Status

  1. #1
    Join Date
    May 2002
    Location
    Indai
    Posts
    11

    Arrow Unanswered: Update Status

    Hii all

    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

    please help it out

  2. #2
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    I would use Transaction to ensure all your operation worked perfectly, or not (roll back).

    It would be something like this:
    ___________________
    BEGIN TRAN MyTran

    your SQL
    SAVE TRAN FirstPoint (this create a kind of bookmark)

    your SQL
    ROLLBACK TRAN FirstPoint

    your SQL
    SAVE TRAN SecondPoint

    your SQL
    ROLLBACK TRAN SecondPoint

    your SQL
    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!)

    Hope this will help.
    Data Climber

  3. #3
    Join Date
    May 2002
    Location
    Indai
    Posts
    11
    After the update made on the database, can i get any value in return to ensure that whether the update is successfull or a faliure.

  4. #4
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    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.
    Data Climber

  5. #5
    Join Date
    May 2002
    Location
    Indai
    Posts
    11
    No this problem is mainly for one table.so can u explain more ...
    what u mean by log table ? how can i access that ? pls explain more about that
    thank u very much.

  6. #6
    Join Date
    May 2002
    Location
    Indai
    Posts
    11
    In most of the time i am updating only one row but in certain times my update affects more columns so how i know that all the rows are updated succuessfully.

  7. #7
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    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
    FOR UPDATE
    AS
    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)

    IF(@new_var=@old_var)
    BEGIN
    INSERT my_log_table (log) VALUES ('same value updated')
    END
    ___________

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

  8. #8
    Join Date
    May 2002
    Location
    Indai
    Posts
    11
    hey one doubt about that ...what is the advantage of checking the status of the update using a trigger insted of checking the same from the table itself.i am confussed.

  9. #9
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    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.
    Data Climber

  10. #10
    Join Date
    May 2002
    Location
    Indai
    Posts
    11

    Arrow

    I mean after the update i can send a SELECT statement to that particular table itself,to ensure that the update is successfull .Then What is the advantage of going for a trigger.

    Can I get any return value to my ADODB connection to ensure that update happens successfully or not.

  11. #11
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    When you send the UPDATE request, I don't think SQL server sends you a status back, unless you get an error.

    I think the solution to your problem depends more on the architecture of your system (What is the technologie making the SQL requests?).

    If your application is using synchronous technologie, and if there is a problem on the update execution, it won't go further. (for my part, I'm using Java/ASP technologies, for web programing).

    Otherwise, I'm afraid you have to make another SELECT to confirm the UPDATE was successfully done before continuing you're process.
    Data Climber

  12. #12
    Join Date
    Feb 2002
    Posts
    7
    If adodb object is not able to complete any task then it returns error number except 0. Check if error number is not 0.


    syntax: adoconn.error<>0

    or if error happens you can loop through the adoconn.errors collection with for each..... error no 547 returns if record is changed by others before update happens.

    This is from vb side.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •