Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How I use oRS.delete ?

    How I use oRS.delete ? How to refer record to be deleted ?
    How I understand that there is no relate record in other table, to the record to be deleted [to can actually delete this record] ?

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,072
    Thanks
    4
    Thanked 8 Times in 8 Posts
    To use the ADO delete method you just open your recordset and then call the delete method. For example
    Code:
    sSQL = "SELECT * FROM myTable WHERE Color='blue'"
    oRs.Open sSQL, myConnection,1,2
    Do While Not oRs.EOF
    	'delete all records one at a time 
     	oRs.Delete
           	oRs.MoveNext
    Loop
    oRs.Close
    Set oRs = Nothing

    As to how to determine if a related record in another table exists, the best way is to do a query to determine if there is a related record. You may be trying to delete orphaned records and want to make sure there is no parent record (the record in the table that holds the primary key) There are a number of ways to determine if the record has no match in the other table obviusly the first is to do an out right query looking for the record. If eof is returned instead of some records you know that there is no matching record. You can also try an outer join in a query to see which records are returned with the information missing from the recordset.

    To prevent orpaned records, if this is a SQL Server database make sure that you have your constraints set up so that when a record in the table that holds the primary key is deleted you delete all of the records in the related tables by using ON DELETE CASCADE.

    example, this code prevents orphaned UserInfo records because when the Related record in the Users table is deleted it cascades to the UserInfo table and also deletes the record with the foreign key to the parent
    Code:
    ALTER TABLE UserInfo ADD 
    	CONSTRAINT FK_USERINFO Foreign Key (UserID)
    	REFERENCES Users(UserID)
    	ON DELETE CASCADE

    I do have a question, Why not just use a SQL delete statement to delete the records?


  •  

    Posting Permissions

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