Display an image from SQL database

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

Regards,
Wakie.

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 & "'", _
    Session("ConnectionString")
  If .EOF Then
    'Image isn't there - you could send a default image
  Else  'Found image - send it to the browser
    Response.ContentType="image/gif"
    Response.BinaryWrite .Fields("data")
  End If
  .Close
End With
%>

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

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

Voila!

Hi tomhoran,

DISPLAYING IMAGES FROM DATABASE

How can I display images that reside in my database through an ASP page?
http://www.aspfaqs.com/ASPScripts/PrintFAQ.asp?FAQID=172

Displaying Images that Reside in a Database
http://www.4guysfromrolla.com/webtech/060100-1.shtml

HOWTO: Display an Image on a Web Page When the Path Is Stored in a Database
http://support.microsoft.com/default.aspx?scid=KB;en-us;q299320

HOWTO: Display Images Stored in a BLOB Field
http://support.microsoft.com/default.aspx?scid=KB;en-us;q173308

Store Images in Your Database
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbpj01/html/gs0102.asp

Storing Images in a DB - Pros and Cons
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10477

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

Best Regards,
>apollois<

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.

'BeginAppendChunkVB

    '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
      rstPubInfo.MoveNext
   Loop
   
   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
   Loop
   
   ' 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.AddNew
   rstPubInfo!pub_id = strPubID
   rstPubInfo!pr_info = strPRInfo

   lngOffset = 0 ' Reset offset
   Do While lngOffset < lngLogoSize
      varChunk = LeftB(RightB(varLogo, lngLogoSize - lngOffset), _
         conChunkSize)
      rstPubInfo!logo.AppendChunk varChunk
      lngOffset = lngOffset + conChunkSize
   Loop
   rstPubInfo.Update
   
    ' 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
   rstPubInfo.Requery
   Cnxn.Execute "DELETE FROM pub_info " & _
      "WHERE pub_id = '" & strPubID & "'"

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

    ' clean up
   rstPubInfo.Close
   Cnxn.Close
   Set rstPubInfo = Nothing
   Set Cnxn = Nothing

End Sub
'EndAppendChunkVB

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 = ""
UploadedInfo.Upload
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
next
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 = " & _
     whichsiglist
Set SignatureRecSet = RepayMeData.GetData("", "", DefinedSel, AddlParms, "", "", adOpenStatic, adLockOptimistic, ErrorMsg)    
DefinedSel = ""

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

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)
    TheRecordSet.Update
    LoadImage = 1
   
End Function

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

Suggestions?

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

Wakie.

Wakie,

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.

tomhoran,

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]
  SignatureRecSet.update


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

  Response.ContentType=.Fields("content_type")

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

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

jSkipB,

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.

Ideas?

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:

0xFF00D800FF00E000000010004A004600490046000000010001000000000001000000010000000000FF00DB000000430000000300020002000300020002000300030003000300040003000300040005000800050005000400040005000A0007000700060008000C000A000C000C000B000A000B000B000D000E00120010000D

jSkipB,

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.

Syntax
.BinaryData (lngIndex)

Return Value
Returns a byte value.

Parameter
lngIndex is an index of the byte's array.

Example

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

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

ISkipB,

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