That article described how to upload a binary file via ASP into the database. In this article I am going to talk about the second part, displaying that binary data from the database.
File Uploading with ASP.NETIf you have the privilege of using ASP.NET then you should read these comprehensive tutorials regarding file uploading using built-in ASP.NET server controls:
File uploading to server hard disk. File uploading to Microsoft Access database. Uploading images, determining size, width & height and resizing image files. In the 1st article I deliberately left two files, show.asp and file.asp Those two files are going to be the ones we create in this article.
Show.aspOpen notepad and create a new file. Name it as show.asp. Copy the following code and paste it into the newly created show.asp file and hit the save button :
' -- show.asp -- ' Generates a list of uploaded files Response.Buffer = True ' Connection String Dim connStr connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("FileDB.mdb")
Showing Binary Data from the Database
To insert data click here
| " Response.Write "No. of records : " & rs.RecordCount Response.Write ", Table : Files " Response.Write " | ||||||
| " Response.Write rs("ID") & " | " Response.Write "" Response.Write rs("File Name") & " | " Response.Write rs("File Size") & " | " Response.Write rs("Content Type") & " | " Response.Write rs("First Name") & " | " Response.Write rs("Last Name") & " | " Response.Write rs("Profession") Response.Write " |
' -- show.asp --' Generates a list of uploaded files Response.Buffer = True
Set the buffering of the show.asp page to True.
' Connection StringDim connStr connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("FileDB.mdb")
Next we declare a variable connStr as our connection string and set it's value to the path of FileDB.mdb database. Note that FileDB.mdb is the database we have been using to store binary data.
show.asp
' Recordset ObjectDim rsSet rs = Server.CreateObject("ADODB.Recordset")
' SQL StatementDim sql_selectsql_select = "SELECT [ID],[File Name],[File Size],[Content Type] "sql_select = sql_select & "FROM Files ORDER BY [ID] desc"
' opening connectionrs.Open sql_select, connStr, 3, 4
We create a Recordset object and run a SELECT query to retrieve all the records from the Files table.
If Not rs.EOF ThenResponse.Write "
"Response.Write "
If the retrieved Recordset is not empty, meaning that there are some records in the Files table, we write the headers of an HTML table to show these records.
While Not rs.EOFResponse.Write "
rs.MoveNextWend
Using a While...Wend loop we display all the records in the Files table.
ElseResponse.Write "No Record Found"End If
rs.CloseSet rs = Nothing
If the Recordset was empty, meaning there are no records in the Files table we display a "No Record Found" message. Next we close the connection to the database and Set Recordset object to Nothing.
Notice that in the all the records that we displayed we linked each record to file.asp page, file.asp is going to be the actual file to display binary records from the database.
file.aspOpen notepad and create a new file. Name it as file.asp. Copy the following code and paste it into the newly created file.asp file and hit the save button :
' -- file.asp -- ' Retrieves binary files from the database Response.Buffer = True ' ID of the file to retrieve Dim ID ID = Request("ID") If Len(ID) < 1 Then ID = 7 End If ' Connection String Dim connStr connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("FileDB.mdb") ' Recordset Object Dim rs Set rs = Server.CreateObject("ADODB.Recordset") ' opening connection rs.Open "SELECT [File Name], [File Data], [Content Type] FROM Files " & _ " WHERE ID = " & ID, connStr, 2, 4
If Not rs.EOF Then Response.AddHeader "Content-Disposition", "filename=" & _ rs("File Name) Response.ContentType = rs("Content Type") Response.BinaryWrite rs("File Data") End If rs.Close Set rs = Nothing
Explanation
' -- file.asp -- ' Retrieves binary files from the database
Response.Buffer = True
' ID of the file to retrieve Dim ID ID = Request.QueryString("ID")
Sets the buffering to True. Next we create a variable named ID and set it's value to the Request.QueryString("ID").
' Connection StringDim connStr connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("FileDB.mdb")
Next we create a connStr variable as the connection string to the database and set it's path to FileDB.mdb database.
' Recordset ObjectDim rs Set rs = Server.CreateObject("ADODB.Recordset")
' SQL SELECT Statement Dim sql_select sql_select = "SELECT [File Name], [File Data], [Content Type] " sql_select = sql_select & "FROM Files WHERE ID = " & ID
' opening connection rs.Open sql_select, connStr, 2, 4
Create a Recordset object and run a SELECT statement to retrieve the File Name, File Data and Content Type of the given file.
Note that File Data field contains the saved binary data for the file, while Content Type field contains the content type for the file. We need both of them to display the file. The "Content-Disposition" header is necessary to send the file with the correct name back to the browser.
If Not rs.EOF Then Response.AddHeader "Content-Disposition", "filename=" & _ rs("File Name) Response.ContentType = rs("Content Type") Response.BinaryWrite rs("File Data")End If
If Recordset is not empty i.e, there is a file with given ID, then we set the Content Type of given ASP page to file's Content Type. Next we use Response.BinaryWrite method to write the binary data to the client browser. The file will thus be shown to the client.
rs.Close Set rs = Nothing
We close the connection to the database.
On the next page I summarize the steps involved in displaying binary data from the database.
SummaryThis article was second in the series of articles about manipulating binary data via ASP and storing it in the database. In this article we built files which were missing in the ASP-Database file uploader application in the first article. show.asp simply displayed a list of all the files in the database. file.asp was the actual file which will send the binary data back to the client.
You must have noticed that how easy it is to write binary data from the database via ASP to the client. There are two things to keep in mind while doing that. First, always properly set the Content Type of the page using Response.ContentType property. Secondly, use one ASP page to display only ONE file like we did with file.asp. And that page should not write anything else to the browser i.e, don't try to write text in an ASP page as we do in other ASP pages when you've already used the Response.BinaryWrite method.
Well that's it for this article. You should continue your reading and read the other two articles of this series; "inserting binary data to the database" and "uploading binary data ( files ) to the server hard disk". That'll be enough.
