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

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?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
jonesgjAuthor Commented:
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


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.

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

jonesgjAuthor Commented:
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)

jonesgjAuthor Commented:
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.

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".


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jonesgjAuthor Commented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.