2. ASPRunnerPro General questions
  3. Move Delete Records to Holding Table
This topic is locked

Move Delete Records to Holding Table

 		1/13/2005 9:54:29 PM
ASPRunnerPro General questions
P
 Preston author

I have a database that holds extremely critical data, but it does have records that need to be deleted from time to time. But I would like to put in a "holding table" for all the deleted records so they can be reviewed again after they are taken from the main table.
Is there any way to modify the "Delete" record from the main list screen to delete the records from the main table and MOVE the records to a holding table?
Sure do appreciate your help. You guys really have a quality product.
Sergey Kornilov admin 1/14/2005

Preston,
you can modify ...list.asp to insert a copy of record into "holding table" before you actually delete a record. See my notes in bold.

...

for i=1 to request.form("mdelete").count

ind = CInt(request.form("mdelete")(i))
*strSQL = "select into HoldingTable from " & strTableName & " where " & AddWrappers(strKeyField) & "=" & gstrQuote & Replace(request.form("mdelete1")(ind), "'", "''") & gstrQuote

dbConnection.Execute strSQL

**
strSQL="delete from " & strTableName &

" where " & AddWrappers(strKeyField) & "=" & gstrQuote & Replace(request.form("mdelete1")(ind), "'", "''") & gstrQuote

...
501136 1/14/2005

Thank you very much for such a thorough and prompt reply. Your application is outstanding.
501137 1/14/2005

It blows up on me. Here's the error. The tblDeceased is the HoldingTable that I want to dump the records to. Is there something else I need to do?
Error Type:

Microsoft VBScript compilation (0x800A0409)

Unterminated string constant

/ALL_REGISTERED_VOTERS_MAIN_TABLE_list.asp, line 62, column 171
strSQL="select * into tblDeceased from " & strTableName & " where " & AddWrappers(strKeyField) & "=" & gstrQuote & Replace(request.form("mdelete1")(ind),"",""")& gstrQuote
Pete M 1/14/2005

Sorry to butt in here
You need to change the name of 'strTableName' in the 'from' part of the script to your equivilant table in _list.
I think..
**SORRY I THINK THIS IS WRONG***
<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=3082&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=3082&image=2&table=forumreplies' class='bbc_emoticon' alt=':(' />
501138 1/15/2005

You'd think that I would need to change the strTableName, but if you look at the rest of the code, I don't think that's the case. Anybody else have any ideas about how to move the records to the holding table and then delete from the main table?
Sergey Kornilov admin 1/16/2005

Preston,
it sounds like Copy&Paste didn't work. In my code I replace single quote character with double signle quotes to keep SQL query valid.
Try to copy&paste it again replacing target table name.
P
 		Preston author 2/2/2005

When I cut and paste the code, it works great to create the table and blow the records into it before they are deleted--but it works only once. The next time I want to delete the records, it gives the error that the HoldingTable already exists. So I think I need to have something with an INSERT INTO SQL.
When I've tried to write this SQL, I keep getting a syntax error. An ASP code expert says it might be something to do with the other functions, AddWrappers, that is preventing this from working.
Can anybody help me fix this? I need this modification made today. Thank you.