I see a couple of problems.

Use the ReadAll method of the TextStream object to read in an entire file.
Note: Reading in a vary large file like this can be problematic and other
methods should be considered.

The variable you read into should be a variant, not string, as a TextStream
object of type adVarBinary will return a byte array.

The real problem comes in trying to use a stored proc to save a binary. You
are far better off using the ADO method as you are, but don't open the
Recordset with Select * from..... this is way too slow. See the VB code
below to speed things up.
If you want to use a Stored Proc you will have to use TEXTPTR, READTEXT,
WRITETEXT, and UPDATETEXT.

Using text, ntext, and image Functions
There are two text, ntext, and image functions used exclusively for
operations on text, ntext, and image data:

TEXTPTR returns a binary(16) object containing a pointer to a text, ntext,
or image instance. The pointer remains valid until the row is deleted.


TEXTVALID function checks whether a specified text pointer is valid or not.
Text pointers are passed to the READTEXT, UPDATETEXT, WRITETEXT, PATINDEX,
DATALENGTH, and SET TEXTSIZE Transact-SQL statements used to manipulate
text, ntext, and image data.

In Transact-SQL statements, text, ntext, and image data is always referenced
using pointers or the address of the data.

This example uses the TEXTPTR function to locate the text column (pr_info)
associated with pub_id 0736 in the pub_info table of the pubs database. It
first declares the local variable @val. The text pointer (a long binary
string) is then put into @val and supplied as a parameter to the READTEXT
statement, which returns 10 bytes starting at the fifth byte (offset of 4).

USE pubs
DECLARE @val varbinary(16)
SELECT @val = textptr(pr_info) FROM pub_info
WHERE pub_id = '0736'
READTEXT pub_info.pr_info @val 4 4

Here is the result set:

(1 row(s) affected)

pr_info
----------------------------------------
 yet

Explicit conversion using the CAST function is supported from text to
varchar, from ntext to nvarchar, and from image to varbinary or binary, but
the text or image data is truncated to 8,000 bytes and ntext data is
truncated at 4,000 characters (8,000 bytes). Conversion of text, ntext, or
image to another data type is not supported, implicitly or explicitly.
However, indirect conversion of text, ntext or image data can be done, for
example:

CAST( CAST( text_column_name AS VARCHAR(10) ) AS INT ).


See Also

Text and Image Functions



'***************************************************************************
************************
	Dim strSQL as String
	Dim objRS As New ADODB.Recordset
	Dim objConn As New ADODB.Connection
	Dim objStream1 As New ADODB.Stream

    	objStream1.Type = adTypeBinary
    	objStream1.Open
	objStream1.LoadFromFile "c:\test.rtf"

	'Look for the rtf you will be saving
	'This limits the select to a single row for updates
      'and if the row does not exist you don't have to wait for the result.

	strSQL = Select rtfBlobID, rtfBlob from Rich_Text_Test_T where rtfBlobID 
GUID

	Set objRS = objConn.Execute(strSQL)

	If objRS.Eof Then
		objRS.AddNew
	End if
	objRS("rtfBlobID") = GUID
	objRS("rtfBlob) = objStream1.ReadAll
	objRS.Update

	objRs.close
	Set objRS = nothing
	objStream.close
	Set objStream1 = nothing
	Set objConn = nothing







Donald C. Burr, CIO
Nationwide Auction Systems
13005 Temple Ave.
City of Industry, CA 91746
Direct:  (xxx) xxx-xxxx
Cell:  (xxx)xxx-xxxx
dburr@n... <mailto:dburr@n...>


-----Original Message-----
From: Chris [mailto:fluffypillow3@h...]
Sent: Friday, February 28, 2003 04:34
To: professional vb
Subject: [pro_vb] ADO Stream Image


Hi People,

I have a post over in SQL_Server in regards to putting richtext into a SQL
DB...  have found 1 solution but now looking for alternative...


http://p2p.wrox.com/view.asp?list=sql_server&id=265716


Ok, I have figured out 1 method of saving RichText to the SQL DB...  by
using the ADO Stream object....

What I do is save the richtextbox as an rtf file and then stream that file
to the db....  to load the richtext back, stream the contents of the DB to
a temp file and then load that temp file to the richtextbox...

However, I wud prefer to save the richtext to the DB in a different
method, by passing the stream and other variables to a stored procedure,
however I can't pass the stream correctly to the stored procedure.

The image field on the SQL database has the ADO equivalant of adVarBinary
but I'm not aware of the VB equivalant.  I have tried, string, variant etc
but no luck

Any help wud be great, Tanx All.....

Here is the first piece of coding I can use which works...


Private Sub cmdSaveRichText_Click()
    Dim stmStream

    Set objRSData = New ADODB.Recordset
    objRSData.Open "Select * from Rich_Text_Test_T", g_objConn,
              adOpenKeyset, adLockOptimistic

    rtbText1.SaveFile ("c:\test.rtf")

    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.LoadFromFile "c:\test.rtf"

    stmStream = mstream.Read
    objRSData.Fields("Rich_Text_VC").Value = stmStream
    objRSData.Update

    objRSData.Close

End Sub



Private Sub cmdReadRichText_Click()

    Set objRSData = New ADODB.Recordset
    objRSData.Open "Select * from Rich_Text_Test_T", g_objConn,
              adOpenKeyset, adLockOptimistic

    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write objRSData.Fields("Rich_Text_VC").Value
    mstream.SaveToFile "c:\test1.rtf", adSaveCreateOverWrite

    rtbText1.LoadFile ("c:\test1.rtf")

    objRSData.Close

End Sub

now that code works fine, but is not how I would ideally like to read and
update the DB....  Here's is what I am trying to do...



Private Sub cmdSaveSP_Click()

    'Declare local variables
    Dim lngRC As Long, strMessage As String, _
        stmStream As String
        'have tried variant also


        rtbText1.SaveFile ("c:\test.rtf")

        Set mstream = New ADODB.Stream
        mstream.Type = adTypeBinary
        mstream.Open
        mstream.LoadFromFile "c:\test.rtf"

        stmStream = mstream.Read


        'Set a reference to the insert data class
        Set objInsert = New clsInsertData

        'Insert the new script
        lngRC = objInsert.InsertRichText(g_objConn, _
                    stmStream, _
                    strMessage)

        'Ensure we were successful
        If lngRC <> 0 Then
            Err.Raise 513 + vbObjectError, "cmdSaveScript_Click", _
                "Call to InsertAdScript failed"
        End If

        'Remove reference to object
        Set objInsert = Nothing

End Sub


Public Function InsertRichText( _
        ByRef objConn As ADODB.Connection, _
        ByVal stmRichText As String, _
        ByRef strMessage As String) As Long

    'Setup error handling
    On Error GoTo InsertRichText_Err

    'Declare local variables and objects
    Dim objCmd As ADODB.Command

    'Set a reference to the command object
    Set objCmd = New ADODB.Command

    'Set the command object properties
    Set objCmd.ActiveConnection = objConn
    objCmd.CommandText = "up_parmins_rich_text"
    objCmd.CommandType = adCmdStoredProc


    'Create and append the paameters to the parameters collection
    objCmd.Parameters.Append objCmd.CreateParameter("RC", _
        adInteger, adParamReturnValue)
    objCmd.Parameters.Append objCmd.CreateParameter("RichText", _
        adVarBinary, adParamInput, , stmRichText)

    'Execute the command object
    objCmd.Execute

    'Check the return value from the stored procedure
    If objCmd.Parameters("RC") <> 0 Then
        Err.Raise 513 + vbObjectError, "InsertRichText", _
            "up_parmins_rich_text"
    End If


    'remove the references to objects
    Set objCmd = Nothing

    'set the return code
    InsertRichText = 0

    'exit function
    Exit Function

InsertRichText_Err:
    'Enumerate ADO errors
    strMessage = EnumerateErrors(objConn)
    'Append any VB errors
    strMessage = strMessage & Err.Number & " : " & Err.Description
    'Return to the caller with a RC of 1
    Debug.Print strMessage
    InsertRichText = 1
End Function

And here's is the stroed procedure for the insert...

CREATE PROCEDURE up_parmins_rich_text
	@Rich_Text_VC		IMAGE 	AS

Declare @New_ID INT


SELECT @New_ID = MAX(No_IN)
	FROM Rich_Text_Test_T


INSERT INTO Rich_Text_Test_T
	(No_IN, Rich_Text_VC)
	VALUES
	(@New_ID, @Rich_Text_VC)