truncate long string on insert into an nvarchar() column?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Serge Wautier - 23 May 2008 16:11 GMT

Env.: CF3.5, SQL CE 3.5

Hi All,

I have a table with an nvarchar(200) column. Occasionally, my app will try
to insert a new row with a text longer than 200 chars in this column. The
SQL command then returns an error. Is there any option to let it accept the
text and truncate it to the max length supported by the column?

TIA,

Serge.

Reply to this Message

Ginny Caughey MVP - 23 May 2008 17:09 GMT

Serge,

You could either test the string you're inserting before the insert to see
if it's longer than 200 characters or put the insert in a try...catch block
and deal with the string in the recover block.

Ginny Caughey
Device Application Development MVP

> Env.: CF3.5, SQL CE 3.5
>
[quoted text clipped - 8 lines]
>
> Serge.

Reply to this Message

William Vaughn [MVP] - 23 May 2008 17:31 GMT

Or use the metadata to trim the string to the max length using Mid.

Signature

__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

> Serge,
>
[quoted text clipped - 18 lines]
>>
>> Serge.

Reply to this Message

Serge Wautier - 23 May 2008 17:48 GMT

Thanks Ginny,

I was hoping that the DB would allow me to be lazy and not do these checks
myself. The best part would have been forgiveness when I forget to do the
check.

Thanks anyway,

Serge.

> Serge,
>
[quoted text clipped - 18 lines]
>>
>> Serge.

Reply to this Message

Jin - 23 May 2008 17:55 GMT

> Env.: CF3.5, SQL CE 3.5
>
[quoted text clipped - 8 lines]
>
> Serge.

All good suggestions from other responders.
In addition, if the input value is coming from the user via the
TextBox control,
then setting the MaxLength property is a good way to prevent this
error too.

- Jin

Reply to this Message