March 13, 2007 at 3:39 pm
I have two rows I'm trying to update directly in SQL Server 2005 Express, but it keeps giving me the following error:
-------------------------------------------------------------------
No row was updated.
The data in row 236 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
-------------------------------------------------------------------
All the other rows in my table works fine in terms of doing direct updates/deletes.
What might be causing this?
March 14, 2007 at 7:02 am
The reason is that the data you're trying to put into one of the fields is longer than the definition for that field.
For example, say you have a table with a field "firstName" and it's defined to be varchar(10). Inserting 'John', 'Maryann' and 'Dexter' will work fine. However, an attempt to insert 'Saramandeser' would result in a "String or binary data would be truncated" error since 'Saramandeser' has twelve characters.
SQL Server will not make the assumption that you wish to only store the first n or last n characters, but will present you with this error. There are (at least) two solutions:
1 - Alter the column(s) which are too small to widen them. For the example above, this would accomplish the task:
ALTER TABLE employees ALTER COLUMN firstName varchar(25)
2 - Change the source of your insert or update to only use the necessary 10 characters:
INSERT INTO employees (firstName) SELECT LEFT(fName, 10) FROM hr_employees
Of course, if you're inserting or updating from a front-end, say ASP.NET web-page or VB.NET Windows Forms application, you'd have to take the appropriate steps in the application.
I'd recommend #1 since it is easier to maintain over time. If you implement #2, then in six months you or someone else implements #1, you'll have room for 'Saramandeser' but still only store 'Saramandes'
Hope this helps!
But boss, why must the urgent always take precedence over the important?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply