RTF saving into SQL

Adam Maltby · Oct 18, 2004

Hi,

In vb.net I am try to save an rtf field to a database field using an sql string:

GetSQL = "UPDATE TestsConfig " & _
"SET ReadMe = '" & Rtxt_readme.Rtf & "' "
"WHERE Type = '" & TestSelected & "'"

I seem to be hitting a probelm when writing the rtf to the db column.
During the write to the database it picks up some of the single quotes used by the rtf formatting and seems to be incorrectly assuming they are an end of string (since when writing a string back to a column in a DB it uses single quotes to mark out the string to write).

Any ideas how I can get round this?

Cheers
Adam

Nak · Oct 18, 2004

Hi there,

I recommend putting it into a byte array and then storing the byte array
in the database. Also you will need to use the command parameters instead
of constructing the command all in a string as you are doing there.

"See OleDbCommand.Parameters or OdbcCommand.Parameters (depending on
what you're using) for more information."

^This is what I was told recently.

BTW, you might want to try another newsgroup more suited to your ADO.NET
questions,

microsoft.public.dotnet.framework.adonet

Nick.

Hi,

In vb.net I am try to save an rtf field to a database field using an sql
string:

GetSQL = "UPDATE TestsConfig " & _
"SET ReadMe = '" & Rtxt_readme.Rtf & "' "
"WHERE Type = '" & TestSelected & "'"

I seem to be hitting a probelm when writing the rtf to the db column.
During the write to the database it picks up some of the single quotes used
by the rtf formatting and seems to be incorrectly assuming they are an end
of string (since when writing a string back to a column in a DB it uses
single quotes to mark out the string to write).

Any ideas how I can get round this?

Cheers
Adam

Cor Ligthert · Oct 19, 2004

Nick,

BTW, you might want to try another newsgroup more suited to your ADO.NET
questions,

Yes than I can do there those typical questions which I do there. However
have than have to add that the samples are made in VBNet code.

However for the rest was your answer in my opinion very right, when you than
compress it than before it is in my opinion even better.

:-)

))

Cor

Nak · Oct 19, 2004

Hi Cor,

However for the rest was your answer in my opinion very right, when you
than compress it than before it is in my opinion even better.

LOL, your one of the people that answered me, so it's your answer
really! :-)

Nick.

Adam Maltby · Oct 21, 2004

Apologies for delay in replying......
How can I do that using ADODB?
All the examples I have found use SQLClient and don't look anything like how I am retrieving and updating my sql data columns?

Thanks
Adam

Nak · Oct 21, 2004

Hi there,

Here's an example,

Dim pBytStuff as Byte() = {70,69,75}

Dim pDBNConnection As OleDbConnection
Dim pDBCCommand As OleDbCommand
pDBNConnection = New OleDbConnection(OLEHelpers.connectionString(iFileName))
Call pDBNConnection.Open()
If (pDBNConnection.State = ConnectionState.Open) Then
pDBCCommand = pDBNConnection.CreateCommand
pDBCCommand.CommandText = "INSERT INTO table (data) values(?)"
pDBCCommand.CommandTimeout = 20
Call addParameter(pDBCCommand, "data", pBytStuff )
If (pDBCCommand.ExecuteNonQuery() > 0) Then
'OPERATION WAS FINE!
End If
End If

Public Function getOleDbType(ByVal iType As Type) As OleDbType
If (iType Is GetType(String)) Then
Return OleDbType.VarChar
ElseIf (iType Is GetType(Integer)) Then
Return OleDbType.Integer
ElseIf (iType Is GetType(Boolean)) Then
Return OleDbType.Boolean
ElseIf (iType Is GetType(Date)) Then
Return OleDbType.Date
ElseIf (iType Is GetType(Char)) Then
Return OleDbType.Char
ElseIf (iType Is GetType(Decimal)) Then
Return OleDbType.Decimal
ElseIf (iType Is GetType(Double)) Then
Return OleDbType.Double
ElseIf (iType Is GetType(Single)) Then
Return OleDbType.Single
ElseIf (iType Is GetType(Byte())) Then
Return OleDbType.Binary
ElseIf (iType Is GetType(Guid)) Then
Return OleDbType.Guid
End If
End Function

Public Sub addParameter(ByVal iCommand As OleDbCommand, ByVal iParameter As
String, ByVal iValue As Object)
If (iCommand.CommandText.IndexOf(iParameter) <> -1) Then
Dim pODPParameter As OleDbParameter =
iCommand.Parameters.Add(iParameter, getOleDbType(iValue.GetType))
pODPParameter.Value = iValue
End If
End Sub

The 2 methods are copied and pasted from a working application I have,
the top code snipette is taken from a working method and modified in here,
so it lacks error catching. Anyway, the byte array is stored in pBytStuff,
so that is what you would put your RTF data into. Hope this helps.

Nick.

Apologies for delay in replying......
How can I do that using ADODB?
All the examples I have found use SQLClient and don't look anything like how
I am retrieving and updating my sql data columns?

Thanks
Adam

Adam Maltby · Oct 22, 2004

Hi,

Thanks for the code.....

I pasted it in and made a few changes to suit my app (see below) which include a slight mod to the connection to get a udl and the command text to do an update instead of insert + it's param, but when run it, when i get to 'If (pDBCCommand.ExecuteNonQuery() > 0) Then' I get :
'An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll'

Any ideas would be most appreciated!

Mod'd Code:
Dim pBytStuff As Byte() = StringToArray(Rtxt_readme.Rtf) 'convert rtf field to byte array

Dim pDBNConnection As OleDbConnection
Dim pDBCCommand As OleDbCommand
pDBNConnection = New OleDbConnection
pDBNConnection.ConnectionString = Globals.udl0Val 'takes con from Globals Class which returns a udl file
Call pDBNConnection.Open()
If (pDBNConnection.State = ConnectionState.Open) Then
pDBCCommand = pDBNConnection.CreateCommand
pDBCCommand.CommandText = "UPDATE TestsConfig Set ReadMe = (bits) Where Type = '" & _
TestSelected & "'"
pDBCCommand.CommandTimeout = 20
Call addParameter(pDBCCommand, "bits", pBytStuff)
If (pDBCCommand.ExecuteNonQuery() > 0) Then
'OPERATION WAS FINE!
End If
End If

Cheers
Adam

Nak · Oct 22, 2004

Hi Adam,

Again I suggest using the ADO.NET newsgroup as the people there are far
more experienced than I at this. But from what I can see, your command is
syntactically incorrect,

pDBCCommand.CommandText = "UPDATE TestsConfig Set ReadMe=@bits Where
Type=@testselected"
....
Call addParameter(pDBCCommand, "@bits", pBytStuff)
Call addParameter(pDBCCommand, "@testselected", TestSelected)

The ADO.NET newsgroup is "microsoft.public.dotnet.framework.adonet".
The above is untested, but I think that is how it needs to be written. I
have also noticed that the "Type" field is a String, is this correct? I
changed is so it is added as a parameter also just to double check that the
data type is correct. But anyway, sorry if this doesn't help! :-(

Nick.

Hi,

Thanks for the code.....

I pasted it in and made a few changes to suit my app (see below) which
include a slight mod to the connection to get a udl and the command text to
do an update instead of insert + it's param, but when run it, when i get to
'If (pDBCCommand.ExecuteNonQuery() > 0) Then' I get :
'An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll'

Any ideas would be most appreciated!

Mod'd Code:
Dim pBytStuff As Byte() = StringToArray(Rtxt_readme.Rtf) 'convert rtf field
to byte array

Dim pDBNConnection As OleDbConnection
Dim pDBCCommand As OleDbCommand
pDBNConnection = New OleDbConnection
pDBNConnection.ConnectionString = Globals.udl0Val 'takes con from Globals
Class which returns a udl file
Call pDBNConnection.Open()
If (pDBNConnection.State = ConnectionState.Open) Then
pDBCCommand = pDBNConnection.CreateCommand
pDBCCommand.CommandText = "UPDATE TestsConfig Set ReadMe = (bits)
Where Type = '" & _
TestSelected &
"'"
pDBCCommand.CommandTimeout = 20
Call addParameter(pDBCCommand, "bits", pBytStuff)
If (pDBCCommand.ExecuteNonQuery() > 0) Then
'OPERATION WAS FINE!
End If
End If

Cheers
Adam

Adam Maltby · Oct 22, 2004

Hi Nick,

The "TestSelected" is a vb var hence my coding it as it was.....
I'll re-post to the ado group as you suggested.
Thanks anyway!

Cheers
Adam

RTF saving into SQL

Adam Maltby

Nak

Cor Ligthert

Nak

Adam Maltby

Nak

Adam Maltby

Nak

Adam Maltby