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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Thanks. Tried it. Did not work.

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)


"Doug" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks. Tried it. Did not work.
>