• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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

Enthusiast ,
Nov 03, 2008 Nov 03, 2008

Copy link to clipboard

Copied

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?
TOPICS
Database access

Views

849

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 03, 2008 Nov 03, 2008

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Nov 03, 2008 Nov 03, 2008

Copy link to clipboard

Copied

> 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)#">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Nov 03, 2008 Nov 03, 2008

Copy link to clipboard

Copied

Try this.....

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

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Nov 03, 2008 Nov 03, 2008

Copy link to clipboard

Copied

> 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(..)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Nov 03, 2008 Nov 03, 2008

Copy link to clipboard

Copied

I just changed they type in CF to string and it wasn't fussy anymore

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 03, 2008 Nov 03, 2008

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Nov 03, 2008 Nov 03, 2008

Copy link to clipboard

Copied

LATEST
> 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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation