RichText To Database

Hey everyone!

I am aware that you can save the value of a RichText Textbox to a database. However I have an insert statement such as:

CMD = "INSERT INTO TBL_ACTION (RTF) VALUES ('" & RichTextBox1.Rtf & "')"

The string always fails and I don't know why! This is urgent your help is much appreciated.
LVL 2
juravichAsked:
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.

tpwellsCommented:
use parameters in a sqlCommand

Dim cm as SqlCommand = new SqlCommand()
cm.Connection = YourConnection
cm.CommadText="INSERT INTO TBL_ACTION (RTF) VALUES (@richText)"
cm.Parameters.Add("richText",RichTextBox1.Rft);
cm.ExecuteNonQuery()
tpwellsCommented:
Forgot to explain: the problem has most likely to do with non-sql friendly text in the rtf string causeing a Sql parsing error. The parameter method takes care of properly parsing the data to be save correctly.
juravichAuthor Commented:
I guess I'm just really bad at commands, because I can't get this to work what does the @richText mean?

Thanks
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

tpwellsCommented:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclassparameterstopic.asp

The @richText is a signal to the SqlCommand's Parameters to insert the parameter with the name richText there in the query.

What is the error your code is generating?
angelfeijooCommented:
I do it using a dataset without any special consideration:

            daSave = New OleDb.OleDbDataAdapter("SELECT * FROM ENTRIES", OleConn)
            daSave.FillSchema(dsData, SchemaType.Mapped, "TEMPSAVE")

            drNew = dsData.Tables("TEMPSAVE").NewRow
            drNew("EN_Content") = rtxRichText.Rtf
            '...
            dsData.Tables("TEMPSAVE").Rows.Add(drNew)
            daSave.Update(dsData, "TEMPSAVE")
juravichAuthor Commented:
I get ORA-00936: Missing Expression

With The Code....



Dim oledbconnectionCC1 = New OleDbConnection

        oledbconnectionCC1.ConnectionString = _
                 "Provider=OraOLEDB.Oracle;" & _
                 "Data Source=****;" & _
                 "User ID=***;" & _
                 "Password=****r"
        Try
            Dim CMD As String


            Dim cm As OleDbCommand = New OleDbCommand
            cm.Connection = oledbconnectionCC1
            cm.CommandText = "INSERT INTO TBL_ACTION (RTF) VALUES (@richText)"
            cm.Parameters.Add("richText", RichTextBox1.Rtf)


            oledbconnectionCC1.Open()
            'Update the row
            cm.ExecuteNonQuery()
            oledbconnectionCC1.close()


            'Open The Database

        Catch EX As Exception
            MessageBox.Show(EX.ToString())
        End Try
tpwellsCommented:
ok i looked at the msdn again and looks like this line:

cm.Parameters.Add("richText", RichTextBox1.Rtf)

should be

cm.Parameters.Add("@richText", RichTextBox1.Rtf)
angelfeijooCommented:
Juravich,

Have you tried using a dataset as I suggested above? I save my RTF that way and it just works!
juravichAuthor Commented:
Thanks tpwells, but unfortunately it still doesn't work, any other ideas?

Angelfeijoo, I'm trying your dataset too, I'll let you know what i find out
tpwellsCommented:
here's my last idea looking at msdn for OleDB and it says the OleDB uses ? instead of named parameters

which would mean changing these two lines

  cm.CommandText = "INSERT INTO TBL_ACTION (RTF) VALUES (@richText)"
  cm.Parameters.Add("richText", RichTextBox1.Rtf)

To

  cm.CommandText = "INSERT INTO TBL_ACTION (RTF) VALUES (?)"
  cm.Parameters.Add(RichTextBox1.Rtf)

here is where I got that data:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbCommandPropertiesTopic.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbparameterclasstopic.asp

If this doesn't do it then I am at a loss.

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
juravichAuthor Commented:
Genius!

Thank you very much for all of your help, you truly deserve the points for your response time.

This is the final code that I got to work....

Dim oledbconnectionCC1 = New OleDbConnection

        oledbconnectionCC1.ConnectionString = _
                 "Provider=OraOLEDB.Oracle;" & _
                 "Data Source=****;" & _
                 "User ID=****;" & _
                 "Password=****"
        Try

            Dim cm As OleDbCommand = New OleDbCommand
            cm.Connection = oledbconnectionCC1
            cm.CommandText = "INSERT INTO TBL_ACTION (RTF) VALUES (?)"
            cm.Parameters.Add("?", RichTextBox1.Rtf)


            oledbconnectionCC1.Open()
            cm.ExecuteNonQuery()
            oledbconnectionCC1.close()

        Catch EX As Exception
            MessageBox.Show(EX.ToString())
        End Try
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.