Empty String Error while using CFQUERYPARAM

[hanzelmans]

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. 

[WolfShade]

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,

^ _ ^

[hanzelmans]

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

[WolfShade]

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,

^ _ ^

[hanzelmans]

No.  The column is set.

[WolfShade]

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,

^ _ ^

[hanzelmans]

I'm actually using the HTML5 type of month, so it's passing a value of "2019-11" or a value in that format.

[hanzelmans]

I think I found the problem.

Even when empty, the fields are still returning a length of 0 so the NULL attribute is never being triggered.  That makes sense and I should have caught that sooner.

However, for all intents I still don't want a value being entered in the DATE columns in the db.

I wish there was a more graceful way of handling this.  

[WolfShade]

I'm not sure how to proceed, then.  Clearly you should keep the cfqueryparam in place; but it's also the root of the issue.

Not the best idea, but is there a way that (if no date is supplied) you could enter 1970-01, setting the date far into the past?  Or would that break something else?

V/r,

^ _ ^

[BKBK]

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

[hanzelmans]

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