Store and retrieve Rich text to and from an access database.

Posted on 2006-04-14
Last Modified: 2009-07-15
I need to store and retrieve Rich text from a rich text box control to and from an access database.

I am using VB .NET 2003, Access 2002 (also looking at VB2005)

I run into problems when I try to use an SQL INSERT statement to take the content of the RTB into the column. I have code which strips out characters such as commas and this works to store plain text. Obviously, with Rich text, there are even more control/formatting characters.

Is there an easy soluton? Is it possible?


Question by:jonesgj
    LVL 5

    Expert Comment

    I don't guess I am understanding the whole problem.  Are you trying to lose the RTF? and ONLY store plain text?
    if that is what you want, why not build a regex to remove all formating other than plain text, and store the new string

    if you want to save the RTF, just save the string value of the RTbox into the db

    Author Comment

    Hi razor,

    I am trying to save the text and its formating. Up til now I have just been required to store the simple plain text held in my text box. Now, however, I need to use a rich text box instead of a text box because I need to apply some kind of formatting to the text. This formatting needs to be retained and held in the database, and recalled from the database into a rich text box when needed formatting intact.

    Sending the RTB.rtf to the db fails.

    Thanks in anticipation


    LVL 34

    Expert Comment

    Sending the RTB.rtf to the db fails.

    It works OK for me, with a relatively simple databinding, provided the set-up is right.

    1)      You will probably need to use a Memo field in the Access Table, because the text that does the formatting takes up a lot of space.

    2)      The databinding (which has to be done in code, because the .rtf property doesn't appear in the "Advanced bindings" drop down in the RichTextBox's property window) should be on the following lines

            rtb1.DataBindings.Add("rtf", ds.Tables(0), "MyText")

    3)      The biggest problem is likely to be that for this approach to work the relevant field's data in the Access Table has ALREADY to be in rtf format.  That is, if you want to bind to the rtb's .rtf property, you can't start off with plain text in the Access Table and then, via the binding, save it in .rtf format.  But what you could do, as a one-off initial exercise to get the Access Table into this state, is fill your VB.NET datatable with plain text from Access then cycle through the rows loading the relevant field into the rtb as .Text and then resave to the datatable as .rtf, and then update the Access Table.  If you do that - I probably don't need to say this ;-) - you ought to backup the Access Table first, in case anything goes wrong.


    Author Comment

    Thanks Roger,

    I am using the following:

                                MySQL = ("UPDATE Content SET " & _
                                 "ContentName = '" & txtName.Text & "', " & _
                                 "ContentText = '" & rtbNotes.Rtf & "' " & _
                                 "WHERE ContentName = '" & AppSettings.CurrentItem & "' ")

                                rtbNotes.Text = Replace(rtbNotes.Text, Chr(124), Chr(34))
                                rtbNotes.Text = Replace(rtbNotes.Text, Chr(42), Chr(39))
                                rtbNotes.Text = Replace(rtbNotes.Text, Chr(17), Chr(43))

                                Call ExecuteQuery(MySQL)


    Author Comment

    Sorry the code above needs a little explanation.

    This returns an error 'syntax error in SQL' because of characters in the rtf.

    Ignore the replace function lines, as they are just putting back the commas removed before the value of rtb was passed to the SQL.

    My thoughts were to, somehow, substitute all the characters (may be to their ascii values) with values which would not split the SQL, before passing it to the insert statement, so the SQL string would be 'clean'.

    Before attempting this, I was looking for an easier way to do it.

    I am not binding any of my controls.

    LVL 34

    Accepted Solution

    I'd never tried it with coding my own SQL statement for ExecuteNonQuery, so what follows is the result of a bit of experimentation rather than any long-standing experience.  But I've got your approach working by making two adjustments.

    First, the rtb's .rtf property appears to return a null-terminated string.  In terms of SQL syntax, that will stop the parser reading anything subsequently.  So it needs stripping off.  Second, any single quote or apostrophe - that is, Chr(44) - within the .rtf string will be regarded by the SQL parser as a delimiter.  So it needs "escaping" - that is, replacing by double single quotes.  So, my successful code looks like this

            Dim myTextToStore As String = rtb1.Rtf
            'remove final - null - character
            myTextToStore = myTextToStore.Substring(0, myTextToStore.Length - 1)
            'replace single quotes by double single quotes
            myTextToStore = myTextToStore.Replace("'", "''")
            Dim MySQL As String = "UPDATE MyRTF SET MyText = '" & myTextToStore & "' WHERE ID = " & myID
            Dim comm As New OleDb.OleDbCommand
            comm.Connection = OleDbConnection1
            comm.CommandText = MySQL

    That's just copied and pasted from my trial project, making use of my existing Access table and its data, so the detail of the SQL statement is different from yours.  But the manipulation of the .rtf to avoid hiccups in the SQL parsing should be similar for you.

    I've tried it with all sorts of characters - e.g. {, }, \, ", etc. - and not found any others that give problems but, as I say, this is just an empirical stab at the issue rather than any real "expertise".


    Author Comment

    Hi Roger,

    You code is what I started with. A simple Insert with one exception: I didn't know about the rtb's .rtf property returning a null-terminated string.

    I have placed your code into my project with 1 or 2 tweaks and it works!

    I had spent 45minutes this afternoon creating an encode and decode ASCII to RTF, just before your comment appeared, and that seemed to work, but was quite slow.

    Anyway. Thanks for the solution

    Kind regards


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now