?
Solved

Handling Large data

Posted on 2005-04-06
26
Medium Priority
?
155 Views
Last Modified: 2010-04-07
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
0
Comment
Question by:praneetha
  • 11
  • 10
  • 2
  • +1
25 Comments
 
LVL 9

Expert Comment

by:Type25
ID: 13717350
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
 
LVL 15

Author Comment

by:praneetha
ID: 13717353
how do sql helper classes handle such stuff?
0
 
LVL 9

Expert Comment

by:Type25
ID: 13717391
How do you mean?

They throw an exception and let you handle it.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Author Comment

by:praneetha
ID: 13717427
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
 
LVL 9

Expert Comment

by:Type25
ID: 13717510
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
 
LVL 15

Author Comment

by:praneetha
ID: 13717581
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
 
LVL 1

Expert Comment

by:nil1st
ID: 13717697
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
 
LVL 9

Expert Comment

by:Type25
ID: 13717767
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
 
LVL 1

Expert Comment

by:nil1st
ID: 13717783
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
 
LVL 9

Expert Comment

by:Type25
ID: 13717854
How would you inform the user the data was truncated?  :)
0
 
LVL 15

Author Comment

by:praneetha
ID: 13717936
can u give me an eg of how to use costom validator. thank you
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 13717942
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
 
LVL 15

Author Comment

by:praneetha
ID: 13717977
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 13718035
What is the datatype in your case? char?

-Nauman.
0
 
LVL 15

Author Comment

by:praneetha
ID: 13718087
nvarchar
0
 
LVL 9

Accepted Solution

by:
Type25 earned 2000 total points
ID: 13718091
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
 
LVL 15

Author Comment

by:praneetha
ID: 13718115
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
 
LVL 15

Author Comment

by:praneetha
ID: 13718137
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
 
LVL 9

Expert Comment

by:Type25
ID: 13718184
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
 
LVL 9

Expert Comment

by:Type25
ID: 13718191
Just make sure you're looking at the events view in the properties window
0
 
LVL 9

Expert Comment

by:Type25
ID: 13718243
Anyhow, it's pub time for me!

Catch up tomorrow with this one.

Have a good one!

0
 
LVL 15

Author Comment

by:praneetha
ID: 13718259
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
 
LVL 9

Expert Comment

by:Type25
ID: 13718267
>> 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
 
LVL 9

Expert Comment

by:Type25
ID: 13718295
>> 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
 
LVL 15

Author Comment

by:praneetha
ID: 13909597
Thank You
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question