Highlighted

Empty String Error while using CFQUERYPARAM

New Here ,
Nov 07, 2019

Copy link to clipboard

Copied

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. 

TOPICS
Advanced techniques

Views

210

Likes

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

Empty String Error while using CFQUERYPARAM

New Here ,
Nov 07, 2019

Copy link to clipboard

Copied

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. 

TOPICS
Advanced techniques

Views

211

Likes

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
Nov 07, 2019 0
LEGEND ,
Nov 07, 2019

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

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
Reply
Loading...
Nov 07, 2019 0
New Here ,
Nov 07, 2019

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Nov 07, 2019 0
LEGEND ,
Nov 07, 2019

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

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
Reply
Loading...
Nov 07, 2019 0
New Here ,
Nov 07, 2019

Copy link to clipboard

Copied

eyeExam.PNGNo.  The column is set.

 

Likes

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
Reply
Loading...
Nov 07, 2019 0
LEGEND ,
Nov 07, 2019

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

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
Reply
Loading...
Nov 07, 2019 0
New Here ,
Nov 07, 2019

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Nov 07, 2019 0
New Here ,
Nov 07, 2019

Copy link to clipboard

Copied

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.  examDates.PNG

Likes

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
Reply
Loading...
Nov 07, 2019 0
LEGEND ,
Nov 07, 2019

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

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
Reply
Loading...
Nov 07, 2019 0
Adobe Community Professional ,
Nov 07, 2019

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Nov 07, 2019 0
New Here ,
Nov 07, 2019

Copy link to clipboard

Copied

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

Likes

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
Reply
Loading...
Nov 07, 2019 0