Copy link to clipboard
Copied
How do I prevent my loop from trying to insert an empty value into mySQL database? The form I am using has four rows automatically created. If they only fill in three rows than I get problems with the fourth row being empty. Below is my code that works fine as long as they don't leave a field empty.
<cfloop from="1" to="#form.numba#" index="idx">
<cfset getqty = form["qty" & idx]>
<cfset getitem = form["item" & idx]>
<cfset getunit = form["unit" & idx]>
<cfset gettotal = form["total" & idx]>
<cfquery name="insertItems" datasource="#application.dsn#">
INSERT into items
(orderID,
qty,
item,
unit,
total
)
VALUES(
<cfqueryparam value="#getoid#" CFSQLType="CF_SQL_INTEGER" >,
<cfqueryparam value="#getqty#" CFSQLType="CF_SQL_INTEGER" >,
<cfqueryparam value="#getitem#" CFSQLType="CF_SQL_VARCHAR" >,
<cfqueryparam value="#getunit#" CFSQLType="CF_SQL_INTEGER" >,
<cfqueryparam value="#gettotal#" CFSQLType="CF_SQL_INTEGER" >
)
</cfquery>
</cfloop>
Thanks in advance.
The user may omit filling any of the rows, not necessarily the last. In fact, he may omit filling the entire form.
The best solution is to validate the data before doing the database insert. For example, you could check for empty rows as follows
<cfif isDefined("form.numba")>
<cfloop from="1" to="#form.numba#" index="row">
<cfset getqty = form["qty" & row]>
<cfset getitem = form["item" & row]>
<cfset getunit = form["unit" & row]>
<cfset gettotal = form["total" & row]>
<cfif (getqty i
...Copy link to clipboard
Copied
The user may omit filling any of the rows, not necessarily the last. In fact, he may omit filling the entire form.
The best solution is to validate the data before doing the database insert. For example, you could check for empty rows as follows
<cfif isDefined("form.numba")>
<cfloop from="1" to="#form.numba#" index="row">
<cfset getqty = form["qty" & row]>
<cfset getitem = form["item" & row]>
<cfset getunit = form["unit" & row]>
<cfset gettotal = form["total" & row]>
<cfif (getqty is "") and (getitem is "") and (getunit is "") and (gettotal is "")>
<!--- Row is empty --->
<!--- Do something. For example, break out of loop, inform user and get him to resubmit --->
<!--- Alternatively, skip insertion of this particular row --->
<cfelse>
<!--- Row is non-empty --->
</cfif>
</cfloop>
</cfif>
Copy link to clipboard
Copied
Thanks BKBK, that did it.