SQL Server 2000 Text field length limitation

As Aneesh has pointed out text supports up to 2GB, so it must be a limitation of your VB6 app.

Unrelated to your question, but since you are now on SQL Server 2005 you may want to consider changing the data type to varchar(MAX).

@Antony

I think u miss the title :) "SQL Server 2000 Text field length limitation"

Oops, I did.  I just saw the MS SQL Server 2005 zone.

Thank you for the reply on the text field limitation. I never knew the actual number but now I do. Still have a question though. Is that size in reference to the number of characters LENGTH or the file size?

Follow on then: I checked the length of the text by using VB6 immediate window by doing a len(field).  

Whenever I add text (by direct insertion into the field), the text at the end of field is truncated and the length remains exactly at 65535. For that reason I was assuming that the field was doing the dirty work and not the text box that displays the data. FYI: the field is NOT bound to the table. When it is time to display the data, it is read from the field and displayed in the text box.

>>Is that size in reference to the number of characters LENGTH or the file size?<<
A text column can store up to 2GB (or 2,147,483,647) characters.  I am not sure what you mean by "file size".

In order to get the true length of the column and not the one you are seeing in the VB6 immediate column do this:
Select DATALENGTH(YourTextColumnGoesHere)
From YourTableNameGoesHere
Where SomeConditionGoesHere

I tried the code above and it returned 65535 in length. I tried adding additional text and the added text is truncated.

Yikes

So it sounds like you have a VB6 problem.  Can you post your VB6 code and I will see if I can figure it out?

**************THIS CODE FINDS THE CORRECT RECORD
 Dim db As Connection
    Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open PreQual.dbLocation
    Set adoPrimaryRS = New Recordset
    adoPrimaryRS.Open "select * from cm_contacts where contact_id = '" & Text1(4) & "'", db, adOpenStatic, adLockOptimistic
 
  ' Call GetTimeElapsed ''get the time in the file
   If adoPrimaryRS.RecordCount = 0 Then
    MsgBox "That contact does not exist.", vbInformation, "System Message"
    Exit Sub
    End If
       
    Call BindFields''BINDS THE CONTROLS TO THE DATASET


********THIS PROCEDURE RUNDS FROM A COMMAND BUTTON THAT APPENDS A TEMP FIELD WHERE THE USER HAS EDITED THE DATA AND USED A SPELL CHECKER AND THEN IT INSERTS IT INTO THE TABLE-I DID IT THIS WAY TO AVOID HAVING TO ADD THE TEXT TO A CONTROL ETC.
If Len(txtFields(1).Text) < 12 Then
        txtFields(1).Text = PreQual.mUsername & "-" & Now & vbCrLf & Tempnotes.Text & vbCrLf
        Tempnotes.Text = ""
        adoPrimaryRS.Fields("comment") = txtFields(1).Text
        adoPrimaryRS.Update
        Me.MousePointer = 0
        Exit Sub
    End If
   
    If Len(txtFields(1).Text) > 11 Then
        txtFields(1).Text = vbCrLf & PreQual.mUsername & "-" & Now & vbCrLf & Tempnotes.Text & vbCrLf & txtFields(1).Text & vbCrLf & vbCrLf
        Tempnotes.Text = ""
        adoPrimaryRS.Fields("comment") = txtFields(1).Text
        adoPrimaryRS.Update
    End If

This shows the results from the datalength query

Please look at the 'comment' field. That is the field that is being truncated.

It seems that this is a limitation of the TextBox control.  From the Help:

"The Text setting for a TextBox control is limited to 2048 characters unless the MultiLine property is True, in which case the limit is about 32K."

Wow-good find

What other control could I use to display the data without a limitation. They are all set to multi line. I wonder why I get the 65535 size consistantly though?

Thanks for the quick interchange.

I'll fix it!

Your solution is to switch to the RichText Control see here:
Visual Basic: RichTextBox Control
http://msdn.microsoft.com/en-us/library/aa261653(VS.60).aspx

And in particular this line is pertinent:
However, the RichTextBox control doesn't have the same 64K character capacity limit of the conventional TextBox control.