Inserting null values into database

[Guest]

Hi,

I am creating a function that inserts a row into a database table. I would like to make some of the function arguments optional; if no values are passed these arguments are not entered.

<cffunction name="insertRow">
<cfargument name="tid" type="numeric" required="yes">
<cfargument name="amt" type="numeric" required="no">

<cfquery name="insertTableRow">
insert into myTable(tid
<cfif isDefined('arguments.amt')>,amount</cfif>)
values(<cfqueryparam value="#arguments.tid#" cfsqltype="cf_sql_integer">
<cfif isDefined('arguments.amt')>,<cfqueryparam value="#arguments.amt#" cfsqltype="cf_sql_money"></cfif>)
</cfquery>
</cffunction>

Since I will have many optional arguments, doing <cfif> for each one of them will get my code unwieldy. Is there a better way to do this?

I tried to set the default value of the optional arguments to null, but that creates error with both <cfargument> and <cfqueryparam>

<cfargument name="amt" type="numeric" required="no" value="null">
<cfqueryparam value="#arguments.amt#" cfsqltype="cf_sql_money">

Any suggestions?

Thanks,
Min

[Newsgroup_User]

set the default value for numeric arguments to 0 and string arguments to
'' (empty string)

then use the NULL attribute in your <cfqueryparam> tags:

for numeric arguments:
<cfqueryparam value="#arguments.amt#" cfsqltype="cf_sql_money"
null="#yesnoformat(arguments.amt)#"> - 0 evaluates to FALSE/NO as
boolean value)

for text/string arguments:
<cfqueryparam value="#arguments.sometextvalue#"
cfsqltype="cf_sql_varchar"
null="#yesnoformat(len(trim(arguments.sometextvalue)))#"> -
len(trim('')) is 0, which will evaluate to FALSE/NO

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Dan_Bracuk]

What if the intent is to submit a value of 0?

[Guest]

I couldn't get a most satisfactory answer, but here's what I ended up doing for numeric arguments. In cfargument, I remove the requirement for the input to be numeric so that I can set the default to "" (empty string). However, if a user tries to enter in a non-numeric value, an error will get triggered by cfqueryparam which requires the input to be numeric.

<cfargument name="amt" required="no" default="">

<cfqueryparam value="#arguments.amt#" null="#not len(arguments.amt)#" cfsqltype="cf_sql_money">

Thanks!

Min

[Dan_Bracuk]

Since one of the unsatisfactory answers was mine, maybe I'll give you a better one. This will work for any data type.

<cfscript>
var OptionalArguments = "textfield,numberfield,datefield";
var textfield_null = false;
var numberfield_null = false;
var datefield_null = false;
<cfscript>
<cfloop list = "#OptionalArguments#" index = "idx">
<cfscript>
if (isDefined("Arguments.#idx#") is false)
variables[idx & "_null"] = true;
</cfscript>

Then in your query, use null = "#textfield_null#", etc in your cfqueryparam tag.