Error : String or binary data would be truncated.

I am trying to submit some form values to the database

However i am getting an error when i insert something like (E. Jensen) or N/o . or such special characters in text fields like street. The error is

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated.  
 
The error occurred in E:\docs_secure\icepe\addhvp.cfm: line 9
 
7 : values('#EVALUATE('form.sitename')#','#EVALUATE('form.street')#','#EVALUATE('form.city')#','#EVALUATE('form.region')#'
8 : ,'#EVALUATE('form.county')#','#EVALUATE('form.zip')#','#EVALUATE('form.state')#','#EVALUATE('form.colocator')#',
9 : '#EVALUATE('form.siteowner')#','#EVALUATE('form.hvpowner')#','#EVALUATE('form.hvptype')#','#EVALUATE('form.phone')#','#EVALUATE('form.note')#')
10 : </cfquery>
11 : <cfquery  name="getsid1" datasource="#dbdsn#" username="#dbuser#" password="#dbpw#">
 

--------------------------------------------------------------------------------
 
SQL    insert into site_info (sitename,street,city,region,county,zip,state,colocator_name,sowner_name,owner_hvp,type_of_hvp,phone_no,note) values('McCall','Leonard Avenue, (E. Jensen )','Fresno','' ,'','','CA','Larry Duba', 'PGE','Unknown','None','268-0441','')  
DATASOURCE   sqldatas
VENDORERRORCODE   8152
SQLSTATE   22001
 

What do i need to do here?
anup001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rob_lorentzCommented:


remove the evaluate functions

 values('#form.sitename#','#form.street#','#form.city#','#form.region#', '#form.county#','#form.zip#','#form.state#','#form.colocator#',
'#form.siteowner#','#form.hvpowner#','#form.hvptype#','#form.phone#','#form.note#')
IanaldoCommented:
Also make sure that you are inserting the correct values into the correct format fields,as in, the value in city should be inserted intoa text field, and since the error is occuring on line 9, there maybe a field there that is being inserted into maybe a number field, also check you relationships in your database!

Hope that Helps!
Tacobell777Commented:
> String or binary data would be truncated.  

Really means that you are trying to insert more characters than the field allows, for example VARCHAR(20) on your field and you try and
insert data with more than 20 chars you get this error.
Tacobell777Commented:
and yes, get rid of the evaluate, its only if you want to evaluate something double, # already evaluates for you
trailblazzyr55Commented:
<cfquery name="qry" datasource="sqldatas">
INSERT INTO site_info (sitename, street, city, region, county, zip, state, colocator_name, sowner_name, owner_hvp,
                                   type_of_hvp, phone_no, note)
VALUES (
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.sitename#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.street#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.city#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.region#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.county#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.zip#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.state#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.colocator#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.siteowner#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.hvpowner#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.hvptype#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.phone#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.note#">
)
</cfquery>

If inserting to SQL SERVER make sure your field types are "varchar" if inserting into MS ACCESS make sure they are "text".
Using cfqueryparam is actually more efficient, and more secure.

Never trust data coming from the browser! URLs can be modified, and form data can be forged. Use <CFPARAM> to test for variable existence and to enforce data type restrictions. In addition, use <CFQUERYPARAM> to enforce type restrictions while improving system performance and scalability.

Here's a list of cfqueryparam attributes and cfsqltype's: http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm

"cfqueryparam" also allows you to define the data type, which allows for more rigorous data checking.

Hope that helps! =^)
~trail

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.