By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,290 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,290 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <sp*******@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
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

Nov 13 '05 #2

P: n/a
Thanks. Tried it. Did not work.

Nov 13 '05 #3

P: n/a
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" <sp*******@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Thanks. Tried it. Did not work.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.