Display an image from SQL database

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...
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

jSkipBConnect With a Mentor Commented:
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.


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") & """>"

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...}">

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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,
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

tomhoranAuthor Commented:
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.

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

tomhoranAuthor Commented:

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.

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...

tomhoranAuthor Commented:

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:

tomhoranAuthor Commented:

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
tomhoranAuthor Commented:
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();">
tomhoranAuthor Commented:

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!!
All Courses

From novice to tech pro — start learning today.