Skip navigation
nikos101
nikos101
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?
366 Views   7 Replies   Latest reply: -==cfSearching==-, Nov 3, 2008 9:26 AM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    Nov 3, 2008 7:36 AM   in reply to nikos101
    Report
    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:
  • paross1
    Currently Being Moderated
    2. paross1,
    Nov 3, 2008 8:09 AM   in reply to nikos101
    Report
    Try this.....

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

    Phil
     
    |
    Mark as:
  • -==cfSearching==-
    Currently Being Moderated
    3. -==cfSearching==-,
    Nov 3, 2008 8:10 AM   in reply to Dan Bracuk
    Report
    > 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:
  • -==cfSearching==-
    Currently Being Moderated
    4. -==cfSearching==-,
    Nov 3, 2008 8:27 AM   in reply to nikos101
    Report
    > 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:
  • nikos101
    Currently Being Moderated
    5. nikos101,
    Nov 3, 2008 8:37 AM   in reply to nikos101
    Report
    I just changed they type in CF to string and it wasn't fussy anymore
     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    6. Dan Bracuk,
    Nov 3, 2008 9:25 AM   in reply to nikos101
    Report
    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:
  • -==cfSearching==-
    Currently Being Moderated
    7. -==cfSearching==-,
    Nov 3, 2008 9:26 AM   in reply to nikos101
    Report
    > 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:
Actions

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