Downloading documents from sql server

Does anyone know how to download documents using sql server from asp.net? I have uploaded files to the database and now i want to retrieve those files.
Rpatel108Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachiekCommented:
You should take look at this below page.

Importing Word Documents into SQL Server
http://www.windowsitpro.com/SQLServer/Article/ArticleID/37903/37903.html
0
Rpatel108Author Commented:
Thanks but this is not actually what i'm looking for!

In my application i want to be able to download documents, and view them from asp.net where the documents are held on sql server. i dont want to directly download them from sql server.
0
sachiekCommented:
Can you tell me which version of word you are using?
Because are you aware of this MS-Office 2003 - which can store all it's document as XML documents.
In that case you can simple to do better. And what you are asking for is going to be easier and straightforward.

Sachi
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Rpatel108Author Commented:
I'm using office 2003.

But the thing is when i upload documents they can be different types; jpg, txt, doc, etc. so basically all i want to do is the reverse of uploading all those i want to be able to download all of those by hardcoding it in my asp.net code.
0
RejojohnyCommented:
i hope u have the content type stored in the table when u uploaded the documents .. look at this example which explains to upload, delete, retrive documents from SQL server using ASP.net ..
http://www.dotnetspider.com/technology/kbpages/943.aspx
0
Rpatel108Author Commented:
Thanks i will take a look. content type meaning what type of document it is - yeah i have a column for that in my database
0
RejojohnyCommented:
could u also tell me how u have stored the docs .. in binary format .. right?
0
Rpatel108Author Commented:
yup
0
RejojohnyCommented:
use this to retrive the document  ..

imports system.data.sqlclient

        Dim lobjConnection As SqlConnection
        Dim lobjCommand As SqlCommand
        lobjConnection = New SqlConnection("urConnectionString")
        lobjConnection.Open()
        lobjCommand = New SqlCommand("Select txt_ContentType, documentFieldName from Table", lobjConnection)
        lobjCommand.CommandType = CommandType.CommandText
        lobjCommand.CommandTimeout = 2000
        Dim ldtrPictures As SqlDataReader = lobjCommand.ExecuteReader()
        Do While (ldtrPictures.Read())
            Response.ContentType = ldtrPictures.Item("txt_ContentType")
            Response.BinaryWrite(ldtrPictures.Item("documentFieldName"))
        Loop

        If Not lobjConnection Is Nothing Then
            If lobjConnection.State = ConnectionState.Open Then
                lobjConnection.Close()
            End If
        End If
        ldtrPictures.Close()
        ldtrPictures = Nothing
        lobjCommand = Nothing
        lobjConnection = Nothing
0
Rpatel108Author Commented:
actually i just checked i've stored them in an image format.

How can i link this code to a datagrid?

Will this code allow the user to save the document on their hardrive? like when you download something it promts you to save it or run it.
0
RejojohnyCommented:
>>actually i just checked i've stored them in an image format.
no problem .. it is binary

>>How can i link this code to a datagrid?
u have a image control in datagrid and u want to show the images within it? if s, u will have to write the code i provided in the load event of a seperate page .. the code is shown below .. i have used a datalist .. u can use a datagrid instead

>>Will this code allow the user to save the document on their hardrive? like when you download something it promts you to save it or run it.
the prompt is based on the settings of the file type in the explorer .. and u cannot control when the popup has to appear .. u can change the settings (but not programatically) .. but then i assume as this been done asp.net the client can be any computer and u will not like to change the settings of the client computer

--- code .
the page where u will have the datagrid ... .aspx
<asp:datalist id="dltImages" runat="server" RepeatColumns="2" ShowFooter="False" ShowHeader="False"
Width="100%">
<AlternatingItemStyle BackColor="#FDFDFD"></AlternatingItemStyle>
<ItemTemplate>
      <TABLE width="100%">
            <TR>
                  <TD align="center">
                        <asp:Image id=imgPicture runat="server" ImageUrl='<%# GetPicture(DataBinder.Eval(Container.DataItem, "id_PropertyPicture")) %>'>
                        </asp:Image></TD>
            </TR>
      </TABLE>
</ItemTemplate>
</asp:datalist>

same .. code-behind .. .vb
    Function GetPicture(ByVal pstrId As String) As String
        If Len(Trim(pstrId)) > 0 Then
            Return ("GetPropertyPicture.aspx?ID=" & pstrId)
        End If
    End Function


GetPropertyPicture.aspx is the new page which will have the code which i had provided in the load event .. it expects the Id as a querystring for which it has fetch the image ..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rpatel108Author Commented:
i get an error: commandtext is not a member of system.data.commandtype
0
Rpatel108Author Commented:
Thank you ever so much it worked, you have really really helped me alot! I couldnt thank you enough. :)
0
Rpatel108Author Commented:
ah i have one problem though, when the document is saved it appears in ASP.net server page. how can i get to its original form? like if its txt, or .doc etc?
0
Rpatel108Author Commented:
It seems to be downloading the actual code of the page which has the load event! which in this case is DocumentData
0
Rpatel108Author Commented:
sorry i meant  GetPropertyPicture.aspx page


i think its something to do with returning bit in the Function GetPicture
("GetPropertyPicture.aspx?ID=" & pstrId)

pstrId - is this meant to be the id of the document?
0
RejojohnyCommented:
thx for the points !!

>>It seems to be downloading the actual code of the page which has the load event!
what do u mean? does it show the content in binary format?
0
RejojohnyCommented:
>>pstrId - is this meant to be the id of the document?
s, it is supposed to be the unique id of the table using which u will be able to fetch the image.

assuming ur table structure is
id_PropertyPicture - int - Unique Id
MyImage - image - Binary data
txt_ContentType - Varchar(50) - Content type - eg. application/jpeg etc

<asp:Image id=imgPicture runat="server" ImageUrl='<%# GetPicture(DataBinder.Eval(Container.DataItem, "id_PropertyPicture")) %>'>

u will notice the code of the grid has the imageURL bound to id_PropertyPicture as shown above .. so what happens is when the grid is build, each row will call the function GetPicture which will call the GetPropertyPicture.aspx with the id as the querystring ...
0
Rpatel108Author Commented:
>>>It seems to be downloading the actual code of the page which has the load event!
>>what do u mean? does it show the content in binary format?

i get the actual code written in this file GetPropertyPicture.aspx, not in binary but asp.net code.

basically where is says: GetPropertyPicture.aspx, in ("GetPropertyPicture.aspx?ID=" & pstrId) that is where i think it is returning the page.

I'm not actually using the datalist, or the code that u gave me for the datalist, as it didn't work. but what i did is in my datagrid in the property builder i put this in newdownload.aspx?Document_ID={0} the URL format string. i thought this would bring up the document id of the selected file and return it in the newdownload.aspx page.


in my datagrid i am displaying all the uploaded files, and a hyperlink as the name of the file which when clicked on allows you to download the file that has been clicked on but its giving me the code for the file GetPropertyPicture.aspx.

 so maybe i should put this in my hyperlink URL? :'<%# GetPicture(DataBinder.Eval(Container.DataItem, "id_PropertyPicture")) %>'>

what do you think?
0
RejojohnyCommented:
what u have done will not work .. if u have a look at the code i have given, u will notice that i have added a image control and the image has the url as the the page.aspx .. what happens is for each row, the code in the page gets executed which returns back the image and which automatically gets assigned to the image control ..
0
Rpatel108Author Commented:
how can i get it so that the file is linked to a hyperlink rather than the image? i dont really want to use an image control.
0
RejojohnyCommented:
u mean show a link to a image?
0
Rpatel108Author Commented:
what i have got at the moment is a datagrid, with the name of the file in the form of a hyperlink, and whe the hyperlink is clicked it then downloads that file. thats what i want.
0
Rpatel108Author Commented:
its ok i got it to work
0
RejojohnyCommented:
good :-) ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.