SQL Image to rich text box

[Mark]

Hi

I am attempting to load data from an ADO recordset (data type = image on SQL
server) into a rich text box. I can save data from the box to the server ok
but I can't display it (

I keep getting errors along the line 'cast byte from string not valid'
running

pNoteRTF.Rtf=rs("noteRTF").Value

Any ideas or examples would be much appreciated...

Also, would I be better off storing the rtf as a file as opposed to an image
maybe?

Cheers

Mark

 

[Andrew D. Newbould]

Hi

I am attempting to load data from an ADO recordset (data type = image on SQL
server) into a rich text box. I can save data from the box to the server ok
but I can't display it (

I keep getting errors along the line 'cast byte from string not valid'
running

pNoteRTF.Rtf=rs("noteRTF").Value

Firstly, SQL Server's image data type is defined as adVarLongBinary in
ADO so your code is failing for good reasons. You need to use the
GetChunk / AppendChunk methods of the Fields collection to manipulate
its content.

Also, would I be better off storing the rtf as a file as opposed to an image
maybe?

Depends on your requirements. Personally, I don't like storing files on
any type within a database unless I really have to. If the system is on
a LAN you could store a link to the real file within your database and
then make open the file from that public share.

 

[Mark]

Thanks Andrew...

I was hoping to be able to load the rtf's into the database so

1) I could control security (being a bit lazy I am) from within SQL server.
Some of the files need to be protected / hidden etc. and..

2) I wanted to be able to search the files in queries. I understand this is
not possible and have been considering either a) copying the unformatted
text (on save) to the backend (for search purposes) whilst storing the
formatted text in a file (not so clever) or b) programatically doing a
search for files containing specified text and appending these to the list
of records containing specified text (some records hold text in RTF and
others within the record itself).

It's a bit of a pain but I'm tryinging to get seamless results where a
search can cover both table and file data (the files are named
primaryKey.rtf so returning a list of filenames would work).

Thanks again

Mark

 

[Andrew D. Newbould]

Thanks Andrew...

I was hoping to be able to load the rtf's into the database so

1) I could control security (being a bit lazy I am) from within SQL server.
Some of the files need to be protected / hidden etc. and..

2) I wanted to be able to search the files in queries. I understand this is
not possible and have been considering either a) copying the unformatted
text (on save) to the backend (for search purposes) whilst storing the
formatted text in a file (not so clever) or b) programatically doing a
search for files containing specified text and appending these to the list
of records containing specified text (some records hold text in RTF and
others within the record itself).

It's a bit of a pain but I'm tryinging to get seamless results where a
search can cover both table and file data (the files are named
primaryKey.rtf so returning a list of filenames would work).

If you take the route of storing the unformatted text in the database
then I would suggest changing the data type to TEXT rather than Binary
or Image for storage and access issues (ie: avoiding GetChunk, reducing
index fragmentation, improve performance etc).

Also, it may be wise to put these in a separate link table rather than
the main table. This again would improve storage and you can ignore the
large Text fields when not required (ie: if required use a Select with
an INNER JOIN).

By using a Text field you would also be able to perform selects like
"SELECT * FROM MyTable [optional inner join] WHERE MyTextField LIKE
'%Search String%' ".

Kind Regards,

 

[Mark]

Thanks again Andrew... That pretty much may be where I'm heading. Next thing
to tackle is security on the formatted files... I need them to be accessible
only through the application (for non admin users etc.).

Mark