Saving and restoring rich text to/from database

[fredw300]

Hello,

I am creating a winform app in C# to store formatted text from a rich text box to MySQL database, which can be retrieved back to the rich text box. The database field is a VARCHAR and my code is something similar to below. But I'm getting "file not in correct format" error. Can anyone tell what could be the problem? Is the VARCHAR field okay to store it or should I change it to a BLOB?

string rtfText = this.richTextBox1.Rtf;
// save rtfText to database field as varchar
// ...
// reload rtfText from database as string
this.richTextBox1.Rtf = rtfText;

[Momerath]

It's possible that some of the formatting codes are confusing MySql. You don't say where you get the error though.

Have you looked at what richTextBox1.Rft contains and compared it to what rtfText contains?

[fredw300]

Thanks for your reply. I am getting the error in the last line of code (this.richTextBox1.Rtf = rtfText).

I compared the strings ("test") and they are not the same.
Initially,
strRtf = {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}\viewkind4\uc1\pard\f0\fs17 test\par}

When I retrieve it from the database:
richTextBox1.Rtf = {tf1ansiansicpg1252deff0deflang1033{fonttbl{f0fnilfcharset0 Microsoft Sans Serif;}}viewkind4uc1pardf0fs17 testpar}

Also tried changing database field to VARCHAR CHARACTER SET utf-8, but did not make any difference.

[Momerath]

From what you've posted it appears that the \ is being stripped out of the text. Try changing this string rtfText = this.richTextBox1.Rtf; to this string rtfText = this.richTextBox1.Rtf.Replace(@"\", @"\\");

[fredw300]

I tried

string rtfText = this.richTextBox1.Rtf.Replace(@"\", @"\\")

and it worked!

When I try to save longer string though, I get database error saying "Data too long for column ...". I'm actually trying to load the rich text box with a .rtf file that may contain text, images etc so the string becomes big. Do I need to change the database field from VARCHAR to BLOB? I tried changing it to TEXT but got the same result.

[Momerath]

BLOB and TEXT store the same amount of data. BLOB just treats it as binary, while TEXT treats it as, you guessed it, TEXT. Both are limited to 65535 bytes (as is VARCHAR). I'd use the text variants (instead of the blob variants). MEDIUMTEXT gives you 16,777,215 characters while LONGTEXT gives you 4,294,967,295 characters. Replace the TEXT part with BLOB if you want to use the blob types.

[fredw300]

I changed the database field to LONGTEXT and it seems to be working fine. The only thing is that as the .rtf file becomes bigger (with more images), saving and restoring is taking too long. Any idea on how to make it more efficient?

[Mitja Bonca]

I`m afraid not. There is plenty of data to save. You can create a progressBar, which will show the progress of saving, and create a new thread which will do the saving (and restoring data back to richTextBox).
Btw: but even the type varchar should do it. Maybe you didn`t set the lenght long enough?

[Hak_1]

Hi, I have same problem in the past but to circumvent it I wrote a small procedure that remove the "/" from the data and replace it with another ASCII character and also create another procedure function that reconstruct it by inserting back the "/" here is my code in Vb.net hope this help you, also note you have to use longtext in mysql field

Public Function backlashInsert(ByVal mrtfdat As String) As String
'This insert baclash i.e dim mytext as string =backlashInsert("rtf \")
    'insert / into data to keep / in mysql record
    Dim n As Integer = mrtfdat.Length
    Dim nrtf As String = ""
    Dim mchrasc As Integer = 0
    For i As Integer = 0 To n - 1
        mchrasc = Asc(mrtfdat.Substring(i))
        If mchrasc = 92 Then
            nrtf = nrtf & Chr(42)
        Else
            nrtf = nrtf & Chr(mchrasc)
        End If
    Next
    Return nrtf
End Function

Public Function backlaskrep(ByVal mrtfdat As String) As String
    Dim n As Integer = mrtfdat.Length
    Dim nrtf As String = ""
    Dim mchrasc As Integer = 0
    For i As Integer = 0 To n - 1
        mchrasc = Asc(mrtfdat.Substring(i))
        If mchrasc = 42 Then
            nrtf = nrtf & Chr(92)
        Else
            nrtf = nrtf & Chr(mchrasc)
        End If
    Next
    Return nrtf
End Function