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

D

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
 
B

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()
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,439
Members
44,829
Latest member
PIXThurman

Latest Threads

Top