473,320 Members | 1,958 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
3 2456
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
Thanks. Tried it. Did not work.

Nov 13 '05 #3
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Universal | last post by:
store Images (or any Binary Data) to a SQL Server database using VC++ Can anybody help me in this Thanks in advance
3
by: PVU | last post by:
Hello, I have an page that calls the function below. What i want to do is that this function post data to the server (ASP) and the server will response with 'OK' My question is how can i send...
3
by: Doug | last post by:
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...
6
by: | last post by:
Hi all, is there a better way to stream binary data stored in a table in sql 2005 to a browser in .net 2.0? Or is the code same as in .net 1.1? We noticed that in certain heavy load scenarios,...
4
by: Schwarty | last post by:
I hope I posted this to the correct group. If not, please let me know and I will get it posted in the correct section. I have a web application developed in ASP.NET using C# for the code behind....
6
by: sajohn | last post by:
Hi A separte application is sending me a pointer to some binary data and I need to find the size of the binary data being passed to me. Does anyone know I can go about doing this? TIA
0
by: RN | last post by:
Hi everyone, First please let me explain. I am attempting to store pdf files in an MS Access DB (2000) and I have written a subroutine to do this. My code seems to work perfectly (see code...
1
by: chaitanya02 | last post by:
Hi All, I have a dvd which has to be stored in SQL server 2000 in the binary format. I want to read this binary values from the sql server, and generate a url and get it on the asp page. Let me...
1
by: nordy | last post by:
Hi, I have these tables with binary data stored in tinyblobs and blobs in MySql. In my application I sometimes need to copy them to another table. Not all the data, maybe just a column or two. So...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.