10 Replies Latest reply on Sep 22, 2006 3:51 AM by azadisaryev

    Get current table ID before data insert

    chinaeye Level 1
      Hi, Friends, anyone know, how coldfusion can get the current table ID before insert into database.
      The table ID is auto generated.
      thanks.
        • 1. Re: Get current table ID before data insert
          azadisaryev Level 1
          by 'table id' i suppose you mean the highest auto-incremented primary key?
          <cfquery name="[yourname]" datasource="[yourdsn]">
          SELECT Max([your autoincremented pk field]) AS maxid FROM [your table name];
          </cfquery>
          • 2. Re: Get current table ID before data insert
            Swift Level 1
            ChinaEye,

            While it's true that you can simply get the maximum ID from a query, I would question why you are trying to get the highest ID before you insert the row. What if another process comes and inserts a record between when you find the highest maximum, and then you insert your row?

            This can be solved by returning the ID that you inserted into the table, or by CFLocking the appropriate code.

            Swift
            • 3. Get current table ID before data insert
              chinaeye Level 1
              thanks, Sabaidee ,Swift , I will follow your idea.
              By the way, how could I make the following strings to be insert into database, I could not do it.
              thanks.

              • 4. Re: Get current table ID before data insert
                patweb
                I agree with Swift. To ensure you get the record's max number that you just inserted you should lock the queries so that no other queries can get between them.
                • 5. Re: Get current table ID before data insert
                  patweb Level 1
                  Oh I forgot to try to answer you last question. Try encapsulating the string within the PreserveSingleQuotes coldfusion function. Since you are inserting a string this may help with ensuring all the text goes into the database properly. Let me know how this works for you.
                  • 6. Re: Get current table ID before data insert
                    azadisaryev Level 1
                    re your second question:
                    1) you can use HTMLEditFormat() function to convert all special characters in your string (quotes, brackets, dashes and slashes, etc) into their HTML-escaped equivalents
                    2) why store the complete code? you can just store the .swf file name in the db, and then retrive it and incert with <cfoutput> into the code where the file name is referenced...
                    • 7. Re: Get current table ID before data insert
                      chinaeye Level 1
                      hi,PatWeb, Sabaidee

                      I tried the PreserveSingleQuotes, but not work, will try the HTMLEditFormat() .

                      I had to store the compete code, for the horizontal layout of the video at once, that by following the attached code below.
                      otherwise, it is vertical layout if I only store the .swf file name in the db.

                      Any good idea?
                      Coldfusion have muh advantage than other language when apply it video application, but so far, I didnot see.

                      Thanks and Regards
                      David

                      <CF_Columns Cols="3" Records="#list.RecordCount#">

                      <table border=0>
                      <tr>
                      <!--- Loop through the number of columns desired. --->
                      <cfloop index="LoopCount" from="1" to="3">
                      <!--- Access the start and end variables created by the custom tag. --->
                      <cfset #start#=("start" & #LoopCount#)>
                      <cfset #end#=("end" & #LoopCount#)>
                      <td valign="top">
                      <cfoutput query="list" startrow="#Evaluate(start)#" maxrows="#Evaluate(end)#">
                      #State#<br>
                      </cfoutput>
                      </td>
                      </cfloop>
                      </tr>
                      </table>
                      • 8. Get current table ID before data insert
                        azadisaryev Level 1
                        if i understand correctly what you are trying to achieve, you can use <cfif> loop with MOD operator to get the hor layout:

                        assuming you want 3 records per row:
                        <table>
                        <tr>
                        <cfoutput query="list">
                        <td valign="top">#State#<br></td>
                        <cfif list.currentrow MOD 3 is 0><!--- every 3rd record close the row --->
                        </tr>
                        <cfif list.currentrow neq list.recordcount><!--- if current record is not last, start new row --->
                        <tr>
                        </cfif>
                        </cfif>
                        </cfoutput>
                        <!--- now pad with empty cells any remaining space in the table --->
                        <cfset cellstopad=3-(list.recordcount - (int(list.recordcount/3))*3)><!--- determine number of empty cells to add --->
                        <cfif cellstopad MOD 3 is 1>
                        <cfloop index="i" from="1" to="#cellstopad#">
                        <td> </td>
                        </cfloop>
                        </cfif>
                        </tr>
                        </table>

                        PS: code can be simpler is you do not need valid html.
                        • 9. Re: Get current table ID before data insert
                          chinaeye Level 1
                          Hi, Sabaidee
                          Greate! I apply your solution, and works for the H layout, by just change cfoutput to loops, so there are two loops work with each other.

                          and also simplify the insert data---just store the .swf file name in the db.

                          From past two week till now, it seems ok for my project.

                          Thanks
                          I will visite your site for any business cooperation in future if any.
                          David
                          • 10. Re: Get current table ID before data insert
                            azadisaryev Level 1
                            i am happy i could help!