473,241 Members | 1,616 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

Data truncation: Data truncated for column 'date' at row 1

113 100+
Hi,

I have a query which updates the projects table of my database, however when I try to run my query with blank values i get the following error:

Expand|Select|Wrap|Line Numbers
  1. Data truncation: Data truncated for column 'date' at row 1
I have done some researching and I believe the problem is to do with the data that is being sent by my cfqueryparam function.

Here is my create function:

Expand|Select|Wrap|Line Numbers
  1.     <cffunction name="create" access="public" output="false" returntype="struct" hint="CRUD Method">
  2.         <!--- arguments for the constructor, all of which are optional (no-arg constructor) --->
  3.         <cfargument name="dsn" displayName="dsn" type="string" hint="Datasource name" 
  4.             required="true" default="" />        
  5.         <cfargument name="id" displayName="id" type="string" hint="The project ID (UUID)" 
  6.             required="true" default="0" />    
  7.         <cfargument name="priority" displayName="priority" type="string" hint="The project priority (Sort order)" 
  8.             required="false" default="1" />
  9.         <cfargument name="type" displayName="type" type="string" hint="The project type" 
  10.             required="false" default="" />            
  11.         <cfargument name="title" displayName="title" type="string" hint="The project title" 
  12.             required="false" default="" />
  13.         <cfargument name="location" displayName="location" type="string" hint="The project location"
  14.             required="false" default="" />
  15.         <cfargument name="description" displayName="description" type="string" hint="The project description"
  16.             required="false" default="" />             
  17.         <cfargument name="body" displayName="body" type="string" hint="The project body description"
  18.             required="false" default="" />                   
  19.         <cfargument name="image" displayName="image" type="string" hint="The project image name" 
  20.             required="false" default="" />
  21.         <cfargument name="image_alt" displayName="image_alt" type="string" hint="The project image alternative text"
  22.             required="false" default="" />
  23.         <cfargument name="movie" displayName="movie" type="string" hint="The project movie"
  24.             required="false" default="" />            
  25.         <cfargument name="pdf_doc" displayName="pdf_doc" type="string" hint="The project pdf reference" 
  26.             required="false" default="" />            
  27.         <cfargument name="word_doc" displayName="word_doc" type="string" hint="The project work doc reference" 
  28.             required="false" default="" />                   
  29.         <cfargument name="date" displayName="date" type="string" hint="The date the project was added"
  30.             required="false" default="" /> 
  31.         <cfargument name="time" displayName="time" type="string" hint="The time the project was added"
  32.             required="false" default="" />             
  33.         <cfargument name="archive" displayName="archive" type="string" hint="The project should be archived"
  34.             required="false" default="" />       
  35.         <cfargument name="display" displayName="display" type="string" hint="The project should be shown"
  36.             required="false" default="" />  
  37.  
  38.         <!--- initialize variables --->
  39.         <cfset var results = StructNew() />
  40.         <cfset var qInsert = 0 />
  41.  
  42.         <!--- defaults --->
  43.         <cfset results.success = true />
  44.         <cfset results.message = "The project was inserted successfully." />
  45.  
  46.         <!--- <cftry> --->
  47.             <cftransaction>
  48.                 <cfquery name="qInsert" datasource="#arguments.dsn#">
  49.                     INSERT INTO
  50.                     projects
  51.                     (
  52.                         id,
  53.                         priority,
  54.                         type,
  55.                         title,
  56.                         location,
  57.                         description,
  58.                         body,
  59.                         image,
  60.                         image_alt,
  61.                         movie,
  62.                         pdf_doc,
  63.                         word_doc,                        
  64.                         date,
  65.                         time,
  66.                         archive,
  67.                         display
  68.                     )
  69.                     VALUES
  70.                     (
  71.                         <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#" null="yes" />,
  72.                         <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.priority#" />,
  73.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.type#" />,                        
  74.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.title#" />,
  75.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#" />,
  76.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.description#" />,                        
  77.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.body#" />,
  78.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image#" />,
  79.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image_alt#" />,
  80.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.movie#" />,                        
  81.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.pdf_doc#" />,       
  82.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.word_doc#" />,                                           
  83.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.date#" />,
  84.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.time#" />,                        
  85.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.archive#" />,
  86.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.display#" />                                                            
  87.                     )
  88.                 </cfquery>
  89.  
  90.                 <!--- Select insert id --->
  91.                 <cfquery name="qMaxID" datasource="#arguments.dsn#">
  92.                     SELECT max( id ) AS maxID FROM projects
  93.                 </cfquery>
  94.  
  95.                 <!--- Add last insert id to results --->   
  96.                 <cfset results.lastInsertID = StructFind(qMaxID,"maxID") />       
  97.  
  98.             </cftransaction>  
  99.         <!--- 
  100.             <cfcatch type="database">
  101.                 <cfset results.success = false />
  102.                 <cfset results.message = "An error occured whilst attempting to update the database, please check you have filled in all the fields correctly and try again." />
  103.                 <cfif cfcatch.detail NEQ "">
  104.                     <cfset results.message = results.message & "Error details: <br />" & cfcatch.detail />
  105.                 </cfif>
  106.             </cfcatch>
  107.         </cftry> --->
  108.  
  109.         <!--- return the struct --->
  110.         <cfreturn StructCopy(results) />
  111.       </cffunction>   
  112.  
As you can see i am just using the cf_sql_varchar cfsqltype, as I'm not sure which is best suited.

I have tryed running the following test query which works fine:

Expand|Select|Wrap|Line Numbers
  1.                     INSERT INTO
  2.                     projects
  3.                     (
  4.                         id,
  5.                         priority,
  6.                         type,
  7.                         title,
  8.                         location,
  9.                         description,
  10.                         body,
  11.                         image,
  12.                         image_alt,
  13.                         movie,
  14.                         pdf_doc,
  15.                         word_doc,                        
  16.                         date,
  17.                         time,
  18.                         archive,
  19.                         display
  20.                     )
  21.                     VALUES
  22.                     (
  23.                         '',
  24.                         '',
  25.                         '',                        
  26.                         '',
  27.                         '',
  28.                         '',                        
  29.                         '',
  30.                         '',
  31.                         '',
  32.                         '',                        
  33.                         '',       
  34.                         '',                                           
  35.                         '',
  36.                         '',                        
  37.                         '',
  38.                         ''                                                            
  39.                     )
  40.  
The cfqueryparam function must be doing something and I don't know what.

Does anyone know what the problem is?

Thanks,

chromis
Nov 21 '08 #1

✓ answered by acoder

Then use CF_SQL_DATE for the cfsqltype.

13 32600
acoder
16,027 Expert Mod 8TB
What's the format/data type of the date field?
Nov 21 '08 #2
chromis
113 100+
What's the format/data type of the date field?
The date field in the db is date.
Nov 21 '08 #3
acoder
16,027 Expert Mod 8TB
Then use CF_SQL_DATE for the cfsqltype.
Nov 21 '08 #4
chromis
113 100+
Ok thanks that sorted the date error, but I'm getting a new error with my time argument, the cfqueryparam for that is <cfqueryparam cfsqltype="cf_sql_time" value="#arguments.time#" /> and the data type is time in the db.

Error:

The cause of this output exception was that: java.lang.NullPointerException.

So the database is getting a null value? I've enabled null for the time field in the db, but still the same problem. Any ideas?
Nov 21 '08 #5
acoder
16,027 Expert Mod 8TB
Check the docs for the cfqueryparam tag (link for CF8) and match the cfsqltype with the ones in your database.
Nov 21 '08 #6
chromis
113 100+
Hmm, I've read through those, but still struggling, here are my mappings:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE IF NOT EXISTS `projects` (
  2.   `id` int(11) NOT NULL auto_increment,
  3.   `priority` tinyint(4) NOT NULL default '0',
  4.   `type` varchar(12) NOT NULL default '',
  5.   `title` text NOT NULL,
  6.   `location` varchar(64) NOT NULL default '',
  7.   `description` text NOT NULL,
  8.   `body` longtext NOT NULL,
  9.   `image` varchar(128) NOT NULL default '',
  10.   `image_alt` varchar(128) NOT NULL default '',
  11.   `movie` varchar(128) NOT NULL default '',
  12.   `pdf_doc` varchar(128) NOT NULL default '',
  13.   `word_doc` varchar(128) NOT NULL default '',
  14.   `date` date default '0000-00-00',
  15.   `time` time default '00:00:00',
  16.   `archive` tinyint(1) NOT NULL default '0',
  17.   `display` tinyint(1) NOT NULL default '1',
  18.   PRIMARY KEY  (`id`)
  19. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
  20.  
Expand|Select|Wrap|Line Numbers
  1.                         <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#" null="yes" />,
  2.                         <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.priority#" />,
  3.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.type#" />,                        
  4.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.title#" />,
  5.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#" />,
  6.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.description#" />,                        
  7.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.body#" />,
  8.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image#" />,
  9.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.image_alt#" />,
  10.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.movie#" />,                        
  11.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.pdf_doc#" />,       
  12.                         <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.word_doc#" />,                                           
  13.                         <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.date#" />,
  14.                         <cfqueryparam cfsqltype="cf_sql_time" value="#arguments.time#" />,                        
  15.                         <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.archive#" />,
  16.                         <cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.display#" />    
Is there anything wrong in the above?

I'm getting this error now: Invalid data for CFSQLTYPE CF_SQL_TINYINT.
Nov 21 '08 #7
acoder
16,027 Expert Mod 8TB
Where you have blank values and default ones and not strings, e.g. integers, date, time, etc., use NULL, i.e. null="yes".
Nov 21 '08 #8
chromis
113 100+
Ah great that's working now, thanks again acoder!
Nov 21 '08 #9
acoder
16,027 Expert Mod 8TB
No problem at all. You're welcome :)
Nov 21 '08 #10
chromis
113 100+
Hi,

<cfqueryparam cfsqltype="cf_sql_tinyint" value="#arguments.display#" />

Is producing the following error:

Invalid data for CFSQLTYPE CF_SQL_INTEGER.

It's moaning because I have removed the null="yes" setting in the cfqueryparam but unfortunately it enters NULL into the database when the setting is true, regardless of the contents of the variable. So having removed it I am left with this problem, how do I keep MySQL happy?

Thanks.
Nov 26 '08 #11
acoder
16,027 Expert Mod 8TB
Are you passing a blank string? For non-string fields, set a default value instead, e.g. 0.
Nov 26 '08 #12
chromis
113 100+
Yes I am already doing that which is strange.

Expand|Select|Wrap|Line Numbers
  1.         <cfargument name="archive" displayName="archive" type="string" hint="The project should be archived"
  2.             required="false" default="0" />       
  3.         <cfargument name="display" displayName="display" type="string" hint="The project should be shown"
  4.             required="false" default="1" />
  5.  
I've done some reading and come across a post which talks about using the Val function like so:

Expand|Select|Wrap|Line Numbers
  1. <cfqueryparam cfsqltype="cf_sql_tinyint" value="#Val(arguments.display)#" /> 
  2.  
This seems to keep MySQL happy as it ensures the value is an integer, although I was passing it an integer anyway...

It's fixed now at least, thanks again for your help.
Nov 26 '08 #13
acoder
16,027 Expert Mod 8TB
Yes, that's correct. I forgot about Val. The defaults are strings, so using val should solve the problem.
Nov 26 '08 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Arabian143 | last post by:
Hey everyone .. i have a website going .. .. ofcourse you use the Ws-FTP to upload the photos to the page .. and i have to use SQL program to insert the file names and values into the...
3
by: RDRaider | last post by:
How can I find which record(s) cause this error: Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated. I have tried Profiler...
1
by: Bernie Yaeger | last post by:
What causes the error 'string or binary data would be truncated'? Here's the routine that sometimes causes the error, sometimes not: irow("ctotal") = FormatCurrency(irow("total"), 2,...
1
by: languy | last post by:
Hi there I'm having a problem when using the SqlDataAdapter. When calling the Update(DataSet, string) method I get the following error message "String or binary data would be truncated". The...
0
by: dileepkumar | last post by:
I'm trying to save file data to sqlserver. file data is converted to sysyte.io.stream and passed through webservice and in Business layer i changed the stream to byte array and passed it as a...
7
Coldfire
by: Coldfire | last post by:
i am having error ....details are ASP.Net application...in which I have a textbox <asp:TextBox ID="Other" TextMode=SingleLine CssClass="serviceBox" Width="250" Height="45" Runat="server"...
12
by: Juan Carlos Espinoza | last post by:
i have a problem com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'FGiroNPagDol' at row 1 at...
2
by: sunkesula | last post by:
I update a field in the database that gives the last update time. The first time I edit the item it puts a value in this field. The second time the applications fails with The statement has...
2
by: david | last post by:
I've noticed that the following compiles (as C) under both VS8 and gcc with no warnings, even though there's a possibility of data truncation from enum to unsigned char. It does generate a warning...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.