There are oftentimes when images or other binary information needs to be stored along with textual information in a database. For example, imagine a database that contains Employee information. Some non-textual information that one may wish to store in this database could be a picture of the employee and the employee's current resume in Word format. This article focuses on how to save and retrieve image information.

There are two approachs to saving images in a database. One is to use textual information to save the URL of the image in the path. For example, our Employee table may have the following structure:

Column	Datatype
EmployeeID	int (Primary Key)
FirstName	varchar(50)
LastName	varchar(50)
Picture	varchar(100)

The Picture column would then contain the path of the respective Employee's personal photo, perhaps /employee/images/Bob.Smith.jpg or http://EmployeeServer/pictures/Bob.Smith.jpg. This method is more efficient than storing the actual binary image in the database, but suffers from scalability issues. In such a scenario, the Employee's photos must be saved on the Web server or on a machine the Web server can access. If this database was to be replicated to several database servers, the physical files would also need to be replicated and their filenames and locations preserved. This would be a major headache!

Fortunately modern database systems allow you to have non-textual columns. In Microsoft SQL Server, the image datatype can be used to save a binary object (such as an Employee's picutre). In Access, use the OLE Object datatype. By placing the actual image in the database, however, there can be some efficiency concerns, especially if the images are large and/or the database resides on a different server than the Web server.

Displaying an Image From a Database Using ASP
For this example, image that our Employee table (defined above) had the Picture column datatype changed to an image datatype. It is essential that we know the type of image being stored in the Picture column, whether it is a GIF, a JPG, a PNG, etc. For this example we'll assume all pictures are JPG. (In a scenario where there may be multiple file formats, a look-up table should be used to specify the filetype for each row.)

Now we need to create an ASP page whose sole responsibility is to display a specific Employee's picture. This ASP page will be called ShowEmployeePicture.asp, and will expect a command through the QueryString, the Employee's EmployeeID. Furthermore, this ASP page will be called through an IMG tag on an ASP or HTML page that wishes to display a particular Employee's picture. For example, on a page that wished to show Employee #007's picture, the following IMG tag should be used:

<IMG SRC="ShowEmployeePicture.asp?EmployeeID=007">

(Note that this method of displaying ASP pages is strikingly similar to the technique discussed in a previous 4Guys article: Serving Dynamic Images from Static Web Pages. If you have not yet read that article, I suggest you do so now.)

The code for ShowEmployeePicture.asp needs to perform a number of tasks:

1.) Read in the EmployeeID passed through the QueryString
2.) Grab the picture from the database corresponding to the passed-in EmployeeID
3.) Set the ContentType to image/jpeg, since all Employee photos are JPG files (If you are unfamiliar with Step 3, using the Response.ContentType property, take a moment to read Serving Dynamic Images from Static Web Pages)
4.) Use Response.BinaryWrite to send the picture to the client

Here is the code for ShowEmployeePicture.asp:

<% 'Step 1: Read in the employee ID from the querystring Dim iEmployeeID iEmployeeID = Request.QueryString("EmployeeID") 'Step 2: grab the picture from the database Dim objConn, objRS, strSQL strSQL = "SELECT Picture FROM Employee WHERE EmployeeID = " & iEmployeeID Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DSN=EmployeeDatabase" Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open strSQL, objConn 'Step 3: Set the ContentType to image/jpeg Response.ContentType = "image/jpeg" 'Step 4: Use Response.BinaryWrite to output the image Response.BinaryWrite objRS("Picture") 'Clean up... objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing %>

Well, that's all there is to it! Not too difficult with ADO and ASP after all! Happy Programming!