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
sammySeltzerAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft 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.
0
 
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 
  begin
  SELECT 'Hey Alphabet, why's your email address so frEEaking long?'
  -- Figure out how to gracefully prevent the INSERT from happening here
  end

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.
1
 
OMC2000Connect With a Mentor Commented:
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
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Martin MillerConnect With a Mentor CTOCommented:
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 ?
0
 
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.
0
 
sammySeltzerAuthor Commented:
Thanks all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.