Skip to main content
H
hanzelmans
Known Participant
November 7, 2019
Question

Empty String Error while using CFQUERYPARAM

  • November 7, 2019
  • 3 replies
  • 1285 views

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 has been closed for replies.

3 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 7, 2019

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#" />
H
hanzelmansAuthor
Known Participant
November 7, 2019

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

WolfShade
WolfShade
Legend
November 7, 2019

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,

 

^ _ ^

H
hanzelmansAuthor
Known Participant
November 7, 2019

No.  The column is set.

 
WolfShade
WolfShade
Legend
November 7, 2019

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,

 

^ _ ^

WolfShade
WolfShade
Legend
November 7, 2019

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,

 

^ _ ^

H
hanzelmansAuthor
Known Participant
November 7, 2019

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

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