September 21, 2005 at 2:28 pm
I am having a hard time to figure out how to read file from database (PDF or DOC). Please help! Here what I have so far... IT READS DOCUMENT SIZE! How to get a content?
P.S 5 mins later I figured it out... WORKS! (see bold)
My test table:
[dbo].[Z_tblBlob]
[DocID] [int] IDENTITY (1, 1) NOT NULL ,
[DocTitle] [varchar] (200) ,
[Doc] [image] NULL ,
[DocType] [varchar] (50)
[Entrydate] [datetime] NULL
My procedure
CREATE PROCEDURE z_tblBlobDownload_proc
@DocID int = NULL
AS
SELECT DocTitle, Doc, DocType
FROM z_tblBlob
WHERE DocID = @DocID
'======================================================
Private Function getDoc(ByVal requestedID As Integer)
Dim ConnGet As new SqlConnection(strConnectionInfo)
Dim cmdDownloadDoc As new SqlCommand("z_tblBlobDownload_proc",ConnGet)
cmdDownloadDoc.CommandType = CommandType.StoredProcedure
Dim DocID As New SqlParameter("@DocID", SqlDbType.smallint,2)
DocID.Value = requestedID
DocID.Direction = ParameterDirection.Input
cmdDownloadDoc.Parameters.Add(DocID)
cmdDownloadDoc.Connection = ConnGet
Try
ConnGet.Open()
Dim dRE As SqlDataReader
dRE = cmdDownloadDoc.ExecuteReader()
While dRE.Read()
Dim myTitle = dRE.GetString(0) 'document title
Dim myType = dRE.GetString(2) 'document type
Dim myDoc = dRE.GetSqlBinary(1) 'document
Response.Buffer = True
Response.Clear()
Response.AddHeader("content-disposition", "attachment; filename=" & myTitle)
'application/octet-stream
Select Case myType.ToLower
Case "doc"
Response.ContentType = "application/msword"
Case "ppt"
Response.ContentType = "application/vnd.ms-powerpoint"
Case "xls"
Response.ContentType = "application/x-msexcel"
Case "htm"
Response.ContentType = "text/HTML"
Case "html"
Response.ContentType = "text/HTML"
Case "jpg"
Response.ContentType = "image/JPEG"
Case "gif"
Response.ContentType = "image/GIF"
Case "pdf"
Response.ContentType = "application/pdf"
Case Else
Response.ContentType = "text/plain"
End Select
Response.BinaryWrite(myDoc.Value)
Response.Flush()
Response.Close()
if myDoc.isNull then
myErrorDownloadLtl.Text &= "<span class=errorText>Retrieving File: " _
& myTitle & "." & myType & " Size: NULL </span><br>"
else
myErrorDownloadLtl.Text &= "<span class=errorText>Retrieving File: " _
& myTitle & "." & myType & " Size: "& myDoc.toString() &" </span><br>"
end if
End While
Catch SqlEx As System.Data.SqlClient.SqlException
Dim myError As SqlError
myErrorDownloadLtl.Text &= "<span class=errorText>" &(SqlEx.Errors.Count).toString()& "SQL errors occu'rred</span>"
For Each myError In SqlEx.Errors
myErrorDownloadLtl.Text &= myError.Number & " - " & myError.Message
Next
Catch eS As SystemException
myErrorDownloadLtl.Text &= "<span class=errorText>A System error occurred</span>"
Finally
ConnGet.Dispose()
ConnGet = Nothing
cmdDownloadDoc.Dispose()
cmdDownloadDoc = Nothing
End Try
End Function
September 22, 2005 at 1:22 am
thanks for the feedback
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 22, 2005 at 1:13 pm
No problem!
If someone needs help on how to save any file into BLOB of MS Server database in VB.NET - let me know.
September 23, 2005 at 3:07 am
euh ... got a question
What if you have the image in a dataset
How can you figure out what image-extention it has ?
I cannot find the "Dim myType = dRE.GetString(2) 'document type" when working with a datarow
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 23, 2005 at 7:09 am
If you're flexible with creating an extra column in the database you may use my approach. I find the extention of the file before storing it as a blob in the database and saving it in the separate column (surely stripping out " . " before hand).
My test table:
[dbo].[Z_tblBlob]
[DocID] [int] IDENTITY (1, 1) NOT NULL ,
[DocTitle] [varchar] (200) ,
[Doc] [image] NULL ,
[DocType] [varchar] (50)
[Entrydate] [datetime] NULL
Protected WithEvents txtFileContents As System.Web.UI.HtmlControls.HtmlInputFile
Private Dim strDocExt As String = ""
.....
strDocExt = Right(txtFileContents.PostedFile.FileName,4).toString()
Select Case strDocExt.ToLower
Case ".doc"
strDocType = "doc"
Case ".ppt"
strDocType = "ppt"
Case ".htm"
strDocType = "htm"
Case ".html"
strDocType = "htm"
Case ".jpg"
strDocType = "jpg"
Case ".gif"
strDocType = "gif"
Case ".pdf"
strDocType = "pdf"
Case ".xls"
strDocType = "xls"
Case ".csv"
strDocType = "csv"
Case Else
strDocType = "txt"
End Select
then pass this parameter to a function that saves file into database.
September 23, 2005 at 7:26 am
I was trying to avoid just that
I'd seen you derive that info from " dRE.GetString(2) " and was hoping to find an equivalent with datarows
Thanks anyway
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 23, 2005 at 7:42 am
Hmmmm... how many image types do you have? JPEG or GIF or more? ...
Response.ContentType = "image/JPEG" works fine for GIF and JPEG (I just tested)
SqlBinary Structure: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqltypessqlbinaryclasstopic.asp
GetSqlBinary Method: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlcedatareaderclassgetsqlbinarytopic.asp
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/default.aspx?scid=kb;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with VB.NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NET
September 23, 2005 at 7:57 am
arch the nice refs are gone, you must have edited the reply
I have one problem with an anymated gif, it appears to only store the first frame. (When I load it from file, it seems to do allright)
When I get the data from db, it generates an image with a black background
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 23, 2005 at 8:34 am
How is it happening? What do you exactly do?
Did you try using:
Response.ContentType = "image/GIF" or Response.ContentType = "image/JPEG"
I just tested storing Animated Gif and downloading back using IMAGE/JPEG and IMAGE/GIF - works with no problems!
October 5, 2005 at 2:08 pm
Hello!
Your experience is really usefull but my problemem begins with storing a file in the DB.
I have a table like this:
Create table ImgTest (
imgID integer identity(0,1),
imgFname varchar(30),
imgType varchar(30),
imgBdata image,
)
I upload my file (pdf) in asp.net and then I try to insert it to table, but I get this error: man I was trying to get the error but now it worked!!!
I just don't get it.
The error was something about truncating data...
Thanks anyway (you have like remote "matrix" powers...)
Free your mind
October 6, 2005 at 12:00 am
just a thinking :
imgFname varchar(30),
that 30 may be to small, then you'll get an error stating truncation would take place if you want to store a imgFname that is longer than 30 chars.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 6, 2005 at 12:34 am
Sorry for the late followup : I've been attending SQLPASS2005
btw Natalia I got it working now
Thanks
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 6, 2005 at 7:10 am
Congrats! Glad to hear that you did solve the problem. BTW : [DocTitle] [varchar] (200) - for file names usually works with not truncation errors.
October 6, 2005 at 9:44 am
Thank you very much!
Free your mind
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy