Display Images from Database in ASP

• Download source code - 2.2 Kb

Introduction

This article describes how to display images which are stored in a database. You can use <IMG> tag to display images stored in the database by calling another ASP page in SRC attribute of <IMG> tag.

For example:

<IMG SRC="ShowPicture.asp?PhotoId=1">

where PhotoId is the ID stored in the database.

Using the code

Following are the steps which needs to be executed:

1. Create table Users in MS-Access or SQL server.
2. Create ShowPicture.asp page.
3. Call this page using <IMG> tag wherever required.

Create table with the following structure:

Table name: Users

• user_id (AutoNumber)
• user_name (Text)
• user_photo (Ole Object - For MS-Access, and Image data type for SQL server).

Code

ShowPicture.asp is used to display images. You need to pass user ID in querystring, and for that user ID, image will be displayed. Following is the code:

 'Declare Variables..

    Dim sql
    Dim rs
    Dim conn
    Dim userID,str
    
   userID = Request("PhotoId")
   If userID = "" Then userID = 0
   
   'Instantiate Objects

   Set conn = Server.CreateObject("ADODB.Connection")
   Set rs = Server.CreateObject("ADODB.Recordset")
   
   'Open connection

   Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
             "Data Source=" & Server.MapPath("data.mdb")
   
   'Get the specific image based on the ID passed in a querystring

    str = "SELECT user_photo FROM users where user_id =" & userID
    rs.Open str, conn,3,3
    if rs.eof then 'No records found

        Response.End
    else 'Display the contents

        Response.ContentType = "image/gif" 
        Response.BinaryWrite(rs("user_photo")) 
    end if
   
   'destroy the variables.

   rs.Close
   conn.Close
   set rs = Nothing
   set conn = Nothing

Please note that "Response.contentType" will depend on the type of content you would like to display. For example, to display jpg image, following will be the code:

     Response.ContentType = "image/jpg"

To use above page for displaying images, following is the example:

     <IMG SRC="ShowPicture.asp?PhotoId=1">
     <IMG SRC="ShowPicture.asp?PhotoId=2">

Upload Image

Following example shows how to upload images in database by using ASPSmartUpload component. More information about this component can be found here.

Following is the code to upload image:

<FORM METHOD="POST" ACTION="saveFile.asp" 
        ENCTYPE="multipart/form-data" NAME="UploadForm">
   <center>
   Employee Name : <INPUT TYPE="TEXT" NAME="USERNAME" SIZE="30"><br>
   <INPUT TYPE="FILE" NAME="UPLOADFILE1" SIZE="50">
   </center>
   <BR>
   <center><INPUT TYPE="SUBMIT" VALUE="Upload" 
             id=SUBMIT1 name=SUBMIT1></center>
</FORM>

Code to save the image in the database:

Dim myupload
   'declare variables..

   intCount=0
        
    ' Create Upload Component

    Set myupload = Server.CreateObject("aspSmartUpload.SmartUpload")
    myupload.Upload

    'Create Connection

    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
              "Data Source=" & Server.MapPath("data.mdb")
   
    Set Rs = Server.CreateObject("ADODB.recordset")
    Rs.Open "SELECT user_id,user_name,user_photo FROM users", Conn,3,3

    'Select each file

    For each file In myupload.Files
        If not file.IsMissing Then 'Check for missing file

            'Add the current file in database

            Rs.AddNew
            file.FileToField Rs.Fields("user_photo")
            Rs("user_name") = myupload.Form("USERNAME")
            Rs.Update
            intCount = intCount + 1
        End If
    Next
    Response.Write(intCount & " file(s) uploaded.")
    'Destroy the objects...

Comments

It is always better to store images in the file system rather than storing in the database. Only image path can be stored in the database. However, storing images in the database depends on requirements and needs.