How to prevent user getting character truncation error??

Greetings mates,

We have a couple of field names on the DB with size of varchar(50).

Those fields are fullname and emailAddress

It turns out that some users have names with values longer than size 50.

Same with emailAddress.

So, when some users with names or email addresses larger than 50, they get truncation error.

Can you please recommend the best solution for this?

For starters, I increased the fieldname size to 100 each.

However, management thinks more needs to be done to prevent user from seeing that error message, like give user custom message.

Any thoughts if she is right?

If yes, any ideas how to proceed?
LVL 29
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>For starters, I increased the fieldname size to 100 each.
Correct, you'll have to increase the length to the maximum value one would expect.

>like give user custom message.
Microsoft doesn't 'do' custom error messages, but you can validate it in your front-end application like this...

IF LEN(@EmailAddress) > 100 
  SELECT 'Hey Alphabet, why's your email address so frEEaking long?'
  -- Figure out how to gracefully prevent the INSERT from happening here

Open in new window

Keep in mind that validations are a normal part of building data entry forms anyways, to make sure somebody enters a reasonable value, and oh I dunno maybe protect from a SQL injection attack.
you could just set maxlength attribute value = 100 for you input fields in the web form and user won't be able to enter longer value
Martin MillerCTOCommented:
How about USER INPUT validation, this can be done in Javascript on a front-end/browser application to message user for limits. Opening up beyond 50 characters is OK, but, what happens when you don't validate the user input data, and they give you 102 characters ?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

sammySeltzerAuthor Commented:
Thanks guys for all your responses.

Reason I was doubtful about using client side validation is that if your name, for instance is longer than say 50 characters (DB size), do you force them to shorten their name?

That's really issue I have with client side validation.

I just thought that increasing fieldname name size to 1000 is better than forcing them to shorten their names or email address.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Last I vaguely remember there's a standard somewhere that email address can't be greater than 255 characters.   Not sure where that's at, and if you're curious enough ask that question in the Outlook or Exchange zones and you'll probably get the answer within five minutes.

>for instance is longer than say 50 characters (DB size), do you force them to shorten their name?
Can't really help you there as that's more of a customer relations decision.  I remember a news story five years ago that some Hawaiian had a last name more than 36 letters and their state DMV couldn't issue them a driver's license because their system wouldn't accept it.  The impacts of excessive length in varchar columns is somewhat minimal, so unless we're talking a huge dataset I don't think having a last name of varchar(255) would be a problem.  Have fun with that one.

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
sammySeltzerAuthor Commented:
Thanks all
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

From novice to tech pro — start learning today.