CF_SQL_DATE Issues with MS SQL Server 2005

Posted April 16, 2006 at 12:00 AM by Ben Nadel

Tags: ColdFusion

Reader Comments

For the love of god... thanks so much for posting this. I was having one of those "this is way too easy for it not to be working" moments up until I realized that the SQL Server version was the variable in my equation. Take care!

Hi Ben,

I had a problem with cf_sql_date on MySQL for a simple query that should run on MSSQL2000 and 2005 as well. It seemed that I didn't get the timestamp into the db-field when using this cfsqltype, even when requiring the db-field to be filled with createodbcdatetime(). Taking the hint from your article I switched to cfsqltimestamp and voila, on all db-server environments I got the required timestamp ;-)

Merci!

Thank you posting this and for your blog.

I finally found this blog entry using Google and switching the cfsqltype from cf_sql_date to cf_sql_timestamp solved the problem I was having querying a MS SQL Server 2000 table.

@Bruce,

Glad to help :)

Love it, saved my ass ;)

I am using the POI utility to upload data into a excel spread sheet. I had seen your code helps. The number part works but the date part is a problem in my case.

I tried to use the sqldatetime in my querynew declaration for the columns.

I tried to put the datatime as string in the row because javacast does not accept any other format. I tried float and double. int was out of option in anycase. But non work.

Can you help me?

But the javacast throws an exception.

--------------------------
An error occurred while trying to modify the query named class coldfusion.sql.QueryTable.
Query objects cannot be modified, they can only be displayed.

The error occurred in D:\WebSites\WebServices\Aruna\Student_NoShow1\admin\test1.cfm: line 28

26 : <cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
27 : <!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
28 : <cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
29 : <cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
30 : </cfloop>

----------------------------

sorry I used CF_SQL_TIMESTAMP in the querynew statement.

My code:

<!--- Create query to ouptut to excel. --->
<cfset qMovie = QueryNew(
"StudentFName,StudentLName,StudentID,InstructorName,CoursePrefix,CourseNo,Section,Semester,Date_1,Reason",
"CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,
CF_SQL_VARCHAR, CF_SQL_VARCHAR,CF_SQL_TIMESTAMP,CF_SQL_VARCHAR") />

<!--- Add rows to query. --->
<cfset QueryAddRow( qMovie, #GetAllSemester.recordcount# ) />

<!--- Set row data. --->
<cfloop query="GetAllSemester">
<Cfset rownum=#GetAllSemester.currentrow#>
rownum: #rownum#, #GetAllSemester.currentrow# ,
qMovie[ "StudentFName" ][ #rownum# ] = JavaCast( "string", "#Stud_FN#" )<br />

<cfset qMovie[ "StudentFName" ][ #rownum# ] = JavaCast( "string", "#Stud_FN#" ) />
<cfset qMovie[ "StudentLName" ][ #rownum# ] = JavaCast( "string", "#Stud_LN#" ) />
<cfset qMovie[ "StudentID" ][#rownum# ] = JavaCast( "string", "#STud_no#" ) />
<cfset qMovie[ "InstructorName" ][ #rownum# ] = JavaCast( "string", "#Fac_name#" ) />
<cfset qMovie[ "CoursePrefix" ][ #rownum# ] = JavaCast( "string", "#Area#" ) />
<cfset qMovie[ "CourseNo" ][#rownum# ] = JavaCast( "string", "#NBR#" ) />
<cfset qMovie[ "Section" ][ #rownum# ] = JavaCast( "string", "#Sec#" ) />
<cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
<!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
<cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
<cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
</cfloop>

Error is :

An error occurred while trying to modify the query named class coldfusion.sql.QueryTable.
Query objects cannot be modified, they can only be displayed.

The error occurred in D:\WebSites\WebServices\Aruna\Student_NoShow1\admin\test1.cfm: line 28

26 : <cfset qMovie[ "Semester" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />
27 : <!--- <cfset qMovie[ "Date_1" ][ #rownum# ] = JavaCast( "string", "#Strm_Desc#" ) />--->
28 : <cfset qMovie[ " Date_1" ][ #GetAllSemester.currentrow# ] = JavaCast( "double", "#gdate#") />
29 : <cfset qMovie[ "Reason" ][ #rownum# ] = JavaCast( "string","#Reason#" ) />
30 : </cfloop>

@Aruna,

Usually that error about modifying queries comes from trying to reference a query column that doesn't exist. You shouldn't get an error for storing data in a query, even if it's the wrong data type (the query doesn't really care until you try to actually use the data).

Usually, this is due to a spelling mistake or leaving a space somewhere.

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment