Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Handle a situation when record is locked

    Hi: How to handle a situation when i try to update a record and its lock. Is there any way when its updating a record and its lock the control will be transfer to another SQL insert statement?

    Thanks.

    ' open DEP table in SQL
    sqlquery = "SELECT * from tbl_DistList_DEP WHERE Updated = 1"
    oDBUtil.OpenADORecordSet sqlquery, cnADOSQL, rsADO, 0, adCmdUnknown
    ' Update DM data base
    Do While Not rsADO.EOF
    i = i + 1
    ' send update query to table dbtr in Debtmaster
    sqlquery = "Update dm.Dbtr SET ASSIGN_ID = '" & rsADO!Worklist &
    "' " & _
    "WHERE DEBTOR_ID = '" & rsADO!Debtor_id & "'"
    On Error Resume Next
    cnDMUpdate.Execute sqlquery


    ' save previous and new worklists
    sOld = rsADO!Oldworklist
    sNew = rsADO!Worklist


    ' send query to dbtract in debtmaster
    sqlquery = "INSERT INTO dm.dbtract (Debtor_id, Comments, User_ID,
    act_Date, act_Time) " & _
    "SELECT '" & rsADO!Debtor_id & "' as debtor_id,'Assign
    ID Changed from " & sOld & " to " & sNew & _
    " BatchID = " & sbatchid & "' as Comments, '" &
    sUserIDDM & "', getdate() ,'" & GetTimeStamp & "'"
    cnDMUpdate.Execute sqlquery


    rsADO.MoveNext
    Loop

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    AFAIK, the only valid approach with VB is to use asynchronous database objects.

    You declare them withevents.

    This allows your app to run in a separate thread from ADO.

    You will need to add code to handle the asynchronous events, and add a timer that you'll initialize when you start your query.

    Since the two processes are now running in a separate thread, you can monitor the time taken to process the query, and if the time exceeds a set amount, you can cancel the query, and start another one.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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