How to insert optional parameters using cfqueryparam

[minli98]

Hi,

Say I have a page that takes in 8 url parameters (url.A, url.B, url.C, ...). Now I want to insert this into my table using <cfqueryparam>

<cfquery>
   insert into tableX(colA, colB, colC, ...)
   values(<cfqueryparam value="#url.A#" cfsqltype="cf_sql_varchar"),
         <cfqueryparam value="#url.B#" cfsqltype="cf_sql_tinyint"),
         <cfqueryparam value="#url.C#" cfsqltype="cf_sql_tinyint"), ...)
</cfquery>

Now, my problem is some of these variables I am inserting are optional. For example, url.B can be a blank. However, if it's left blank, the code above will trigger an error as it's expecting url.B to be of tinyint value. And a blank is not a tinyint.

I know I can insert a null by using <cfqueryparam null="yes">. However this requires me to check whether url.B is blank.

insert into tableX(....)
<cfif url.B neq ''>
   <cfqueryparam value="#url.B#" cfsqltype="cf_sql_tinyint">
<cfelse>
   <cfqueryparam value="#url.B#" null="yes">
</cfif>

Since there are quite a few optional variables, I would need to repeat it for each variable. It's cumbersome and ugly.

Does anyone have an idea how I can simplify this?

Thank you.

Min

 

[imstillatwork]

Do all of your validation outside of the query and then keep the query simple. It will be easier to read and maintain later.

cfparam all the isSomethingNull variables, then check the form fields.

Real form checking an validation does take extra code.

<cfparam name="isBNull" default="no">

<cfif NOT len(url.b)>
<cfset isBNull = "yes">
</cfif>

<cfqueryparam .... null="#isBNull#">

You could make a UDF that makes the code much cleaner.
<cfqueryparam .... null="#isNull(url.b)#">

where the udf isNull would check the length of the argument value and return yes or no.




Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[minli98]

Kevin,

I like the idea of udf. It will clean up my code a bit. Thanks!

Min