asked on

String or binary data would be truncated

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

ASKER CERTIFIED SOLUTION

Gidi Kern

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gidi Kern

Use the following code to see the problem

create table temp (a char(5))

insert into temp values ('123456')

gkern

thomasdodds

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?

Anthony Perkins

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

tfurry

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

pchristoph

ASKER

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 <new Unit> and therefore it was too long.
In query analyzer I would use <new Unit> in the insert statement and therefore it worked.

Christoph