SQL Image to rich text box

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

re: SQL Image to rich text box

In message <oOfoe.1211$BQ3.304@newsfe3-win.ntli.net>, Mark
<mark@tencdf.com> writes[color=blue]
>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[/color]

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.
[color=blue]
>Also, would I be better off storing the rtf as a file as opposed to an image
>maybe?[/color]

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.

--
Andrew D. Newbould E-Mail: newsgroups@NOSPAMzadsoft.com

ZAD Software Systems Web : www.zadsoft.com

re: SQL Image to rich text box

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

re: SQL Image to rich text box

In message <gaqpe.13937$iy2.2488@newsfe1-gui.ntli.net>, Mark
<mark@tencdf.com> writes[color=blue]
>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).[/color]

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,
--
Andrew D. Newbould E-Mail: newsgroups@NOSPAMzadsoft.com

ZAD Software Systems Web : www.zadsoft.com

re: SQL Image to rich text box

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