Solved

Do .NET tableadapters truncate strings?

Posted on 2009-05-14
19
980 Views
Last Modified: 2013-11-07
Hiya,
I'm having trouble figuring out the following so would appreciate some help...
I have a TableAdapter set up that includes an insert query to insert a lot of text data into a field in my database.  However, it seems that the string is truncated to 1024 characters every time.  I've checked external factors (IIS, sceurity, etc) and ruled those out - not least because another program using the same services CAN post >1024 chars - so that leaves my code.

I've checked that the data is not truncated in my code (it's stored as a string) and it is not.
The field is set up as a system.string in the tableadapter with a max length of 2147483647
The field is set up in my DB as a text

Why would the data get truncated?  Is there an issue in .NET (v2 by the way) or in what I've done?  Any advice greatly appreciated...
0
Comment
Question by:markg28
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
19 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 24383398
Hi,

How do you see that text is 1024 chars? If it's in query analyzer/SSMS you may have to increase maximum chars displayed in a column or change to grid view which usually display more characters.

/peter
0
 

Author Comment

by:markg28
ID: 24383496
Hi Peter,
I use SQL Mgmt Studio (Express) to view the DB contents.  I've also just viewed the results using a new query window.
Interesting point though - hadn't considered that it could be the view...

Mike.
0
 

Author Comment

by:markg28
ID: 24383538
[edit] FWIW the field in the DB is actually 'ntext' not 'text'
0
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
LVL 22

Expert Comment

by:pivar
ID: 24383555
Choose Tools/Options in the menu. Then choose Query Results/SQL Server/Results to text, change the Maximum number of characters... as needed. Max is 8192 though.
0
 

Author Comment

by:markg28
ID: 24383749
Well... this made no difference :-) Not least becuase it was set to 256 and 1024 chars were still showing in my grid (which is set to 65535.

I will create a test program in ASP that will display the contents of the tbale - as that shouldn't be restricted to any characters surely?  That will confirm that it isn't in the display - as I'm pretty sure it isn't that unfortunately (would be an easy fix if it were).
0
 
LVL 22

Expert Comment

by:pivar
ID: 24383877
Yes, that could confirm it. Can you show us your insertioncode?
0
 

Author Comment

by:markg28
ID: 24384037
OK, test code show truncated text from Db, so safe to assume that it's stored that way.

Code as below - *valueIRV* is the field that's being truncated - as described above, it's set in my TableAdapter as a system.string with maxlength of 2147483647.  I haven't delved into the data layer designer.vb - should I?

Dim qIRVUpdate As New tdDALTableAdapters.Candidates3_IRVTableAdapter
                    Dim qIRVdata As tdDAL.Candidates3_IRVDataTable = qIRVUpdate.GetIRVData(CandBlindID, InstrumentID, iLang, CurrentSectionName)
qIRVUpdate.InsertIRVData(tdAccountID, CandBlindID, InstrumentID, iLang, CurrentSectionName, itemIRV, valueIRV, Now())

Open in new window

0
 
LVL 22

Expert Comment

by:pivar
ID: 24385944
I tried to mimic your code and it works well. Are you sure valueIRV is not truncated before InsertIRVData? Where does the text come from?
Also I do believe maxlength if you got ntext should be 1073741823.
How does the insertstatement look like in InsertIRVdata?
0
 

Author Comment

by:markg28
ID: 24386320
The text comes from the FORM:

Dim valueString As String = ""
valueString = Request.Form(aryItemText(i))  ' where aryItemText(i) is the name of the form element

I've verified that this is the full string by writing it to a session object, just before the insert command - which showed the full text.

The InsertIRVdata query is as follows:
INSERT INTO [Candidates3_IRV] ([AccountID], [CandBlindID], [InstrID], [Lang], [SectionName], [ItemIRV], [ValueIRV], [DateCompleted]) VALUES (?, ?, ?, ?, ?, ?, ?, ?)

Not sure about the maxlength of ntext - obviously it's all auto-created by .NET as the tableadapter was created.  

Thinking back, the DB field may not ahve been ntext historically and, although I've recrated the adapter since, could it be that it's still reverting back to the previous settings (despite showing a maxlength that should cope)?  Would this be in the designer.vb file?  Looked through and it seems to be set as per shown, but you never know...
0
 

Author Comment

by:markg28
ID: 24395661
[bump]
Can anyone help me please?!?!
0
 
LVL 1

Expert Comment

by:PUBAND
ID: 24396821
Are you sure it is not complete in the DB?  Try something like this:
declare @mytext varcar(5000)
select @mytext = textcol from yourtable
print @mytext
0
 

Author Comment

by:markg28
ID: 24397244
Quite sure.  Just ran the SQL you suggested and got the text truncated at 1024 again.  Did the same by creating a test ASP page to output the DB contents also.
It must be how the data is inserted, I just can't figure out why it would truncate it...
0
 
LVL 22

Expert Comment

by:pivar
ID: 24401569
Have you tried changing the maxlength? I'm a C# guy so I'm not familiar with VB but you should be able to change that in designer?
0
 

Author Comment

by:markg28
ID: 24402461
Cheers - I guess I could change the maxlength - hadn't really thought as it was showing that it was plenty big enough.  Of course, that's assuming there isn't something funny going on - will give it a go on Monday and report back - much appreciated.
0
 

Author Comment

by:markg28
ID: 24409932
Well... reporting back - changed the maxlength to 1,999,999,999 and still have same issue of data string being truncated at 1024chars.  Verified by both DB check and also through ASP (not ASP.NEt in case the issue is specific to .NET) code to connect to DB and show contents.

Hmm.  So, I guess what you're saying is that there isn't a known limit and there shouldn't be a problem posting this much data?

Will rebuild the table adapter and see if this helps, although looking through the designr.vb file, it all seems to be defined correctly.

Feel free to supply any futher top tips!!!  Thanks.
0
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 24410002
Try 1073741823, since ntext/nchar uses 2 bytes per char compared to text/char which uses 1 byte and 2147483647(maximum for text)/2=1073741823. This is a longshot, but worth trying I believe. No there should not be a maximum of 1024 chars för ntext. Otherwise rebuilding the adapter from scratch would probably be easier compared to search for this type error.
0
 

Author Comment

by:markg28
ID: 24410117
Nope, same again; truncated.
OK, will get on and rebuild the adapter - cheers
0
 

Author Comment

by:markg28
ID: 24410550
Whoo-hoo, it works!!!  Rebuilding the tableadapter did it - it may have taken a while, but the definition must have been all screwy in my .xsd.  Many thanks for your help everyone, very much appreciated indeed!
0
 

Author Closing Comment

by:markg28
ID: 31581374
Thank you for hanging in there with me!!!
0

Featured Post

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!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 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