7 Replies Latest reply on Jan 4, 2013 6:55 AM by jean.mizrahi

    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

        • 1. Re: Update several data simultaneously in a database
          Dan Bracuk Level 5

          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.

          • 2. Re: Update several data simultaneously in a database
            jean.mizrahi Level 1

            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

            • 3. Re: Update several data simultaneously in a database
              BKBK Adobe Community Professional & MVP

              <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>

              • 4. Re: Update several data simultaneously in a database
                jean.mizrahi Level 1

                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

                • 5. Re: Update several data simultaneously in a database
                  BKBK Adobe Community Professional & MVP

                  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>

                  • 6. Re: Update several data simultaneously in a database
                    Dan Bracuk Level 5

                    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.

                    • 7. Re: Update several data simultaneously in a database
                      jean.mizrahi Level 1

                      Works perfectly. You save my life. Thanks a lot