Displaying Images In Access

[larry]

Hi Jim,
We did collaborate - John gave up on it and passed it down
to me. I do see now where you had replied to his last
message - but I'm afraid he never saw it. I'll tell him
though.

It is probably notable as well that we did visit the web
site listed below and that it did not have enough
information - as you state, it references SQL server. We
are trying to query an Access DB "with" SQL - not using
SQL server.

After reading your last response to John, it occurs to me
that perhaps you may not understand what we are looking to
accomplish. We are trying to implement a searchable web
site (asp) to an Access database that is already built and
contains some 30,000 products - most with identifying
pictures. We do not use SQL server. Although article
175261 outlines how to go about creating the DLL and
setting up a VBScript, it appears that this method would
only bring up a specific record - and not a group of
records (ie, if a user were to query a DB with WHERE Tools
LIKE '%::Input Search Criteria::%' it may bring up several
listings for 'nails').

Also after reading your last message to John, you
mentioned that we should create Table Cells that are
linked to pictures. I would love for it to be so simple,
but as I have stated, but we do need to have a searchable
database.

We are not opposed to creating and installing components
on the server, but we do need this speicific functionality.

Thank you.

Subject: re: Displaying Images From Access 2002 by SQL
Through FP 2002
From: "Jim Buyens" <[email protected]> Sent:
9/8/2003 2:12:05 PM

-----Original Message-----
Hello.

I am attempting to display images from an Access DB on to
an .asp page through FP 2002. The searchable text comes
up just fine (different field associated with the image),
however; the image does not display properly.

When I follow the directions provided by MS for FP2000:

http://support.microsoft.com/default.aspx?
scid=/support/frontpage/fp2000/aspweb/page00010.asp

I get the broken image link. I believe this is because
Access stores the image as an OLE Binary object and FP
cannot convert the image properly. Does anyone know of a
way to strip out the OLE/Binary headers prior to
transfer?
I am also needing to do the same with Adobe Acrobat .pdf
files as well - but if I can solve (or someone can help
me solve) the riddle of the image, the pdf portion
shouldn't pose a problem. Thank you!

Dear (e-mail address removed):

I answered this same question just four days ago for
(e-mail address removed). As I recall, however, johnb asked
about Microsoft Access rather than SQL Server. Here's a
relevant article regarding SQL server.

173308 HOWTO: Display Images Stored in a BLOB Field

http://support.microsoft.com/default.aspx?scid=kb;en-
us;173308

I suggest the two of you collaborate and then, if either
of you have any specific questions regarding my previous
answer, please post them.

Jim Buyens
Microsoft FrontPage MVP
(e-mail address removed)
http://www.interlacken.com
Author of:
*------------------------------------------------------*
|\----------------------------------------------------/|
|| Microsoft Office FrontPage 2003 Inside Out ||
|| Microsoft FrontPage Version 2002 Inside Out ||
|| Web Database Development Step by Step .NET Edition ||
|| Troubleshooting Microsoft FrontPage 2002 ||
|| Faster Smarter Beginning Programming ||
|| (All from Microsoft Press) ||
|/----------------------------------------------------\|
*------------------------------------------------------*

 

[Thomas A. Rowe]

Larry,

If the images are stored directly in the web site, then in your Access
database table, you only need to store the name of the image file. This will
make your database a lot smaller, plus save you this effort of trying to
extract the image from the database.

I build e-commerce and other database driven web site, and never store the
images within the database.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[Jim Buyens]

Hi Jim,
We did collaborate - John gave up on it and passed it down
to me. I do see now where you had replied to his last
message - but I'm afraid he never saw it. I'll tell him
though.

Sorry I thought you'd started talking about SQL server. My Mistake.

In any event, here's the code for the bitmap.asp page shown in article
175261.

<%
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "DSN=NWind", "admin", ""
Set cmdTemp = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "SELECT Photo " & _
"FROM Employees " & _
"WHERE EmployeeID = 1"
cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = DataConn
RS.Open cmdTemp, , 0, 1
Response.ContentType = "image/bmp"
Set Bitmap = Server.CreateObject("MyProject.MyClass")
Response.BinaryWrite Bitmap.DisplayBitmap(RS("Photo"))
RS.Close
%>

You will probbly want to make the following modifications to this
code:

1. Change the connection string to the database. This affects line 2.
You probably need something like:

DataConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
server.mappath("../db/products.mdb") & ";"

where ../db/products.mdb is the relative path from your Web page to
the
database.

2. In two places, change the field name Photo to the field name that
contains the pictures in your database.

3. Change the table name Employees to the name of your table.

4. Change the database command from:

cmdTemp.CommandText = "SELECT Photo " & _
"FROM Employees " & _
"WHERE EmployeeID = 1"

to

cmdTemp.CommandText = "SELECT Photo " & _
"FROM Employees " & _
"WHERE EmployeeID = " & Request("prodnum")

This will look up a product number that you specify on a query
string,
as in

bitmap.asp?prodnum=1234.

If your product ID field is character, code the last line:

"WHERE EmployeeID = '" & Request("prodnum") & "'"

5. To correct an oversight in article, change the last two statements
to:
these five:

Response.BinaryWrite Bitmap.DisplayBitmap(RS("Photo"))
RS.Close
Response.End

6. Create a DLL as instructed in steps 1-9 of article 175261.

7. Copy this DLL into the winnt/system32 folder of your Web server.
Then,
open a command prompt on the server, switch to the winnt/system32
folder,
and run the command:

regsvr32 <dllname>

where <dllname> is the file name of your new DLL.

8. Note that in the bitmap.asp page,

Set Bitmap = Server.CreateObject("MyProject.MyClass")

loads an object from the DLL you registered in step 6. If you used
different names in steps 8 and 9 of article 175261, you'll need to
change
MyProject and MyClass to the values you used.

9. Now, typing a URL like
http://myserver/mypath/bitmap.asp?prodnum=1234
should display the picture from the database record for product
1234.
The expression Request("prodnum") retrieved the product number and
put
it in the SQL statement. The DisplayBitmap method of the
MyProject.MyClass
class pulled the raw picture bits out of the OLE object in the
database,
and the Response.BinaryWrite method sent them to the browser.

10. Once you get the pictures to appear in the browser, you can
include them
in any Web page by coding a tag thus:

<img src="http://myserver/mypath/bitmap.asp?prodnum=1234">

To get the Database Results Wizard to create such tags, set up an
Access query or a DRW custom query to include an expression like
this:

"<IMG SRC=" & chr(34) & "bitmap.asp?prodnum=" & [prodnum]& chr(34)
& ">"
AS prodpictag

Of course, you should also include any necessary, relative path
from the
page that runs the query to the bitmap.asp page.

11. By default, the DRW will display the prodpictag field as text. To
make it
display the contents of the treat prodpictag field as HTML, finish
running the wizard, right-click the prodpictag column in Normal
view,
choose Database Column Value Properties, and select Column Value
Contains
HTML.

Altogether, this is a terribly complicated way to display pictures. It
gets worse if you want to display thumbnail pictures or convert
pictures to GIF or JPEG on the fly.

BTW, the Inside Out books for FrontPage 2002 and 2003 each contain a
working example of this technique. The name of the main Web apge is
nwemps.asp.

Jim Buyens
Microsoft FrontPage MVP
(e-mail address removed)
http://www.interlacken.com
Author of:
*------------------------------------------------------*
|\----------------------------------------------------/|
|| Microsoft Office FrontPage 2003 Inside Out ||
|| Microsoft FrontPage Version 2002 Inside Out ||
|| Web Database Development Step by Step .NET Edition ||
|| Troubleshooting Microsoft FrontPage 2002 ||
|| Faster Smarter Beginning Programming ||
|| (All from Microsoft Press) ||
|/----------------------------------------------------\|
*------------------------------------------------------*

 

[Larry]

Thomas,

This sounds like exactly what we are trying to accomplish.
We have tried "linking" a bound OLE object in Access, but
it does not seem to work properly. Should we be using a
text field in Access?

Scenario:
We have our DB in our web under the FPDB directory. We
have stored the images under this directory as well.
Should we use a bound OLE (BLOB) or an unbound OLE or a
text field to store the image? If it is a text field, do
we just use the image name (ie 91250.jpg) in the text
field?

Thanks in advance Thomas.

 

[Thomas A. Rowe]

Yes, Forget about the BLOB, just create a text field, call say, "ProdImage",
then type in the filename "productid.jpg" into the field.

Store the images in another folder, not the fpdb folder, say "ProdImages",
then on your pages where you want to display the image, do like:

<img src="../ProdImages/<%=objRS("ProdImage")%>" width ="200"
alt="<%=objRS("ProdName")%>">

If you will be process customer data in your database, store the database
outside the root of your web. However, do not delete the fpdb folder.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================

 

[Larry]

Thank you Thomas,
We will give this a go. I did notice however that FP will
not allow us to change the code for this image field on
the ASP page - so perhaps I will do it in a text editor.
Thanks again.

Larry

 

[Thomas A. Rowe]

I always hand code.

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================