Skip navigation
Currently Being Moderated

Is there a way to avoid the error and avoiding the need to write seperate queries

Nov 3, 2008 7:12 AM

Say I have a query that uses a param in the sql :

<cfqueryparam value="#arguments.forwardValue#" cfsqltype="cf_sql_integer">

However sometimes the value forwardValue will contain nothing ie nothing entered in the text input.

This will generate an error from the CF side. Is there a way to avoid the error and avoiding the need to write seperate queries?
 
Replies
  • Currently Being Moderated
    Nov 3, 2008 7:36 AM   in reply to nikos101
    Do some if/else logic to create a variable that will be either true or false, depending on whether or not arguments.forwardvalue is an integer. Then use that variable in the null attribute of cfqueryparam.
     
    |
    Mark as:
  • Currently Being Moderated
    Nov 3, 2008 8:09 AM   in reply to nikos101
    Try this.....

    <cfqueryparam value="#arguments.forwardValue#" cfsqltype="cf_sql_integer" null=#not isNumeric(arguments.forwardValue)#>

    Phil
     
    |
    Mark as:
  • Currently Being Moderated
    Nov 3, 2008 8:10 AM   in reply to Dan Bracuk
    > Do some if/else logic to create a variable that will be either true or false, depending on whether
    > or not arguments.forwardvalue is an integer. Then use that variable in the null attribute of cfqueryparam.

    I think CF always validates the value, even when you are using the null attribute. So you may have to resort to using val(arguments.forwardValue) in combination with the if/else logic for the null attribute

    <cfqueryparam value="#val(arguments.forwardValue)#"
    cfsqltype="cf_sql_integer"
    null="#not IsNumeric(arguments.forwardValue)#">
     
    |
    Mark as:
  • Currently Being Moderated
    Nov 3, 2008 8:27 AM   in reply to nikos101
    > you may have to resort to using val(arguments.forwardValue) in
    > combination with the if/else logic for the null attribute

    Hmm.. I guess I was wrong about that. I just tried it with MS SQL and it works just fine ... _without_ the val(..)
     
    |
    Mark as:
  • Currently Being Moderated
    Nov 3, 2008 9:25 AM   in reply to nikos101
    quote:

    Originally posted by: nikos101
    I just changed they type in CF to string and it wasn't fussy anymore

    If the db field is integer, send your function a non-numeric string and see how fussy your db gets.
     
    |
    Mark as:
  • Currently Being Moderated
    Nov 3, 2008 9:26 AM   in reply to nikos101
    > I just changed they type in CF to string and it wasn't fussy anymore

    That will also allow invalid values like "abc this is not an integer" to pass through to the database, resulting in an ugly conversion error. Using the "null" attribute, as suggested, will validate the values _before_ the query executes. There is little point in hitting the database if the statement will fail anyway.
     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points