SQL Server 2005 issue with CF8

Report · Apr 12, 2009

Im having issues inserting information into a database via CFQUERY tag. Error and SQL structure are posted below:

[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated. The error occurred in X:\Inetpub\wwwroot\www\www\register.cfm: line 470
Called from X:\Inetpub\wwwroot\www\www\register.cfm: line 441
Called from X:\Inetpub\wwwroot\www\www\register.cfm: line 98
Called from X:\Inetpub\wwwroot\www\www\register.cfm: line 1
Called from X:\Inetpub\wwwroot\www\www\register.cfm: line 470
Called from X:\Inetpub\wwwroot\www\www\register.cfm: line 441
Called from X:\Inetpub\wwwroot\www\www\register.cfm: line 98
Called from X:\Inetpub\wwwroot\www\www\register.cfm: line 1

468 : VALUES 
469 : <cfoutput>
470 : ('#encuser#', '#encpass#', '#email#', '#CGI.REMOTE_ADDR#', '#dobmd#', '#form.doby#', '#datenow#', '#strvalidate#')
471 : </cfoutput>
472 : </cfquery>

SQLSTATE	22001
SQL	INSERT INTO USERINFO (uname, upass, uemail, uip, udobmd, doby, udatereg, uvalcode) VALUES ('D7FC922DA32B202114E31FA0DBF3E5815F9E4EC3E01460BE', '8D8E0F9FFDDD4A9BA1324F87E052075AE39FF17B24F1BE0C', 'test@test.com', '12.34.567.89', '11/17', '1984', '04/12/2009', 'uCz7xXzJ')
VENDORERRORCODE	8152
DATASOURCE	furrycombine_master

Any information would be greatly appreciated. I've gone through the database, and all fields have enough length. I even increased them all by 5 just to give them a bit extra room.

Cheers,

Sean Kirby

Report · Apr 12, 2009

Sean,

This might not get you to the ultimate answer but, as a test, try the replacing your quoted values (i.e., '#encuser#') with cfqueryparams.

For example:

INSERT INTO ( x, y, z )

VALUES(

<cfqueryparam cfsqltype='cf_sql_varchar' value='#formx#' />,

<cfqueryparam cfsqltype='cf_sql_integer' value='#formy#' />,

)

Based on cfqueryparam's behavior, you might find a better, more useful error this way.

Report · Apr 12, 2009

Looking a bit closer at the database, one of the colums in the table had a Default setting of (user_name[]), where i wanted 'user'. I entered it without the single quotes, and that is what was causing the error.

Thanks in advance,

Sean Kirby