Moving Records from one table to another.. how to?

[bboehle]

I need to take information from one table and move it over to another table within the same database with a click of a button. What I've tried doing is displaying the data then using a button <form method=post action=link><input type=submit value=retire></form> where the link would look something like this

http://www.domain.com/page.cfm?id=#id#&?var1=#var1#

etc etc using a url variable for each varaiable in the record. I also just tried passing along hte id and no other variables.

It then takes you to a page that has a cfinsert and a delete statement to remove the data from table a and insert it in to table b.. the problem is though It only removes the data from table a and does NOT insert it in to table b... here is the code from that page:


<
CFINSERT
DATASOURCE=&quot;db&quot;
TABLENAME=&quot;Retired_Security&quot;
>
<CFQUERY
DATASOURCE=&quot;db&quot;
>
DELETE
FROM Security
WHERE ID = #ID#
</CFQUERY>

Thanks in advance for any help!
Brandon

 

[bboehle]

Hrm ok I changed the code and instead of passing all the data through the url I put in hidden form fields and that seems to work. Either way though it all seems rather redundant please let me know if there is a more simple way of doing this.

 

[kristof]

Hi,

Using a querystring (url variable) doesn't work because CFINSERT requires formfields. The safest way to insert data is to add FORMFIELDS=&quot;var1&quot; (plus any other fields you might want to use) to the tag. If FORMFIELDS isn't specified, the tag will try to add every form variable it encounters.

So, for code reusability and especially readability it is best to add all fields required to the tag.

In addition, if you prefer using a querystring you could use <cfset form.var1 = url.var1> to make an url variable available as a form variable.

But usually its a bit more elegant to use hidden forms.

Hope this clears a few things?

 

[bboehle]

Hey,

Thanks for the input

I ended up doing that when I put the hidden input fields in. Easy enough but to me just seems like alot of time for something to me that should be as simple as like cfcopy record #50 from database x to database y

LOL guess that tag will be kept for cf version 10 ;P

Thanks again!

 

[joepeacock]

I know this thread is long dead, but I just stumbled upon it, so for future reference or anyone else having this problem...

<cfquery datasource=&quot;db&quot;>
INSERT INTO Retired_security
(field1,field2,field3)
SELECT field1,field2,field3
FROM security
WHERE ID = #id#
</cfquery>

Then do the delete query and that should do it.