close ad
WARNING PC USERS: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Multiple record insert using ColdFusion not working

Thread began 1/17/2011 1:43 pm by sandy170299 | Last modified 1/17/2011 6:26 pm by Jason Byrnes | 838 views | 3 replies |

sandy170299

Multiple record insert using ColdFusion not working

I am not receiving any errors, so I'm not sure where to start troubleshooting. I'm taking the field values from a master table and outputting them on my page, and then the user will check the check boxes which get their values from the master table, and then it will write all of the selections to a NEW table. Following is my code:

<cfinclude template="WA_DataAssist/WA_AppBuilder_CF.cfm">
<!--- application info --->
<cfquery name="getAppInfo" datasource="MYDSN">
SELECT *
FROM dbo.tblApplications
WHERE applicantID = <cfqueryparam value="#Session.applicantID#" cfsqltype="cf_sql_numeric">
</cfquery>
<cfquery name="getCK" datasource="MYDSN">
SELECT *
FROM dbo.tblScholarshipChecklist
</cfquery>
<cfif true>
<cfset RepeatSelectionCounter_1 = 0 >
<cfset RepeatSelectionCounterBasedLooping_1 = false >
<cfset RepeatSelectionNestedLoopStart_1 = 1 >
<cfset RepeatSelectionCounter_1_Iterations = "1" >
<cfif Not RepeatSelectionCounterBasedLooping_1 AND RepeatSelectionCounter_1_Iterations EQ "-1">
<cfset RepeatSelectionCounter_1_Iterations = getCK.RecordCount>
</cfif>
<cfif "getCK" NEQ "None">
<cfif IsDefined("MaxRows_getCK")>
<cfif IsDefined("StartRow_getCK") AND IsDefined("EndRow_getCK")>
<cfset RepeatSelectionNestedLoopEnd_1 = RepeatSelectionNestedLoopStart_1 + EndRow_getCK - StartRow_getCK >
<cfelse>
<cfset RepeatSelectionNestedLoopEnd_1 = RepeatSelectionNestedLoopStart_1 + MaxRows_getCK - 1 >
</cfif>
<cfelse>
<cfset RepeatSelectionNestedLoopEnd_1 = RepeatSelectionNestedLoopStart_1 + getCK.RecordCount - 1 >
</cfif>
<cfelse>
<cfset RepeatSelectionNestedLoopEnd_1 = RepeatSelectionNestedLoopStart_1 + RepeatSelectionCounter_1_Iterations - 1 >
</cfif>
<cfset RepeatSelection_1_PresentIteration = 0>
<cfset RepeatSelection_1_NestArray = ArrayNew(1)>
</cfif>

<cfif (isDefined("Form.submit")) >
<cfset WA_loopedFields = WA_ListToArray("checklistID","|") />
<cfset WA_connection = "MYDSN" />
<cfset WA_table = "dbo.tblApplicantChecklist" />
<cfset WA_redirectURL = "" />
<cfset WA_keepQueryString = false />
<cfset WA_fieldNamesStr = "applicantID|checklistID" />
<cfset WA_columnTypesStr = "none,none,NULL|none,none,NULL" />
<cfset WA_insertIfNotBlank = "applicantID" />
<cfset WA_fieldNames = WA_ListToArray(WA_fieldNamesStr,"|") />
<cfset WA_columns = WA_ListToArray(WA_columnTypesStr,"|") />
<cfset WA_multipleInsertCounter = 0 />
<cfloop condition="(WA_AB_checkMultiInsertLoopedFieldsExist(WA_loopedFields, WA_multipleInsertCounter))">
<cfif (WA_insertIfNotBlank EQ "" OR WA_AB_checkLoopedFieldsNotBlank(WA_ListToArray(WA_insertIfNotBlank,"|"), WA_multipleInsertCounter))>
<cfset WA_fieldValuesStr = "" & ToString(Session.applicantID) & "" & "|" & "" & ToString(WA_AB_getLoopedFieldValue("checklistID", WA_multipleInsertCounter)) & "" />
<cfset WA_fieldValues = WA_ListToArray(WA_fieldValuesStr,"|") />
<cfset insertParamsObj = WA_AB_generateInsertParams(WA_fieldNames, WA_columns, WA_fieldValues, -1) />
<cfset insertParamsObj2 = insertParamsObj[2] />
<cfquery datasource="#WA_connection#">
INSERT INTO #WA_table# (#insertParamsObj[1]#) VALUES (#PreserveSingleQuotes(insertParamsObj2)#)
</cfquery>
</cfif>
<cfset WA_multipleInsertCounter = WA_multipleInsertCounter+1 />
</cfloop>
<cfif (WA_redirectURL NEQ "")>
<cfif WA_keepQueryString AND CGI.QUERY_STRING NEQ "" >
<cfif Find("?", WA_redirectURL) GT 0 >
<cfset WA_redirectURL = WA_redirectURL & "&" & CGI.QUERY_STRING />
<cfelse>
<cfset WA_redirectURL = WA_redirectURL & "?" & CGI.QUERY_STRING />
</cfif>
</cfif>
<cflocation url="#WA_redirectURL#" addtoken="no">
</cfif>
</cfif>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-size: 8px}
.style2 {
color: #990000;
font-weight: bold;
}
-->
</style>
</head>

<body>
<p>Check category/s of scholarship(s) you wish to be considered for. All applicants will be considered for a CSF General Scholarship. Scholarships may change depending on the receipt of funds from donors.</p>
<p align="center" class="style2">Place an X in each field you wish to be considered for.<br />
<br />
Applicant ID: <cfoutput>#Session.applicantID#</cfoutput></p>
<form id="form4" name="form4" method="POST">
<table width="95%" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#CCCCCC">
<tr>
<td width="16%"><div align="center">Check Box</div></td>
<td width="17%"><div align="left">Category</div></td>
<td width="24%"><div align="left">Criteria and/or Area of Study</div></td>
<td width="43%"><div align="left">Name of Scholarship</div></td>
</tr>
<cfoutput query="getCK"><cfif RepeatSelection_1_PresentIteration EQ 0>
<cfset RepeatSelection_1_NestArray = ArrayNew(1)>
<cfsavecontent variable="RepeatSelection_1_temp">
<tr></cfsavecontent>
<cfset RepeatSelection_1_NestArray[1] = RepeatSelection_1_temp>
</cfif>
<cfsavecontent variable="RepeatSelection_1_temp">
<td valign="top"><div align="center">
<input type="checkbox" name="checklistID" id="checklistID" />
<span class="style1">#getCK.checklistID#</span></div></td>
<td valign="top"><div align="left"><br />#getCK.category#</div></td>
<td valign="top"><div align="left">#getCK.criteria#</div></td>
<td valign="top"><div align="left">#getCK.scholarshipName#
<input name="applicantID" type="hidden" id="applicantID" value="#Session.applicantID#" />
</div></td>
</cfsavecontent>
<cfset RepeatSelection_1_NestArray[(2+RepeatSelection_1_PresentIteration)] = RepeatSelection_1_temp>
<cfset RepeatSelection_1_PresentIteration = RepeatSelection_1_PresentIteration+1>
<cfset RepeatSelectionCounter_1 = RepeatSelectionCounter_1+1>
<cfif RepeatSelectionCounter_1 EQ RepeatSelectionNestedLoopEnd_1 AND RepeatSelection_1_PresentIteration LTE RepeatSelectionCounter_1_Iterations-1>
<cfloop from="#RepeatSelection_1_PresentIteration#" to="#RepeatSelectionCounter_1_Iterations-1#" index="RepeatSelectionCounter_1_Idx">
<cfsavecontent variable="RepeatSelection_1_temp">No records match your request.</cfsavecontent>
<cfset RepeatSelection_1_NestArray[(2+RepeatSelection_1_PresentIteration)] = RepeatSelection_1_temp>
<cfset RepeatSelection_1_PresentIteration = RepeatSelection_1_PresentIteration+1>
<cfset RepeatSelectionCounter_1 = RepeatSelectionCounter_1+1>
</cfloop>
</cfif>
<cfif RepeatSelection_1_PresentIteration EQ RepeatSelectionCounter_1_Iterations>
<cfsavecontent variable="RepeatSelection_1_temp"></cfsavecontent>
<cfset RepeatSelection_1_NestArray[(2+RepeatSelection_1_PresentIteration)] = RepeatSelection_1_temp>
<cfset RepeatSelection_1_PresentIteration = 0>
#ArrayToList(RepeatSelection_1_NestArray, "")#
</cfif></cfoutput>
</table>
<div align="center">
<input type="submit" name="submit" id="submit" value="Submit" />
</div>
</form>
</body>
</html>

Sign in to reply to this post

sandy170299

Updated Code. I wanted to make sure that the correct data was being captured, and it is.

<cfoutput>#Session.applicantID#</cfoutput> :: <cfoutput>#IIf(isDefined("Form.checklistID"),"Form.checklistID",DE(""))#</cfoutput>

<cfabort>

It's just not writing anything to the database table. Nothing at all and no errors.
********************************************************************
********************************************************************
********************************************************************
<cfinclude template="WA_DataAssist/WA_AppBuilder_CF.cfm">
<!--- application info --->
<cfquery name="getAppInfo" datasource="MYDSN">
SELECT *
FROM dbo.tblApplications
WHERE applicantID = <cfqueryparam value="#Session.applicantID#" cfsqltype="cf_sql_numeric">
</cfquery>
<cfquery name="getCK" datasource="MYDSN">
SELECT *
FROM dbo.tblScholarshipChecklist
</cfquery>
<cfif true>
<cfset RepeatSelectionCounter_1 = 0 >
<cfset RepeatSelectionCounterBasedLooping_1 = false >
<cfset RepeatSelectionNestedLoopStart_1 = 1 >
<cfset RepeatSelectionCounter_1_Iterations = "1" >
<cfif Not RepeatSelectionCounterBasedLooping_1 AND RepeatSelectionCounter_1_Iterations EQ "-1">
<cfset RepeatSelectionCounter_1_Iterations = getCK.RecordCount>
</cfif>
<cfif "getCK" NEQ "None">
<cfif IsDefined("MaxRows_getCK")>
<cfif IsDefined("StartRow_getCK") AND IsDefined("EndRow_getCK")>
<cfset RepeatSelectionNestedLoopEnd_1 = RepeatSelectionNestedLoopStart_1 + EndRow_getCK - StartRow_getCK >
<cfelse>
<cfset RepeatSelectionNestedLoopEnd_1 = RepeatSelectionNestedLoopStart_1 + MaxRows_getCK - 1 >
</cfif>
<cfelse>
<cfset RepeatSelectionNestedLoopEnd_1 = RepeatSelectionNestedLoopStart_1 + getCK.RecordCount - 1 >
</cfif>
<cfelse>
<cfset RepeatSelectionNestedLoopEnd_1 = RepeatSelectionNestedLoopStart_1 + RepeatSelectionCounter_1_Iterations - 1 >
</cfif>
<cfset RepeatSelection_1_PresentIteration = 0>
<cfset RepeatSelection_1_NestArray = ArrayNew(1)>
</cfif>

<cfif (isDefined("Form.submit")) >

<cfoutput>#Session.applicantID#</cfoutput> :: <cfoutput>#IIf(isDefined("Form.checklistID"),"Form.checklistID",DE(""))#</cfoutput>
<cfabort>

<cfset WA_loopedFields = WA_ListToArray("checklistID","|") />
<cfset WA_connection = "MYDSN" />
<cfset WA_table = "dbo.tblApplicantChecklist" />
<cfset WA_redirectURL = "" />
<cfset WA_keepQueryString = false />
<cfset WA_fieldNamesStr = "applicantID|checklistID" />
<cfset WA_columnTypesStr = "none,none,NULL|none,none,NULL" />
<cfset WA_insertIfNotBlank = "applicantID" />
<cfset WA_fieldNames = WA_ListToArray(WA_fieldNamesStr,"|") />
<cfset WA_columns = WA_ListToArray(WA_columnTypesStr,"|") />
<cfset WA_multipleInsertCounter = 0 />
<cfloop condition="(WA_AB_checkMultiInsertLoopedFieldsExist(WA_loopedFields, WA_multipleInsertCounter))">
<cfif (WA_insertIfNotBlank EQ "" OR WA_AB_checkLoopedFieldsNotBlank(WA_ListToArray(WA_insertIfNotBlank,"|"), WA_multipleInsertCounter))>
<cfset WA_fieldValuesStr = "" & ToString(Session.applicantID) & "" & "|" & "" & ToString(WA_AB_getLoopedFieldValue("checklistID", WA_multipleInsertCounter)) & "" />
<cfset WA_fieldValues = WA_ListToArray(WA_fieldValuesStr,"|") />
<cfset insertParamsObj = WA_AB_generateInsertParams(WA_fieldNames, WA_columns, WA_fieldValues, -1) />
<cfset insertParamsObj2 = insertParamsObj[2] />
<cfquery datasource="#WA_connection#">
INSERT INTO #WA_table# (#insertParamsObj[1]#) VALUES (#PreserveSingleQuotes(insertParamsObj2)#)
</cfquery>
</cfif>
<cfset WA_multipleInsertCounter = WA_multipleInsertCounter+1 />
</cfloop>
<cfif (WA_redirectURL NEQ "")>
<cfif WA_keepQueryString AND CGI.QUERY_STRING NEQ "" >
<cfif Find("?", WA_redirectURL) GT 0 >
<cfset WA_redirectURL = WA_redirectURL & "&" & CGI.QUERY_STRING />
<cfelse>
<cfset WA_redirectURL = WA_redirectURL & "?" & CGI.QUERY_STRING />
</cfif>
</cfif>
<cflocation url="#WA_redirectURL#" addtoken="no">
</cfif>
</cfif>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-size: 8px}
.style2 {
color: #990000;
font-weight: bold;
}
-->
</style>
</head>

<body>
<p>Check category/s of scholarship(s) you wish to be considered for. All applicants will be considered for a CSF General Scholarship. Scholarships may change depending on the receipt of funds from donors.</p>
<p align="center" class="style2">Place an X in each field you wish to be considered for.<br />
<br />
Applicant ID: <cfoutput>#Session.applicantID#</cfoutput></p>
<form id="form4" name="form4" method="POST">
<table width="95%" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#CCCCCC">
<tr>
<td width="16%"><div align="center">Check Box</div></td>
<td width="17%"><div align="left">Category</div></td>
<td width="24%"><div align="left">Criteria and/or Area of Study</div></td>
<td width="43%"><div align="left">Name of Scholarship</div></td>
</tr>
<cfoutput query="getCK"><cfif RepeatSelection_1_PresentIteration EQ 0>
<cfset RepeatSelection_1_NestArray = ArrayNew(1)>
<cfsavecontent variable="RepeatSelection_1_temp">
<tr></cfsavecontent>
<cfset RepeatSelection_1_NestArray[1] = RepeatSelection_1_temp>
</cfif>
<cfsavecontent variable="RepeatSelection_1_temp">
<td valign="top"><div align="center">
<input name="checklistID" type="checkbox" id="checklistID" value="#getCK.checklistID#" />
<span class="style1">#getCK.checklistID#</span></div></td>
<td valign="top"><div align="left"><br />#getCK.category#</div></td>
<td valign="top"><div align="left">#getCK.criteria#</div></td>
<td valign="top"><div align="left">#getCK.scholarshipName#
<input name="applicantID" type="hidden" id="applicantID" value="#Session.applicantID#" />
</div></td>
</cfsavecontent>
<cfset RepeatSelection_1_NestArray[(2+RepeatSelection_1_PresentIteration)] = RepeatSelection_1_temp>
<cfset RepeatSelection_1_PresentIteration = RepeatSelection_1_PresentIteration+1>
<cfset RepeatSelectionCounter_1 = RepeatSelectionCounter_1+1>
<cfif RepeatSelectionCounter_1 EQ RepeatSelectionNestedLoopEnd_1 AND RepeatSelection_1_PresentIteration LTE RepeatSelectionCounter_1_Iterations-1>
<cfloop from="#RepeatSelection_1_PresentIteration#" to="#RepeatSelectionCounter_1_Iterations-1#" index="RepeatSelectionCounter_1_Idx">
<cfsavecontent variable="RepeatSelection_1_temp">No records match your request.</cfsavecontent>
<cfset RepeatSelection_1_NestArray[(2+RepeatSelection_1_PresentIteration)] = RepeatSelection_1_temp>
<cfset RepeatSelection_1_PresentIteration = RepeatSelection_1_PresentIteration+1>
<cfset RepeatSelectionCounter_1 = RepeatSelectionCounter_1+1>
</cfloop>
</cfif>
<cfif RepeatSelection_1_PresentIteration EQ RepeatSelectionCounter_1_Iterations>
<cfsavecontent variable="RepeatSelection_1_temp"></cfsavecontent>
<cfset RepeatSelection_1_NestArray[(2+RepeatSelection_1_PresentIteration)] = RepeatSelection_1_temp>
<cfset RepeatSelection_1_PresentIteration = 0>
#ArrayToList(RepeatSelection_1_NestArray, "")#
</cfif></cfoutput>
</table>
<div align="center">
<input type="submit" name="submit" id="submit" value="Submit" />
</div>
</form>
</body>
</html>

Sign in to reply to this post

sandy170299

I actually got this to work coding it by hand. Sure would have been nice if I didn't have to do that, though...

Sign in to reply to this post

Jason ByrnesWebAssist

where you using data assist 2.0.8? or a previous version? i know that in 2.0.8 we fixed a few bugs with multiple insert.

Please dont past the code from the page into your message, it's unwieldy to deal with that way, instead compress the page in to a zip archive and attach the zip.

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...