Binary data stored in SQL Server: can't read from ASP.NET, *can* read from Access?

[Doug]

Working on converting an Access front-end/SQL Server back-end to a .NET
front/SQL Server back-end. We are also redesiging the SQL Server
database.

We have many Word/Excel documents that have been stored as OLE Objects
in the old SQL Server database. We can move them over to the new
database okay. We can "read" the files okay when accessing them via
Access (design mode/open table or runtime via double-clicking on an
object frame directly bound to the appropriate column).

However, we cannot read the files from ASP.NET. If we upload it from
ASP.NET, we can read it fine. This leads us to believe that MS Access
is "adding" something to the file header, or performing some other
weird trick.

This is the code we're using (sorry for the formatting):

Dim clsCR As New CommonRoutines
Dim clsHTML As New HTMLWrite

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim cmdGetFile As New System.Data.SqlClient.SqlCommand

cmdGetFile.CommandText = "SELECT Datalength(FileData) As
FileSize,FileName,FileData,ContentType " & _
"FROM tblCMDocument " & _
"WHERE CMDocumentID = @CMDocumentID"
cmdGetFile.Connection = clsCR.conConnection
cmdGetFile.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CMDocumentID",
System.Data.SqlDbType.Int, 4, "CMDocumentID"))
Dim dr As System.Data.SqlClient.SqlDataReader

cmdGetFile.Parameters("@CMDocumentID").Value =
Request("CMDocumentID").ToString

dr = cmdGetFile.ExecuteReader

If dr.Read Then
Response.ContentType = dr("ContentType").ToString
Response.OutputStream.Write(CType(dr("FileData"), Byte()),
0, CInt(dr("FileSize")))
Response.AddHeader("Content-Disposition",
"attachment;filename=" + dr("FileName").ToString())
Else
Response.Write("File Not Found.")
End If
End Sub

 

[Bruce Barker]

you output is merged with what ever html/controls are on the page.

try:

Response.Clear()
Response.ContentType = dr("ContentType").ToString
Response.AddHeader("Content-Disposition","attachment;filename=" +
dr("FileName").ToString())
Response.OutputStream.Write(CType(dr("FileData"), Byte()),0,
CInt(dr("FileSize")))
Response.End()

add Response.Clear() at the tsrat and Response.End()

 

[Doug]

Thanks. Tried it. Did not work.

 

[Bruce Barker]

then you probably did not convert the ole object to binary data when you
moved them to sqlserver. when access stores ole data in a database, it uses
that component to serialize the data to a binary stream. this is not the
same format as a file. to read the data, you need to create an instance of
the component, load the stream, then have the component write to a file
format.

-- bruce (sqlwork.com)