Update several data simultaneously in a database

[jean_mizrahi]

Hello all,

Who can help me on this : I am trying to update simultaneously all data returned with a simple cfoutput query, where all form tags are indexed with all the id(s) returned by the query (same serie of input/textarea/etc. for each id, but only one single validation button for all id). I am stuck and cannot find a way to do this. Would someone have a simple example of how I could proceed ? That would save my life...

Thanks

Jean

[Dan_Bracuk]

Your question is unclear.

It might mean that you are generating form fields from a query, but it might mean something else.  The comment about single validation button may or may not be relevent.

Perhaps if you could provide more details.

[jean_mizrahi]

Let me try to be more explicit:

I will for example create a simple query such as :

<cfquery datasource="ABC" name="DEF"

select book_id, book_name from books

</cfquery>

Then I create the following form:

<form....>

<cfoutput query="DEF">

#book_id# <input type="text" name="book_#book_id# value="#book_name#><br>

</cfoutput>

<input type="submit" value="Refresh">

</form>

I want to be able to update all the "#book_name#" simultaneously but as I cannot nest a cfquery and insert inside a cfoutput, I need to go through a different path...and I don't know how. An example would be helpful.

If you can solve this for me, I would be really grateful !

Jean

[BKBK]

<cfif isDefined("form.sbmt")>

    <cfoutput query="session.DEF">

        <!--- Update only book names that were changed in the form--->

        <cfif book_name is not form["book_#book_id#"]>

            <cfquery datasource="ABC" name="session.DEF">

                update books

                set book_name = '#form["book_#book_id#"]#'

                where book_id = #book_id#

            </cfquery>

        </cfif>

    </cfoutput>

</cfif>

<!--- Store query in session scope. This makes it dynamically updatable, and available to the update queries above as well as to the form below --->

<cfquery datasource="ABC" name="session.DEF">

select book_id, book_name from books

</cfquery>

<!--- In this test, the form submits to its own page--->

<cfform>

<cfoutput query="session.DEF">

#book_id# <cfinput type="text" name="book_#book_id#" value="#book_name#"><br>

</cfoutput>

    <cfinput name="sbmt" type="submit" value="Refresh">

</cfform>

[jean_mizrahi]

Thank you very much for such a quick reply ! I will try this later today and tell you how it works in the context of what I am trying to do. Jean

[BKBK]

OK. But before that, a minor correction.

The name of the update query need not be session.DEF. To avoid confusion, change it to something like:

<cfquery datasource="ABC" name="updateBook">

                update books           

etc., etc.

</cfquery>

[jean_mizrahi]

Works perfectly. You save my life. Thanks a lot

[Dan_Bracuk]

I've done that a few times.  I just found one of the pages and the logic for updates went like this.  By the way, my form is like yours, where the record id is at the end of the form field name.

First, I ran a query against the database to get exisitng values.

Then, I created a ColdFusion query called formdata.  Then I populate this by looping through the form fields.  Something like this:

<cfloop list="#form.fieldnames#" index="ThisField">

<cfif left(thisfield, 9) is "newrecord" and len(form[thisfield]) gt 0>

code for new records

<cfelseif left(thisfield, 4) is "name">

<cfscript>
ThisCode = RemoveChars(ThisField, 1, 4);
if (len(form[thisfield]) gt 0 ) {  // possible update
// handle commas in the text
ThisValue = replace(form[thisfield], ",", "|^|", "all");
// addNewRow is a udf I wrote that combine QueryAddRow and QuerySetCell

x= addNewRow(formdata,"id,f_organism","#Thiscode#,#ThisValue#");
x = QuerySetCell(formdata,"f_organism", Replace(ThisValue, "|^|", ",", "all"));
}
else { // delete
DeleteList = ListAppend(DeleteList, ThisCode);
}
</cfscript>

Then I do a query of queries to identify the records to update.

<cfquery name="RecordsToUpdate" dbtype="query">

select id, f_organism

from formdata, existingdata

where id = organism_id

and f_organism <> organism

</cfquery>

and update them like this:

<cfloop query="RecordsToUpdate">
<cfquery name="update" datasource="infograms">
update organism
set organism = <cfqueryparam cfsqltype="cf_sql_varchar" value="#f_organism#">

where organism_id = <cfqueryparam cfsqltype="cf_sql_char" value="#id#">
</cfquery>

</cfloop>

Then I do my deletions.