DIsplaying Word Binary from SQL to VB.Net

Hi all,

I have stored a word document in the sql database in a column of type Image.

I am trying to retrieve it from the database into an array of bytes and then open it.

The Word.Application.Document.Open function needs a file name there to open it, but i basically have a variable called BinaryData that is of type Byte, and that holds the information that is retrieved from the DB(in binary).

My code at the moment is:
 Public Shared Function GetLetterDocument(ByVal LetterCode As String)
        Dim oFS As System.IO.FileStream
        Dim oDS As New DataSet
        Dim BinaryData() As Byte
        Dim arParams() As SqlParameter = New SqlParameter(0) {}
        Dim strfn As String = Convert.ToString(DateTime.Now.ToFileTime())
        Dim _Word As Word.Application

        Try
            arParams(0) = New SqlParameter("@Letter_Code", SqlDbType.VarChar, 3)
            arParams(0).Value = LetterCode.Trim

            oDS = SqlHelper.ExecuteDataset(cnPredator, CommandType.StoredProcedure, "USP_Maintenance_LetterData_Select", arParams)

            If Not oDS Is Nothing Then
                BinaryData = Convert.ToByte((oDS.Tables(0).Rows(0).Item(0).ToString.Trim))

                oFS = New System.IO.FileStream(strfn, FileMode.CreateNew, FileAccess.Write)
                oFS.Write(BinaryData, 0, BinaryData.Length)
                _Word.Documents.Open(strfn)

            End If
           
        Catch ex As Exception
            Throw ex
        End Try
    End Function

The line  BinaryData = Convert.ToByte((oDS.Tables(0).Rows(0).Item(0).ToString.Trim)) is not workin, as it says i cant convert 1-dimensional byte to byte array.

So, im confused as to how i can retrieve that info and open word to display the doc.

Thanks
Saurabh
saumathurAsked:
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.

slyckboyCommented:
In oracle i use the response.binarywrite.  You may want to look into that.  I am not schooled on how sql server stores the word document but here is an example of how i did it in oracle, maybe you can salvage something from this.

                sqlDocument = "select attachment document, attachment_name doc_name " & _
                                      "from cdi.email_attachments " & _
                                      "where attach_id = :docid"
                intId = Request.QueryString("attach_id")
           
            cn = getconnection()
            cmdDocument = New OracleCommand(sqlDocument, cn)
            cmdDocument.Parameters.Add("docid", OracleDbType.Int64).Value = intId
            drDocument = cmdDocument.ExecuteReader(CommandBehavior.CloseConnection)
            While drDocument.Read
                Response.ContentType = "application/vnd.ms-word"
                Response.AddHeader("Content-Disposition", "attachment;filename=" & drDocument("doc_name"))
                Response.BinaryWrite(drDocument("document"))
            End While

the section in the while loop will probably be most useful to you.
maybe something like
response.binarywrite(oDS.Tables(0).Rows(0).item(0))

I believe the error you are getting is because you are trying to stick a string into a string array.  The byte array is an array that holds each character in a different index.  But all that aside i think the response.binarywrite is where you are going to find the answer.  I will look into it more.

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
saumathurAuthor Commented:
I changed my code to be somethng like this :
 BinaryData = CType(SqlHelper.ExecuteScalar(cnPredator, CommandType.StoredProcedure, "USP_Maintenance_LetterData_Select", arParams), Byte())
            If BinaryData.Length > 0 Then
                 oFS = New System.IO.FileStream("c:\hello.doc", FileMode.CreateNew, FileAccess.Write)
                oFS.Write(BinaryData, 0, BinaryData.Length)

                _Word = New Word.Application
                _Word.Documents.Open("c:\hello.doc")
            End If

Here, it gave me an error that it couldnt open the doc..i tried to open it from the windows, and it didnt.

So i dunno is my insert a problem? here is the code for that:

 'read full file content into memory
            oFS = New System.IO.FileStream(Location, System.IO.FileMode.Open, System.IO.FileAccess.Read)
            ReDim BinaryData(oFS.Length)

            oFS.Read(BinaryData, 0, oFS.Length)
            oFS.Close()

            'Now store these as the parameters
            arParams(0) = New SqlParameter("@Letter_Code", SqlDbType.VarChar, 3)
            arParams(0).Value = LetterCode.Trim

            arParams(1) = New SqlParameter("@Letter_Data", SqlDbType.VarBinary, 2147483647)
            arParams(1).Value = BinaryData

            SqlHelper.ExecuteNonQuery(cnPredator, CommandType.StoredProcedure, "USP_Maintenance_LetterData_Insert", arParams)

The column is of type Image....but i read somewhere that i shud use varbinary in the proc..!!! the proc is as follows:
CREATE PROCEDURE USP_Maintenance_LetterData_Insert
       @Letter_Code AS CHAR(3)
      ,@Letter_Data AS VARBINARY(8000)
      
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED


      UPDATE [Letters]
      SET       [Letter_Data] = @Letter_Data
            
      WHERE [Letter_Code] = @Letter_Code


Thanks again,
saumathurAuthor Commented:
OK, i changed everything to IMAGE in the procedure and the code for the insert, and now i can open it.

But this makes me open the file by creating a new file..is there a way to open the Stream, so that the physical file doesnt need to be stored on the computer?
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.