Issue with inserting text in to ntext field in db.

Question

Issue with inserting text in to ntext field in db.

samir shah-143616

SSC Veteran

Points: 206
More actions
October 31, 2006 at 10:10 am

#116166

Hi all,
I am trying to insert some data from a text box in my vb.net code to mysql server database. The field on the database is declared as ntext. When I try to insert more characters (like in the range of 7500 characters including blank spaces) , I see that they get truncated?
Does any one have a solution as to how I can store more than 8000 characters in a database field ?
thanks
nanku

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

do you mean when you view it in Query analyzer?

QA is limited to displaying 8000 chars or 4000 nchars for a column value; so if you do "SELECT someNTextValue from YourTable", QA can only display the first 4000 chars; however, if you use something like the ADODB.recordset or a .net datatable, then when you print/render/display, the entire value will be available.

there's no practical limit to the size of a text or ntext field, but there might be limitations with the tool you display it with.

also, if you are using command objects or anything, make sure your parameter is of type text and not of adVarChar or anything,a s that would truncate the data at insertion.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vijay ram SSChasing Mays Points: 628 More actions · Answer 2

Hi

There is a limitation to the maximum size of a record in SQL Server 2000.

the combined size of all the columns in a row can be 8060 bytes(max)--refer MSDN. this is because the default size of a page is 8Kb and once that limit is reached the page overflow will occur. this is not supported in SQL Server 2000. but in SQL Server 2005 this is automatically handled and a new page will be created for the excess data.

-Vijay

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Vijay, you don't know what you are talking about.

Text field takes 16 bytes in every row.

Text itself is not stored in the table pages. There is only pointer to the actual text storage.

SQL Server handles text strings up to 2GB each without any problem.

Samir must have it truncated because of the way he stores text.

There are many pitfalls when text is being implicitly converted to varchar(8000).

Must take care of it.

_____________
Code for TallyGenerator

vijay ram SSChasing Mays Points: 628 More actions · Answer 4

Sergiy, thanks for correcting me....I didnt notice the datatype is ntext and I was talking about varchar.

sry folks, I havent looked it carefully..will be more careful from now on.

-Vijay