Displaying Images from an Access Database using plain ASP

Introduction
This is the 2nd article in a series of articles about inserting binary data ( files, images etc ) into database ( Access Database ) and then displaying that binary data from the database using ASP. I am going to assume here that you have read the 1st article, Uploading Files into an Access Database using plain ASP. Quite a lot of important background information and detail has been covered there, so better give it a reading beforing continuing with this article.

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.NET
If 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:

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.asp
Open 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")
%>
<html>
<head>
   <title>Inserts Images into Database</title>
   <style>
      body, input, td { font-family:verdana,arial; font-size:10pt; }
   </style>
</head>
<body>
   <p align="center">
      <b>Showing Binary Data from the Database</b><br>
      <a href="insert.htm">To insert data click here</a>
   </p>
   
   <table width="700" border="1" align="center">
<%
   ' Recordset Object
   Dim rs
      Set rs = Server.CreateObject("ADODB.Recordset")
      
      ' opening connection
      rs.Open "select [ID],[File Name],[File Size],[Content Type]," & _
		"[First Name],[Last Name],[Profession] from Files " & _
		"order by [ID] desc", connStr, 3, 4

      If Not rs.EOF Then
         Response.Write "<tr><td colspan=""7"" align=""center""><i>"
         Response.Write "No. of records : " & rs.RecordCount
         Response.Write ", Table : Files</i><br>"
         Response.Write "</td></tr>"
   
         While Not rs.EOF
            Response.Write "<tr><td>"
            Response.Write rs("ID") & "</td><td>"
            Response.Write "<a href=""file.asp?ID=" & rs("ID") & """>"
            Response.Write rs("File Name") & "</a></td><td>"
            Response.Write rs("File Size") & "</td><td>"
            Response.Write rs("Content Type") & "</td><td>"
            Response.Write rs("First Name") & "</td><td>"
            Response.Write rs("Last Name") & "</td><td>"
            Response.Write rs("Profession")
            Response.Write "</td></tr>"
            rs.MoveNext
         Wend
      Else
         Response.Write "No Record Found"
      End If
      
      rs.Close
      Set rs = Nothing
%>
   </table>
</body>
</html>

Explanation

<%
' -- show.asp --
' Generates a list of uploaded files
	
Response.Buffer = True

Set the buffering of the show.asp page to True.

' Connection String
Dim 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 Object
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

' SQL Statement
Dim sql_select
sql_select = "SELECT [ID],[File Name],[File Size],[Content Type] "
sql_select = sql_select & "FROM Files ORDER BY [ID] desc"

' opening connection
rs.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 Then
Response.Write "<tr><td colspan=""4"" align=""center""><i>"
Response.Write "No. of records : " & rs.RecordCount
Response.Write ", Table : Files</i><br>"
Response.Write "</td></tr>"

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.EOF
Response.Write "<tr><td>"
Response.Write rs("ID") & "</td><td>"
Response.Write "<a href=""file.asp?ID=" & rs("ID") & """>"
Response.Write rs("File Name") & "</a></td><td>"
Response.Write rs("File Size") & "</td><td>"
Response.Write rs("Content Type")
Response.Write "</td></tr>"

rs.MoveNext
Wend

Using a While...Wend loop we display all the records in the Files table.

Else
Response.Write "No Record Found"
End If

rs.Close
Set 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.