Handling Large data

i have a simple form which inserts data into database. now when user enters large data and the sql datatable datacoulm is defined to take less data it always throws an exception

System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

so what is the best way to handle the exception. i know i ca set the max length of textbox to match with the datacoumn size..is their any other way to do it..

hmm like catch teh exception and tell the user exactly which field is waytoo bigger than it needs to be..

or how do peple handle such an exception

Thank You
LVL 15
praneethaAsked:
Who is Participating?
 
Type25Connect With a Mentor Commented:
praneetha, i wouldn't recommend using text unless you really do need it, the data is stored on a seperate data page in sql server so it has to do more work to retreive it.

Custom Validator
-------------------

Drag one on your page, set it's control to validate to any text control you like and in it's ServerValidate event

ServerValidateEvent
------------------------

(psudo code)

if len textbox1.Text > 60)
    Page.IsValid = false;
    customValidator.ErrorMessage = "textbox 1 will be truncated";
else if  len textbox2 > 20)
   ... etc etc...


make sure in your final submit postback that you put:

if (Page.IsValid)
{
   // insert values into DB... etc
}
0
 
Type25Commented:
Personally i just set the maxlength of the textbox to the length of the column, however if it's a multiline textbox i usually setup a custom validator

0
 
praneethaAuthor Commented:
how do sql helper classes handle such stuff?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Type25Commented:
How do you mean?

They throw an exception and let you handle it.
0
 
praneethaAuthor Commented:
all the exception says is that SQL exception is thrown and it doenst really say which column it was...(does it?)

and is there way that i can just chop the data in stored procedure instead of letting it throw the exception...

well i understand it is better to od the max length way..but was just wondering..what happens in a case when i do not know the sql datacolumn size...

(did u mean sql helper classes throw an exception and wants us to handle it...do u know how to find out which column actually failed)
0
 
Type25Commented:
Unfortunately it's a general SqlException, afaik it doesn't tell you the failed column  (99% sure on that)

You can crop the string in your sp no probs, ie;

IF LEN(@yourstring) > columnLength) SET @yourstring = columnLength

If the columnLength is unknown then you'll need to return that from systables (?)

However, in my opinion i wouldn't want to go truncating the text automatically, not at least without telling the user.
0
 
praneethaAuthor Commented:
yep i understand that's a bad thing to do..just chop with out notification. i guess will go with custom validation for now...

and leave the question open in case anyone has better suggestions

thank you

0
 
nil1stCommented:
praneetah,
I agree with Type25, just limit your textbox to column length of database column.
but you can use try and catch statment as following to get around the issue you are having...

in the Catch block put the if statments saying that if the textbox input size is larger then what your database column size is then use the substring function to get however size you want
for instence
try{
  //your code goes here

}
catch(exception err){
   if (textbox.Text.Lenth > "some numbers"){
       textbox.Text.substring("starting point", "Length")
       do rest of the coding...

  }

}//end of try and catch stmt


Hope this helped

Nirav

0
 
Type25Commented:
To be fair you may as well use a custom validator instead of writing the code in your catch block.

That way you can wrap that up in a try catch
0
 
nil1stCommented:
Or batter yet just do the substring, so it would chop it off at the very first submission....

e.g. i am assuming you are using insert statment

inser into values(textbox1.text.substring(0,50), textbox2.text.substring(0,25));

that way you dont have to worry about catching it in to try and catch statment and user can put however many length of data yet it would only insert only 50 or 25 or whatever you assign in substring characters into the database...


Nirav
0
 
Type25Commented:
How would you inform the user the data was truncated?  :)
0
 
praneethaAuthor Commented:
can u give me an eg of how to use costom validator. thank you
0
 
nauman_ahmedCommented:
Is it possible to change the field type in your table to text or ntext.   You will not face problem of truncation after that.

Best, Nauman.
0
 
praneethaAuthor Commented:
well i guess i can..but the data is never really that large...and would that also mean changing the stored procedures to take text paramater? and if i understand it right would it not mean...just taking up large amount of memory on sql server bcz of text or ntext...
0
 
nauman_ahmedCommented:
What is the datatype in your case? char?

-Nauman.
0
 
praneethaAuthor Commented:
nvarchar
0
 
praneethaAuthor Commented:
HEY  why did u recommend server validate event instead of client validateevent..

i actually do nto see any server validate event but just clientvalidateevent in properties of custom validator
0
 
praneethaAuthor Commented:
in the validate function can i do this

e.ControlToValidate="txtComment1";
            e.ClientValidationFunction="CustomValidator_ClientValidate";
            

switch the controltovalidate to other textbox when one textbox is validated...instead of dragging and dropping as many custom validators as textboxes...
0
 
Type25Commented:
That's the thing you don't need to, as long as it has a controltovalidate set you can do what you like in one event.

I didn't necessarily recommend the server validate event, you can do it with JS if you wish but i usually can't be bothered and just do it server side :)


oh..and there is definiately a serverValidate event for the custom validator
0
 
Type25Commented:
Just make sure you're looking at the events view in the properties window
0
 
Type25Commented:
Anyhow, it's pub time for me!

Catch up tomorrow with this one.

Have a good one!

0
 
praneethaAuthor Commented:
yep got it. i was not looking at eevnts.

so anyways r u saying
>> That's the thing you don't need to, as long as it has a controltovalidate set you can do what you like in one event.

so say i need to validate textbox1,2,3,4

and i set control to validate to textbox1

but in the server validate event..

and i check the 2,3,4 length and display the error message...hmm but the errormessage will be displayed on the custom validator..like say i have it next to textbox1 ...even though textbox 2 fails..it would still display error next to textbox1 right?



0
 
Type25Commented:
>> I didn't necessarily recommend the server validate event, you can do it with JS if you wish but i usually can't be bothered and just do it server side :)

I can debug easier as well!  Which is nice when you're in a hurry most of the time.....as i often am as a contractor...

Right...really am going. See yas.
0
 
Type25Commented:
>> and i check the 2,3,4 length and display the error message...hmm but the errormessage will be displayed on the custom validator..like say i have it next to textbox1 ...even though textbox 2 fails..it would still display error next to textbox1 right?


Yes, but you can stick just standard asp.net labels next to the text boxes and set those:

if (textBox1.length > 50)
    label1.Text = "Error";

if (textBox2.length > 20)
   label2.Text = "Error";  

etc etc...
0
 
praneethaAuthor Commented:
Thank You
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.