String or binary data would be truncated

pchristoph
pchristoph used Ask the Experts™
on
Hi,

I have the following table:

curr_id              int      4   NOT NULL
curr_Name            varchar  50  NOT NULL
curr_Exchange_Rate   decimal  9  (18,5)
curr_Short           varchar  15

and the following SQL statement, which is put together in an ASP file:

insert into Currencies (curr_name, curr_exchangeRate, curr_short) values ('<new Currency>','0.0','<new Unit>')

Executing it via the ASP page returns "String or binary data would be truncated".
But executing it with Query Analyzer inserts it correctly.

What's wrong????

Thank you,
Christoph

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It looks like you are inserting data that is longer than the declared length.
'<new Currency>' or'<new Unit>' with length longer than varchar (50) or varchar (15)

I guess in the query analyzer you are using data that fits

gkern

Use the following code to see the problem

create table temp (a char(5))

insert into temp values ('123456')

gkern
returned messages (errors and/or warnings) from a DB to ADO will result in ADO Errors and thus the action gets halted ... you will have to manually cut your values to fit in ASP or up the size of your table columns if data is consitently larger than you planned for...

"But executing it with Query Analyzer inserts it correctly." - do you mean that the full length of the string is inserted or that the data gets truncated (cut) without a show stopping error?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012

Commented:
And of course if you want to avoid the error message you can set ansi_warnings off. (Although why you would is another matter) Here is an example:

Create Table #Temp (a char(1))
Set Ansi_Warnings Off
Insert #Temp values ('1234')
Select a From #Temp
Drop Table #Temp

Anthony

Commented:
Would you mind posting the relevant ASP code?  Thanks.
Tim

Author

Commented:
Thank you for all your suggestions!!!!
I checked the ASP source again and the problem was this:
in the ASP page <new Unit> was actually transformed into &lt;new Unit&gt; and therefore it was too long.
In query analyzer I would use <new Unit> in the insert statement and therefore it worked.

Christoph

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial