Skip to main content
A
Anonymous
November 15, 2008
Question

Inserting null values into database

  • November 15, 2008
  • 4 replies
  • 2566 views
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
    This topic has been closed for replies.

    4 replies

    D
    Dan_Bracuk
    Inspiring
    November 18, 2008
    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.
    A
    Anonymous
    November 17, 2008
    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
    D
    Dan_Bracuk
    Inspiring
    November 16, 2008
    What if the intent is to submit a value of 0?
    N
    Newsgroup_User
    Inspiring
    November 16, 2008
    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/
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices