Display an image from SQL database

Posted on 2003-03-24
Medium Priority
Last Modified: 2012-06-22
i've been read this and that about doing this. Any one out there have any code they want to share that will real an image from a SQL database and then dispay it on a webpage ....

the ado stream look interesting...
Question by:tomhoran
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2

Expert Comment

ID: 8200006
Are you storing the binary or path of the image in the database?

If you are doing the former, I'd strongly suggest against it.

The following is a way to retreive the image "path".

[Assuming your database constant is "DB"]

SQL = "SELECT Images WHERE ID = " & Request.QueryString("ID")
Set RS = DB.Execute(SQL)

Response.Write "<img src=""" & RS("ImagePath") & """>"


Expert Comment

ID: 8200032
OK, let's say you've got this table, "images", and it stores only GIFs in a binary column called "data". And, of course, a uniqueidentifier Primary Key, "id". Let's assume that you have a global ConnectionString (NOT an ADO Connection object!) stored in a Session variable.

Next, you create a page called "image.asp", and pass it the image ID. "Image.asp" will look kinda like this:

<%@ Language=VBScript %><%
Dim ID, Item

Set Item=Request.QueryString("ID")
If Item.Count Then ID="" & Item(1)

With Server.CreateObject("ADODB.Recordset")
  .Open "[images] WHERE [id]='" & ID & "'", _
  If .EOF Then
    'Image isn't there - you could send a default image
  Else  'Found image - send it to the browser
    Response.BinaryWrite .Fields("data")
  End If
End With

Now, on our target page, we have our image tag:

  <IMG src="image.asp?ID={OICU-812...}">

LVL 10

Expert Comment

ID: 8201426
Hi tomhoran,


How can I display images that reside in my database through an ASP page?

Displaying Images that Reside in a Database

HOWTO: Display an Image on a Web Page When the Path Is Stored in a Database

HOWTO: Display Images Stored in a BLOB Field

Store Images in Your Database

Storing Images in a DB - Pros and Cons

If you have any follow-up questions, feel free to post here.

Best Regards,
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 8201945
Take this from MSDN. It might help you.
Anyway it not advisable to store images in database. It is better to store the path and retieve it.

AppendChunk and GetChunk Methods Example (VB)
This example uses the AppendChunk and GetChunk methods to fill an image field with data from another record.


    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
Public Sub AppendChunkX()

    'recordset and connection variables
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim rstPubInfo As ADODB.Recordset
   Dim strSQLPubInfo As String
    'record variables
   Dim strPubID As String
   Dim strPRInfo As String
   Dim lngOffset As Long
   Dim lngLogoSize As Long
   Dim varLogo As Variant
   Dim varChunk As Variant
   Dim strMsg As String
   Const conChunkSize = 100

   ' Open a connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
   ' Open the pub_info table with a cursor that allows updates
   Set rstPubInfo = New ADODB.Recordset
   strSQLPubInfo = "pub_info"
   rstPubInfo.Open strSQLPubInfo, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable
   ' Prompt for a logo to copy
   strMsg = "Available logos are : " & vbCr & vbCr
   Do While Not rstPubInfo.EOF
      strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
         Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",") - 1) & _
         vbCr & vbCr
   strMsg = strMsg & "Enter the ID of a logo to copy:"
   strPubID = InputBox(strMsg)
   ' Copy the logo to a variable in chunks
   rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
   lngLogoSize = rstPubInfo!logo.ActualSize
   Do While lngOffset < lngLogoSize
      varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
      varLogo = varLogo & varChunk
      lngOffset = lngOffset + conChunkSize
   ' Get data from the user
   strPubID = Trim(InputBox("Enter a new pub ID" & _
                            " [must be > 9899 & < 9999]:"))
   strPRInfo = Trim(InputBox("Enter descriptive text:"))

   ' Add the new publisher to the publishers table to avoid
   ' getting an error due to foreign key constraint
   Cnxn.Execute "INSERT publishers(pub_id, pub_name) VALUES('" & _
                  strPubID & "','Your Test Publisher')"
   ' Add a new record, copying the logo in chunks
   rstPubInfo!pub_id = strPubID
   rstPubInfo!pr_info = strPRInfo

   lngOffset = 0 ' Reset offset
   Do While lngOffset < lngLogoSize
      varChunk = LeftB(RightB(varLogo, lngLogoSize - lngOffset), _
      rstPubInfo!logo.AppendChunk varChunk
      lngOffset = lngOffset + conChunkSize
    ' Show the newly added data
   MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _
      "Description: " & rstPubInfo!pr_info & vbCr & _
      "Logo size: " & rstPubInfo!logo.ActualSize

   ' Delete new records because this is a demo
   Cnxn.Execute "DELETE FROM pub_info " & _
      "WHERE pub_id = '" & strPubID & "'"

   Cnxn.Execute "DELETE FROM publishers " & _
      "WHERE pub_id = '" & strPubID & "'"

    ' clean up
   Set rstPubInfo = Nothing
   Set Cnxn = Nothing

End Sub


Author Comment

ID: 8203238
jSkipB .. Thanks.

that's excatly what i'm doing. I wonder if my upload of the image isn;t working right. Here is my code for that:

Set UploadedInfo = Server.CreateObject("aspSmartUpload.SmartUpload")
set RepayMeData = Server.CreateObject("RepayMeData.GetData")
set RepayMeFuns = Server.CreateObject("RepayMeFuns.LoadImage")

compno = ""
descrip = ""
whichsiglist = ""
itemcnt = UploadedInfo.Form.Count
for x = 1 to itemcnt
     itemname = UploadedInfo.Form.Item(x).Name
     itemvalue = UploadedInfo.Form.Item(x).Values
     if UploadedInfo.Form.Item(x).Name = "descrip" then
          descrip = UploadedInfo.Form.Item(x).Values
     end if
     if UploadedInfo.Form.Item(x).Name = "whichsig_list" then
          whichsiglist = UploadedInfo.Form.Item(x).Values
     end if
     if UploadedInfo.Form.Item(x).Name = "compno" then
          compno = UploadedInfo.Form.Item(x).Values
     end if
FileSize = UploadedInfo.Files.Item(1).Size
Signaturedata = UploadedInfo.Files.Item(1).ContentString

UploadedInfo.Files.Item(1).SaveAs "c:\wk2\testupld.jpg"

'new signature?
if whichsiglist = "N" then
     ColNames = "cust_no,description"
     ColValues = "'" & CntlParms(LogCustNo_) & "','" & _
          replace(descrip, "'", "''") & "'"
     DefinedSel = "set nocount on "
    DefinedSel = DefinedSel & "insert signatures(" & ColNames & ") values (" & ColValues & ") "
    DefinedSel = DefinedSel & "select @@identity AS 'Id' "
    DefinedSel = DefinedSel & "set nocount off "
     Set SignatureRecSet = RepayMeData.GetData("", "", DefinedSel, AddlParms, "", "", adOpenStatic, adLockOptimistic, ErrorMsg)

     whichsiglist = SignatureRecSet("Id").value
end if
DefinedSel = "select signature from signatures where signature_id = " & _
Set SignatureRecSet = RepayMeData.GetData("", "", DefinedSel, AddlParms, "", "", adOpenStatic, adLockOptimistic, ErrorMsg)    
DefinedSel = ""

Colname = "signature"
xx=RepayMeFuns.LoadImage(SignatureRecSet, Colname, Signaturedata)

here is the code for RepayMeFuns.LoadImage:

Public Function LoadImage(TheRecordSet As Variant, TheColumn As Variant, ImageData As Variant) As Variant

    TheRecordSet(TheColumn).AppendChunk (ImageData)
    LoadImage = 1
End Function

When i look at the table using query analyzer i can see hex data in the image column.


Expert Comment

ID: 8206779
Suggestion : Do NOT store binary in your database. It makes your database far too big, especially if you are storing multiple images in it.


Author Comment

ID: 8206985

I'm only storing signatures in the database and there won't be many.

i'm doing it for security reasons, i don't want to store the images on a hard drive.

Expert Comment

ID: 8207362

The hex visible from Query Analyzer is a good thing - it means your data is being stored properly, in binary format. What I didn't notice, though, and I may have missed it, is, setting the content type for the Response.

You'll notice in my example that I set Response.ContentType to "image/gif" - which only works for GIF images. However, in your example, you show a JPEG image. So, most likely, your upload control has a property of its File object that tells you the content type. You're best bet is to make sure you have a column in your table for the content type, and store whatever the Upload's File object's content type property contains (I'm not familiar with the particular controls you're using, so I'll try to wing it):

  Colname = "content_type"
  Colvalue = Upload.File(1).ContentType
  '[Set "content_type" column]

Then, to display it with my code (way above), substitute:


And don't worry too much about storing a modest amount of binary data in your database.

That should do it. I'm a little burnt today, so keep me posted,

P.S. If you decide to go with those VB examples, better remove all those " As Variant" type declarations...


Author Comment

ID: 8214936

Why should i get rid of the As Variant type declarations. Since i'm calling this from an ASp page and ASP types are Variant if i don't declare them as that the call fails.

I can get the window/screen to display and you can see that there is an image there, i.e. there is a box the size of the image, but the image doesn;t display. Its just an out line with a box with a red x in the upper left corrner.


I'm wondering if i need to do some else with the image string before it's stored into the database? this is what i see:


Accepted Solution

jSkipB earned 600 total points
ID: 8215296
You are this (can you see my fingers almost pinched together?) close...

Here's 3 quick things to check:

1) Make sure your data column type is defined binary, not text.

2) I'm not familiar with the specific controls you're using, but check to see if there's another property besides ContentString that returns binary rather than a string.

3) Again, being unfamiliar with the controls you're using, but I noticed the SQL INSERT command to store the image data, then you'll end up with text. Consult your doc for details on storing binary data, perhaps through another of its available objects and/or methods.

The Variant declarations don't work in VBScript, only VB. In VBScript, everything is a variant, and it doesn't support type declarations in a Dim statement. (This is VBScript on an ASP page, isn't it?)

If you're still stuck, let me know the product names of the controls, and I'll be only too happy to look up info on them for you.



Author Comment

ID: 8215467

The data column in the SQL database is "image" not binary, is that a problem?

The ASP program calls a VB DLL called "LoadImage". This code takes the passed recordset and uploaded image and then uses the AppendChunk method to load the image (which is passed in a Variant type variable) into the database/recordset.

The upload control does have a binary option, i wonder if that would make a difference?:

The BinaryData property contains the byte corresponding to the table index containing the transmitted data.

.BinaryData (lngIndex)

Return Value
Returns a byte value.

lngIndex is an index of the byte's array.


for i=0 to MyUpload.Files.Item(2).size - 1
    Response.Write( Chr( MyUpLoad.Files.Item(2).BinaryData(MyIndex) ) )
next i

Author Comment

ID: 8215688
could it be the way i have the form setup to do the upload?

i'm using the form definition.

<FORM ENCTYPE="multipart/form-data" action="./checksetup3.asp" method="post" id="thisForm" name="thisForm" ONSUBMIT="return editform();">

Author Comment

ID: 8218119

Thanks for all of the effort, could get it to work but didn't find a aspsmartupload control/method that loaded the image and it worked ...

UploadedInfo.Files.Item(1).FileToField SignatureRecSet("signature")

A for effort!!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question