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

Discussion in 'ASP .Net' started by Doug, Nov 3, 2005.

  1. Doug

    Doug Guest

    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
     
    Doug, Nov 3, 2005
    #1
    1. Advertising

  2. Doug

    Bruce Barker Guest

    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" <> wrote in message
    news:...
    > 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, Nov 3, 2005
    #2
    1. Advertising

  3. Doug

    Doug Guest

    Thanks. Tried it. Did not work.
     
    Doug, Nov 4, 2005
    #3
  4. Doug

    Bruce Barker Guest

    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" <> wrote in message
    news:...
    > Thanks. Tried it. Did not work.
    >
     
    Bruce Barker, Nov 4, 2005
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gavin Joyce
    Replies:
    0
    Views:
    4,580
    Gavin Joyce
    Jul 5, 2003
  2. =?Utf-8?B?cmljaGk=?=
    Replies:
    3
    Views:
    3,535
    =?Utf-8?B?cmljaGk=?=
    Nov 18, 2004
  3. =?Utf-8?B?Um9iIEM=?=
    Replies:
    0
    Views:
    584
    =?Utf-8?B?Um9iIEM=?=
    Apr 16, 2005
  4. Peri
    Replies:
    1
    Views:
    418
    Patrick Olurotimi Ige
    Jun 13, 2005
  5. izumi
    Replies:
    2
    Views:
    206
    izumi
    Aug 3, 2004
Loading...

Share This Page