Skip to main content
hanzelmans
Known Participant
Nov 7, 2019 4:37 PM UTC
Question

Empty String Error while using CFQUERYPARAM

I continue to receive an Empty String error while trying to update a date type field (SQL Server).

I'm using the NULL attribute, but that doesn't change anything when I pass an empty date field in my form. 

Any ideas how to handle this?

SET [eyeExamDate] = <cfqueryparam value= "#createdate(year(formdata.examMonth), month(formdata.examMonth), 01)#" cfsqltype="cf_sql_date" 				null="Yes" />

The function works fine with a date, but with none I have no luck.

I've tried using a cfparam in case there is no date passed, but that doesn't work. 

This topic is closed to new replies. Start a new post to keep the conversation going.

10 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
Nov 7, 2019 8:17 PM UTC

You seem to have made a mistake. The argument for the functions year() and month() should be a datetime object. But you seem to be passing the number of months instead. In any case, I shall assume your intention is createdate(formdata.examYear, formdata.examMonth, 01).

Then you could do something like:

<cfset isNullDate = true>

<cfif isNumeric(formdata.examYear) and isNumeric(formdata.examMonth) and isDate(createdate(formdata.examYear, formdata.examMonth, 01))>
    <cfset isNullDate = false>
</cfif>

SET [eyeExamDate] = <cfqueryparam value= "#createdate(formdata.examYear, formdata.examMonth, 01)#" cfsqltype="cf_sql_date" null="#isNullDate#" />
Translate
hanzelmansAuthor
Known Participant
Nov 7, 2019 8:23 PM UTC

Hi all,

Again, thanks for the fast responses.

 

The field is named examMonth (kind of a mistake) but it passes year and month.  I'm just defaulting in the first day of the month to complete the date.  The month and year are both passed in the form field.

 

However, I will give your solution a try.  It certainly won't hurt.  I rigged it so that it works, but it certainly isn't the cleanest of solutions.  I commented it heavily so in the future I will know why it looks so freaking ugly.

 

Steve

Translate
WolfShade
WolfShade
Legend
Nov 7, 2019 4:49 PM UTC

Nevermind.. it may be cfqueryparam:

 

null      Optional     no     Whether parameter is passed as a null value:

·         yes: tag ignores the value attribute.

·         no

 

If you set null="yes", then cfquery ignores the value.  So it _should_ be passing blank.  Which makes me think that the eyeExamDate column is not set to allow null values.

 

V/r,

 

^ _ ^

Translate
hanzelmansAuthor
Known Participant
Nov 7, 2019 4:53 PM UTC

No.  The column is set.

 
Translate
WolfShade
WolfShade
Legend
Nov 7, 2019 6:02 PM UTC

Sorry.. I replied very quickly after the first post.  🙂

 

So.. null="yes" should send a blank, regardless of what is in the value attribute.  Is examMonth in the form a select?  Or input type="text"?  If it's a select, are you cfparam'ing the examMonth on the action page?

 

V/r,

 

^ _ ^

Translate
WolfShade
WolfShade
Legend
Nov 7, 2019 4:45 PM UTC

Check the database.  If the table were created so that eyeExamDate can accept a null value, then it shouldn't be an issue.  But assuming that form.examMonth is blank, I believe createDate() still creates a date object.  The help page for createDate() doesn't say what is returned if no values are included.  So..

 

V/r,

 

^ _ ^

Translate
hanzelmansAuthor
Known Participant
Nov 7, 2019 4:48 PM UTC

Thanks for the fast response!

The eyeExamDate is set to Allow Nulls.

If I do a CFDUMP, it shows [empty string].  I was guessing the same thing; that the issue is coming from the createDate() but was hoping that the NULL attribute in CFQUERYPARAM would override it (no value passed, so I'll use this...).

Thanks for the input!

 

Steve

Translate
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices