TextArea Data Truncated on Database Save

I have a TextArea into which the user pastes formatted text (RTF) which was copied from some source (e.g., WordPad). The TextArea’s LimitText parameter is set to 0 (no limit on number of characters inputted). The resultant data is placed in a string and used to update a MS SQL Server database table (using prepared statements) whose associated field is set to NVARCHAR(MAX) data type. I have been using this successfully for some days now without any problems. But today I noticed something strange when I tried it with a “larger” amount of data than usual. The update operation executed without any db errors yet saved nothing … nor did it affect the data that was there in the database previously in any way (when I closed an reopened the program, the same data was there as was before I tried saving the “larger” amount). Playing around with a few things to see what was going on, I found that when the string that contains the TextArea’s content exceeded 2000 characters (RTF overhead and all), the problem occurs. If I back the content down until the string shows something less than 2000 characters, the problem does not exist and the TextArea’s content is saved properly in its entirety. Since the string I’m using to see what the TextArea’s content was DOES contain the entire amount of text it should, regardless of how big the amount is, I know it’s NOT the TextArea limiting the process here.

Something somewhere else seems to be “limiting” the size of what can be saved. I tried TEXT and VARCHAR(MAX) for database table field data types (from what I read researching this, both of which theoretically have greater storage capabilities than NVARCHAR(MAX) … I read somewhere that NVARCHAR(MAX) is actually limited to 4000 characters) but it made no difference.

What could be causing the limit to occur??? 2000 characters ain’t much to choke on!

Here’s the code I’m using to do the save of the TextArea’s contents:

[code] dim ss as string

// save the NOTES section
for iloop As Integer = 0 to Ubound(oContainers)

if oContainerIndex(iloop) = "nc" then
  
  ss = cntNotes(oContainers(iloop)).TANotes.WinRTFDataMBS
  
  Exit For
end if

next

MsgBox (str(ss.Len) + EndOfLine + ss) // USE THIS TO SEE THE SIZE OF THE STRING AND THE CONTENTS IT GOT FROM THE TEXT AREA

// get the caseID of the current record
Dim rs As RecordSet = db.SQLSelect(“SELECT CaseID FROM caseModel WHERE (CaseName=’” + caseName + “’) AND (Revision=’” + caseRevision + “’)”)
Dim cIDnum As Integer = rs.Field(“CaseID”).IntegerValue
rs.close

// save the Notes
Dim sqln As String = _
"UPDATE Notes " + _
" SET NotesData = :nts " + _
" WHERE CaseID = :cID; "

dim vn as Variant = db.Prepare(sqln)

If db.Error Then

// open Modal MsgBox window
taMessage = "UPDATE Model General Record Set  PREPARE NOTES Data DB Error: " + db.ErrorMessage
wndModalMsgBox.ShowModal

End If

dim pn as SQLPreparedStatementMBS = vn

pn.BindType(“nts”, SQLPreparedStatementMBS.kTypeString)
pn.BindType(“cID”, SQLPreparedStatementMBS.kTypeLong)

pn.Bind(“nts”, ss)
pn.Bind(“cID”, cIDnum)

pn.SQLExecute

If db.Error Then

// open Modal MsgBox window
taMessage = "UPDATE Model General Record Set SAVE NOTES  Data DB Error: " + db.ErrorMessage
wndModalMsgBox.ShowModal

else
db.Commit // Commit the recordset to the database
End If

[/code]

Others will authoritatively be able to answer about the size limit. One way of going around it when it exceeds the limit could be to save the text in a separate file and store its path in the database with a token you can recognize as a marker for an external storage.

Thanks for responding, Michel. Your time is always appreciated! Your idea is somewhat how I do pictures right now in the same database, but I was hoping I wouldn’t have to resort to another “pointer” technique for anything else … the maintenance of all those “separate” files can be a real headache … in theory, having “everything” (content-wise) in the same database is the reason I’m using a db to begin with.

The more reading I do here, the more I am amazed at how many different size limitations come into play with SQL Server database (db page, field data types, etc.). NVARCHAR(MAX) is not really a “MAX” at all when it comes right down to it ^^

How do other people out there store large text blocks (>4000 characters) in their databases? I would have to believe that’s something that’s a pretty common, everyday thing.

From MS DN which documents nvarchar
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

Nvarchar uses UCS 2 (2 bytes per character) - hence the 2x requirement
So 2000 makes sense

Try NTEXT

Thanks, Norm. Unfortunately, no luck trying NTEXT either. I tried to save what you see pasted below (sans formatting here due to plain text display). According to the MsgBox I inserted, it indicated the total length of the string containing the RTF to save this was roughly 2200 characters. Again, no error messages and when you look at the involved database table field with SSMS, the value hasn’t changed (was NULL to begin with and is is NULL).

I have a bit of trouble grasping the relationship between 2GB and 4,000 … let alone 2,200

[quote]For meat application: extended front sills are required for maintaining proper case temperature levels (5" baseframe = 24" or higher; 11" baseframe = 30" or higher)
BTUH/ft notes:
Listed BTUH/ft indicate unlighted shelves. For LED lighting, add 36 BTUH per 4’ lighted shelf and 27 BTUH per 3’ lighted shelf to determine Total Lighting BTUH Load, then divide the Total Lighting BTUH Load by the length of the case. For T8 lighted shelves (see Appendix D) and 3rd row lighting, add 92 BTUH per 4’ lighted shelf and 69 BTUH per 3’ lighted shelf to determine Total Lighting BTUH Load, then divide the Total Lighting BTUH Load by the length of the case.
Rear refrigerated box increases refrigeration load by 110 BTUH/ft.
Add 132 BTUH/ft when aftermarket merchandising accessories are utilized to determine the total BTUH load.
Listed evaporator temperature indicates unlighted shelves. For lighted shelves and/or aftermarket merchandising accessories, reduce the listed evaporator temperature by 2°F.
Conventional Discharge Air Control – Recommended Settings: Cut-In Temp = Discharge Air + 2°F; Cut-Out Temp = Discharge Air - 2°F
Average discharge air velocity at peak of defrost.
NOTE: “- - -” indicates that feature is not an option on this case model.[/quote]