Get current table ID before data insert

[chinaeye]

Hi, Friends, anyone know, how coldfusion can get the current table ID before insert into database.
The table ID is auto generated.
thanks.

[azadisaryev]

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>

[Swift]

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

[chinaeye]

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.

[Guest]

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.

[Guest]

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.

[azadisaryev]

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

[chinaeye]

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>

[azadisaryev]

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.

[chinaeye]

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

[azadisaryev]

i am happy i could help!