Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    53

    Unanswered: insert & delete at the same time

    can i do inserting data into one table and at the same time i want to delete the same recordset in another table. it just like transferring data from active table to archieve table. i have problem to do that.can somebody give me the solution

  2. #2
    Join Date
    Jul 2003
    Posts
    21
    you might be able to write a trigger on the table that you update... On an update to that table, you could add a delete SQL statement in the trigger to delete the record from the other table. Otherwise I would do it in code or a stored procedure..

    HTH

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    What is customarily done is to first do the INSERT query, then do the DELETE query, and do both steps within the purview of a single transaction.

    A "transaction" is an atomic unit of operation: either everything succeeds, or nothing does. Therefore, either all of the rows are successfully copied and deleted, or nothing at all happens. (The transaction is "rolled back.")

    When large numbers of records are being copied, it usually becomes necessary to do the job in smaller chunks, each performed by a transaction. This creates a situation whereby, if the operation fails, one or more (complete) chunks will have been copied but zero or more chunks may remain unchanged. But it controls the size of the "rollback file" that would otherwise have to be built.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Mar 2004
    Posts
    53

    details

    actually i'm not really understand what both of u talking about because i'm new in asp. so, i really need help to elaborate more

  5. #5
    Join Date
    Apr 2004
    Posts
    1

    Re: details

    Originally posted by fisya
    actually i'm not really understand what both of u talking about because i'm new in asp. so, i really need help to elaborate more
    Try this

    objRS.open "Main", objConn, 3, 3 'open recored set for addnew
    objRS.AddNew
    objRS("Record") = request.form("txtRecordNum") 'set DB field = ?
    objRS.Update 'adds recored
    objRS.Close 'close record set

    'Create a sql varibale
    SQL = "Select * FROM DeleteRecord WHERE (Record = '" & what ever record you want to delete & "')

    objRS.open SQL, objConn, 3, 3 'open Record set for deletion
    if objRS.EOF then ' check to see if record exists first
    Error msg record does not exist
    else
    objRS.delete 'delete current record
    end if
    objRS.close ' close the recored set
    set objRS = nothing ' set the record set object to nothing
    objConn.close 'close the connection

    Put this were ever yo u need it to fire. This is one way of doing it.

Posting Permissions

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